Compatibility Checker

Question

I am having problems with Excel files losing some links to worksheets in another file. It retains some links while others break. The part it loses is the worksheet name, but the names only differ by a single digit, "Scen 1" vs "Scen 2."

In VBA, properties, there is one item called (Name) and another called Name. The first one appears to be the original name Excel gave the sheet when it was created, such as, "Sheet 16", and the second is the name I gave the worksheet, "2030 AM with Scen 1. The links to that sheet stay connected when saved and closed. The link in the adjacent cell is to "Sheet 70", which I renamed "2030 AM with Scen 2." I have hundreds of sheets in the source workbook, which is an Excel Macro-Enabled Workbook (*.xlsm) file.

Due to an issue with an old macro, I can't change the file format of the second workbook from 97 - 2003 (.xls) to anything newer.

Do .xls files have a maximum sheet number that can be used in the original name, something less than 70 perhaps?

Answer 1

Hi @Bradley William Yarger,

Thank you for posting your question on Microsoft Q&A Community and for sharing the details, this is a nuanced issue, and I appreciate the clarity you've provided.

In Excel, each worksheet has two identifiers:

• (Name): The internal code name (e.g., “Sheet70”), which VBA uses.
• Name: The visible name you assign (e.g., “2030 AM with Scen 2”).

Your observation is correct: links that reference the internal name (like “Sheet16”) tend to stay intact, while those pointing to higher-numbered sheets (like “Sheet70”) sometimes lose the worksheet reference, especially in .xls format.

The possible cause could be:

The Excel 97–2003 (.xls) format has limitations:

• It supports only 256 sheets per workbook, but stability may degrade as you approach that limit.
• It’s more prone to link corruption, especially when referencing renamed sheets or using external links across formats.

Below are the steps that I suggest:

1. Use Defined Names Instead of Direct Sheet References

• In your source workbook, go to Formulas > Name Manager.
• Create a named range for each worksheet you link to.
• Update your formulas to use these names instead of direct sheet references.

This helps Excel maintain links even if sheet names or positions change.

2. Avoid Renaming Sheets After Linking

• If possible, finalize sheet names before creating links.
• Renaming sheets after links are established can cause Excel to lose track, especially in .xls.

3. Check for Hidden Characters or Similar Names

• “Scen 1” vs. “Scen 2” may look similar but could contain hidden characters or spaces.
• Use VBA or Find/Replace to verify consistency.

4. Split the Workbook

• If your .xls file has hundreds of sheets, consider splitting it into smaller files.
• This reduces the risk of link loss and improves performance.

5. Use VBA to Monitor Link Integrity

You can write a macro to scan for broken links and log which ones fail. Let me know if you'd like help with that.

Hope my information helps. Please don't hesitate to reach back for more assistance. I'm here to help.

Looking forward to hearing from you.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I am on a deadline for tomorrow, so I can't implement some of your suggestions right away. I manually plowed through the process to get what we need for now.

I may try the defined names in the next project. For now, I manually got what I needed, and then saved to a .csv file to input in to another software as data.

The broken links are pretty obvious when I open the file since there is just one worksheet in the destination file, and I don't change it from Sheet 1.

The links are created using "=" and then clicking on the source cell, so if there are extra characters in the sheet name, it should still keep them, unless Excel removes double spaces when saving to .xls. If I save to a .xlsx file, the same link remains connected after saving, closing and reopening.

Because the sheet name can only have 31 characters, we have a check built in the sheet where we get the sheet names. We use a macro to update the sheetnames to the new name, but it isn't changing it when we open the destination file. The sheet names are based on the project's criteria, which changes from project to project, but the workbook names always stay the same from project to project.

I may split the source .xlsm file. It has tables, and then "figures" that reference the tables. The data in the table and figures are traffic counts at intersections. The purpose of the figure is to be sure that the traffic volumes leaving one intersection arrive at the next. If the user makes a mistake, then the two numbers in the figure won't be equal, and a warning goes off with conditional highlighting showing the error. We use "Warning" rather han "Error" since some of them have count data which may not balance because cars get caught between intersections during the time being analyzed. The figures use =@INDIRECT("'" & $K$1 & "'!j$12") to link back to the tables. The figure part of it has lines showning the relationship between the numbers in the table. K1 has the source worksheet name. This part works fine 99% of the time. If we split the workbook, I may have to build in the worksheet name, but that never changes, so it could be hard coded.