Phantom Links in Workbook that cannot be found to be deleted

ngolinda408

New Member
Joined
Jul 12, 2019
Messages
10
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 12, 2019
Messages
10
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

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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 :)
 
Last edited:

ngolinda408

New Member
Joined
Jul 12, 2019
Messages
10
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...:eek:
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
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...:eek:

BLPHs in the name manager?
Code:
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
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
do you really need over 42 000 japanese styles? :)

ad rem:
Code:
< ?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>

Code:
< ?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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,089
Members
416,010
Latest member
NJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top