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.
 

ngolinda408

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

Sorry MoshiM, I'm not on the level as you are...more like a yellow belt in excel....whats a BLPHs? And I tried to put that in the VBA module and hit Run but nothing happened...
Can you help me out and guide me a little, please? Thanks!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ngolinda408

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

Thanks a million for trying to clean the file but can you please teach me to clean it like you did? I would love to learn because I inherited a whole bunch of files similar to this.
I tried to save it as a xml but i got a message that said it cannot be saved as a xml because it doesnt have any xml mappings.

I used the Inquire Workbook Analysis and found the name of the file that you referred to but couldnt find its location.
Can you guide me as to how to clean this file like you did? Do i plug the code that you wrote into the VBA module?
Thanks a million for your help and patience.</row></sheetnames>
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
this is xml from your excel file :)

there was over 42 000 styles (for what?)
also hundreds hidden defined names, most of for single cell (for what?)

I removed broken link only and some styles.

maybe better is create brand new file and data? think about it.

btw. where are your backup files? :eek:
 
Last edited:

ngolinda408

New Member
Joined
Jul 12, 2019
Messages
10
this is xml from your excel file :)

there was over 42 000 styles (for what?)
also hundreds hidden defined names, most of for single cell (for what?)

I removed broken link only and some styles.

maybe better is create brand new file and data? think about it.

btw. where are your backup files? :eek:

I didn’t make this file and it’s just been added on to over the years with data and models. To recreate it would be really painful as it’s like 28mb. When I try to copy the tabs to a new sheet then it picks up that phantom link so I’d have to totally recreate it.

I have no idea why there are so many styles and hidden names. I couldn’t even find it to delete it, but I know we definitely don’t need it. I’ll try to learn how to delete from the xml. I’m not such an expert at all this so thanks for the suggestions. :)
 

sandy666

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

ADVERTISEMENT

28 MB ?!
for the future try to split data to the source layer(s) and presentation layer(s) or use any simple DB (eg. MS Access)
and remember about backup. Easier is recreate 200 kb file size than 28 MB.
but this is your choice of course :LOL:

btw. your file is "empty" and size should be ca. 10 kb max but it has ca. 500 kb
 
Last edited:

ngolinda408

New Member
Joined
Jul 12, 2019
Messages
10
28 MB ?!
for the future try to split data to the source layer(s) and presentation layer(s) or use any simple DB (eg. MS Access)
and remember about backup. Easier is recreate 200 kb file size than 28 MB.
but this is your choice of course :LOL:

Yeah I’ll definitely be doing that, thanks for the tips. I archive off a copy of the file every so often but I think your idea of splitting up the file will be more beneficial. It’s a headache when anything goes wrong. :(
 

MoshiM

Active Member
Joined
Jan 31, 2018
Messages
371
Office Version
  1. 2016
Platform
  1. Windows
Sorry MoshiM, I'm not on the level as you are...more like a yellow belt in excel....whats a BLPHs? And I tried to put that in the VBA module and hit Run but nothing happened...
Can you help me out and guide me a little, please? Thanks!

It makes all names in the Name Manager visible.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,356
Messages
5,624,219
Members
416,017
Latest member
moritz210

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