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.

 

[ngolinda408]

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

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!

 

[ngolinda408]

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

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]

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?

 

[ngolinda408]

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?

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]

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

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

 

[ngolinda408]

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

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.

 

[sandy666]

loss of data often leads to loss of money

have a nice day

 

[MoshiM]

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.