Missing links

Question

Good morning all.

I have a large, complex spreadsheet that, on opening, tries to open non-existent links. 

The error messages that come up on opening follow:

Error 1

Sorry, we couldn’t open ’.../FASSETS/Ytdmar99/99DEPN.xls’.

Error 2

Microsoft Excel cannot access the file ".../FASSETS/Ytdmar99/99DEPN.xls". There are several possible reasons:

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

Error 3

Sorry, we couldn’t open ’.../RoadArea.xls’.

Error 4

Microsoft Excel cannot access the file "...RoadArea.xls". There are several possible reasons:

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open workbook.

Those errant links do not come up in the Edit Links dialogue box. 

I have a vague recollection of a spreadsheet I used five or six years ago that might have had those references but that was well before I created this spreadsheet with which I now have the problem.

Happy to provide the one sheet extract if it helps.

Mac OS 12.1.1. Excel for Mac 16.56.

The main spreadsheet has been developed progressively over the last year or so and is far too complex for me to rebuild from scratch. Any thoughts?

Thanks in advance.

Shane Blakeley

Answer 1

Hi

The workbook was designed to use those links. They're dead, so whatever data was coming from those links is not available.

Removing the links within the workbook will probably cause new errors since the expected data won't be there.

Answer 2

Igor,

Thanks for your very detailed instruction. I tried them all (except the VBA suggestion; beyond me I'm afraid) but, unfortunately, to no avail. The links remain.

Yes please to your offer to scrutinise the actual file. I'm on the road this week and this laptop doesn't have One Drive loaded but here's a dropbox link to the file: https://www.dropbox.com/s/eulnkr1xbk8qz5k/Errant%20links.xlsx?dl=0

Good luck.

Bob and Jim, thanks for your suggestions but the links pasted to the browser went nowhere. Not sure what that means but hoping Igor can work it out.

Thanks again to you all.

Shane

Answer 3

Those errant links do not come up in the Edit Links dialogue box. 

PMFJI but I just wanted to clarify this point... That dialog is only for managing formula links to other Excel workbooks. It has nothing to do with Hyperlinks so it's understandable that it's of no help in resolving your issue.

Answer 4

Hi Shane,

The links tell you that functionality of the workbook you have requires the presence of 4 additional workbooks.

Have you tested the links by copying them into the address bar of your web browser? I tried the first one and it did not work. If the links are dead, you might spend some time removing them as Igor suggested only to discover your workbook is unusable without the links.

Answer 5

Hello Shane Blakeley

Sorry for the inconvenience caused by this issue.

According to description, it seems that your Excel workbook contains the phantom links. In this scenario I may suggest you try the following methods:

Check for Data Validation Links

1. The easiest test is to remove all Validation and see if the link remains (you may want to make a copy of your file before performing this step)
2. Copy any blank, unformatted cell
3. Select all cells with Validation; press the F5 key, click the “Special” button and select “Data validation”
4. Select Paste Special and then select Validation
5. Save, close and reopen the file to see if this has solved the problem
6. Repeat steps above for the remaining worksheets in the file

Check for Conditional Formatting Links

1. Go to the Conditional Formatting Rules Manager
2. Select “This Worksheet” from the drop down
3. Check each Rule to ensure they are referencing data in an external file
4. Amend or Delete the Rule to remove the Link
5. Repeat steps above for the remaining worksheets in the file

Check for Links on Hidden Worksheets

1. If you or anyone else has hidden any worksheets in the file, there may be links on these hidden sheets
2. Check for hidden worksheets by going to the Format Sheet Unhide menu
3. If this option is greyed out, the file probably doesn’t contain any hidden sheets; however, be sure to also check for Very Hidden Sheets with the Visual Basic Editor
4. If there are hidden worksheets, unhide them and complete all of the steps above on the sheets that were hidden

If none of the provided suggestions helped you to remove these links I kindly ask you to share a sample of your file with us for the proper tests. You may upload it to OneDrive and share a link.

Best Regards,

Igor