Phantom Links in Workbook that cannot be found to be deleted

[ngolinda408]

Hello,


There is a workbook links that is hidden in my Excel 2016 Excel file. I believe it is causing Excel to crash as it tries to access a network folder I don't have access to.
The only way I know the links exist is through the Inquire tab, using the Workbook Analysis tool.
The problem is that Workbook Analysis doesn't tell me the location of the links so I don't know how to remove them.
I checked:
used CTRL+F in formulas for "[", ".xl" (no luck)
opened Workbook Connections window in the Data tab (no connections visible)
conditional formatting
data validation
name manager
I checked in all worksheets, but I cant find where the link is.
I even deleted all of the tabs and have only a blank tab left and the link is still there in the Inquire Workbook Analysis.


Short of recreating the file, is there a macro i can use to find and delete this phantom link?

Any help is greatly appreciated.

Thanks in advance.

 

[MoshiM]

Have you tried to edit links under the Data tab? It might not allow you to delete the link though. There is also a chance the referenced link may be in the conditional formatting of one of your worksheets

 

[ngolinda408]

Have you tried to edit links under the Data tab? It might not allow you to delete the link though. There is also a chance the referenced link may be in the conditional formatting of one of your worksheets

Hi MoshiM, I did check and there were no links listed in there, but if i ran a workbook inspection from the files tab or from the Inquire add in then it will find it. Its the only thing slowing down and crashing the file.

I deleted all of the worksheets in the file to try and do a process of elimination to see if it was in one of the sheets but at the end, all of the sheets were deleted and it still was there.
This is an old file that was established a long time ago and we reuse it every month. I can recreate the file but I run into this problem every so often so was hoping there was a better solution than recreating it every time. Thanks for your suggestions.

 

[MoshiM]

Hi MoshiM, I did check and there were no links listed in there, but if i ran a workbook inspection from the files tab or from the Inquire add in then it will find it. Its the only thing slowing down and crashing the file.

I deleted all of the worksheets in the file to try and do a process of elimination to see if it was in one of the sheets but at the end, all of the sheets were deleted and it still was there.
This is an old file that was established a long time ago and we reuse it every month. I can recreate the file but I run into this problem every so often so was hoping there was a better solution than recreating it every time. Thanks for your suggestions.

Check the name manager as well

 

[sandy666]

maybe post a link to shared example file. use GoogleDrive, OneDrive or any similar.

 

[Fazza]

Be sure to check HIDDEN names, the name manager doesn't show these IIRC.
In case you haven't already checked, also check for any very hidden worksheets.
And check objects on worksheets: these objects (things like shapes) can be hidden too, of course.

PS Sometimes I see files with THOUSANDS of hidden names linked back to ancient versions/workplaces/companies/projects

 

[ngolinda408]

Hi Everyone,

Here's a link to the file.
https://docs.google.com/file/d/0Bx7...pYUXA0/edit?usp=docslist_api&filetype=msexcel

 

[ngolinda408]

maybe post a link to shared example file. use GoogleDrive, OneDrive or any similar.

Just did it! Take a look. I deleted all tabs and still cant find the link. The original copy has tons of tabs and would be a real pain in the butt to recreate...

 

[MoshiM]

Just did it! Take a look. I deleted all tabs and still cant find the link. The original copy has tons of tabs and would be a real pain in the butt to recreate...

BLPHs in the name manager?

Sub unhideAllNames()
Dim tempName As Variant
'Unhide all names in the currently open Excel file
    For Each tempName In ActiveWorkbook.Names
        tempName.Visible = True
    Next
End Sub

 

[sandy666]

do you really need over 42 000 japanese styles?

ad rem:

< ?xml version="1.0" encoding="UTF-8" standalone="yes"?>
< Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">< Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath" Target="file:///\\[B][COLOR="#0000FF"]Sc-finance-f1\Group-Share\TREASURY\CURRENCY\Currency%20update\Q4%20FY01\Weekly%20Currency%20update_10_22_01.xls[/COLOR][/B]" TargetMode="External"/>< /Relationships>

< ?xml version="1.0" encoding="UTF-8" standalone="yes"?>
< externalLink xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">< externalBook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"><sheetNames>< sheetName val="[B][COLOR="#0000FF"]Sheet3[/COLOR][/B]"/>< sheetName val="[B][COLOR="#0000FF"]Weekly[/COLOR][/B]"/>< /sheetNames>< sheetDataSet>< sheetData sheetId="0">< row r="[B]484[/B]"><cell r="[B]I484[/B]">< v>[B]37152[/B]< /v></cell>< /row><row r="[B]498[/B]">< cell r="[B]I498[/B]" t="str">< v>[B]JPY Curncy[/B]< /v>< /cell>< /row><row r="500">< cell r="[B]I500[/B]">< v>[B]37165[/B]< /v>< /cell>< /row>< row r="[B]527[/B]">< cell r="[B]K527[/B]">< v>[B]37165[/B]< /v>< /cell></row>< /sheetData>< sheetData sheetId="1"/>< /sheetDataSet>< /externalBook>< /externalLink>

this file was linked to the file: Weekly Currency update_10_22_01.xls sheet3, name: Weekly to data: JPY Curncy
I think after any crash link to this file has been saved in the current file instead of being automatically deleted

you can remove folder externalLinks from the xml version of this file then repair and save.

clean (except some jpy styles which I can't remove ) file is here: download