Excel Filename Reference Length

I am finally getting around to posting this tip.

I came across an error while helping a colleague of mine a couple of months ago. They were trying to figure out what the easiest way was to update an Excel file that had many reference links to other Excel files. Every time he opened the main file & refreshed the connections, the formulas would give him a #REF! error message.

We eventually figured out that due to the huge folder structure he was storing everything in, the length of the filenames to the referenced Excel files were too long for Excel to digest! Since I had never come across this issue (probably because I always try to keep my file structure as flat and as short as possible), I figured it would be good to post about it to help others who might come across this error.

Microsoft support does have an article detailing this issue which you can read about here: http://support.microsoft.com/kb/213983

In summary, this is what you need to know about filename length:

If you save or open a file where the path to the file (including the file name) exceeds 218 characters you will get an error. This limitation includes three characters representing the drive (e.g. “C:\”), the characters in folder names, the backslash character between folders, and the characters in the file name. In addition, it appears that the file extension (e.g. “.xlsx”) contributes to the length of the filename when causing this error.

This behavior is based on a 256-character limitation in Excel for creating links to another file. This limit of 218 characters for the path name is based on the following:

Up to 31 characters in a sheet name.
Apostrophes and brackets used to denote the workbook name.
An exclamation point.
A cell reference.

For example, the path for a file might resemble the following:

c:\excel\personal\...\[my workbook.xls]up_to_31_char_sheetname'!$A$1

This behavior will also occur if there is a square bracket in the path.

Hopefully this will help encourage you to keep your file structure & file names as short as possible to avoid issues such as these!