Is there a limit on an Excel worksheet s name length

Question

When I try to set a longish worksheet name using ruby and win32ole with the following code   require  win32ole  excel   WIN32OLE new  Excel Application   excel Visible   1 puts excel version workbook   excel Workbooks Add worksheet1   workbook Worksheets Add worksheet1 Name    Pseudopseudohypoparathyroidism   Length 30  fine worksheet2   workbook Worksheets Add worksheet2 Name    Supercalifragilisticexpialidocious   Length 34  not fine   I get the following   12 0 - 9 in  method missing    in setting property  Name      WIN32OLERuntimeError      OLE error code 800A03EC in Microsoft Office Excel       You typed an invalid name for a sheet or chart  Make sure that    The name that you type does not exceed 31 characters   The name does not contain any of the following characters                     or     You did not leave the name blank      HRESULT error code 0x80020009       Exception occurred          from - 9 in   lt main gt     The version 12 0 indicates that I m running Excel 2007  but it s complaining that the worksheet name is too long  I had a look at Excel 2007 specifications and limits as mentioned in this related answer  and I couldn t find it mentioning any such limit   Trying to rename a worksheet manually suggests there may be such a limit  however   Is there a limit  and is it a hard limit or one that can be changed by changing the configuration of Excel

User · Answer

I just tested a couple paths using Excel 2013 on on Windows 7. I found the overall pathname limit to be 213 and the basename length to be 186. At least the error dialog for exceeding basename length is clear: basename error

And trying to move a not-too-long basename to a too-long-pathname is also very clear:

The pathname error is deceptive, though. Quite unhelpful:

This is a lazy Microsoft restriction. There's no good reason for these arbitrary length limits, but in the end, it’s a real bug in the error dialog.

User · Answer

The file format would permit up to 255-character worksheet names  but if the Excel UI doesn t want you exceeding 31 characters  don t try to go beyond 31  App s full of weird undocumented limits and quirks  and feeding it files that are within spec but not within the range of things the testers would have tested usually causes REALLY strange behavior   Personal favorite example  using the Excel 4 0 bytecode for an if   function  in a file with an Excel 97-style stringtable  disabled the toolbar button for bold in Excel 97

User · Answer

My solution was to use a short nickname  less than 31 characters  and then write the entire name in cell 0

User · Answer

Renaming a worksheet manually in Excel  you hit a limit of 31 chars  so I d suggest that that s a hard limit

User · Answer

I use the following vba code where filename is a string containing the filename  I want  and Function RemoveSpecialCharactersAndTruncate is defined below   worksheet1 Name   RemoveSpecialCharactersAndTruncate filename    Function to remove special characters from file before saving  Private Function RemoveSpecialCharactersAndTruncate  ByVal FormattedString       Dim IllegalCharacterSet      Dim i As Integer  Set of illegal characters     IllegalCharacterSet          amp  Chr 34   amp                    Iterate through illegal characters and replace any instances     For i   1 To Len IllegalCharacterSet  - 1         FormattedString    Replace FormattedString   Mid IllegalCharacterSet  i  1           Next      Return the value capped at 31 characters  Excel limit      RemoveSpecialCharactersAndTruncate    Left FormattedString                                Application WorksheetFunction Min Len FormattedString   31   End Function

[excel] Is there a limit on an Excel worksheet's name length?

Examples related to excel