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 `<main>' 

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?

Answer 1

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.)

Answer 2

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