SOLVED

Remove external references

%3CLINGO-SUB%20id%3D%22lingo-sub-1561164%22%20slang%3D%22en-US%22%3ERemove%20external%20references%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561164%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20file%20shows%20links%20to%20two%20external%20Excel%20workbooks%20which%20I%20want%20to%20remove.%26nbsp%3B%20I%20have%20followed%20the%20instructions%20to%20find%20external%20references%20(formulas%2C%20Charts%2C%20Text%20Objects%2C%20Named%20Ranges)%20and%20can't%20find%20them%20anywhere.%26nbsp%3B%20I'm%20using%20M365%2C%20Windows%2010.%26nbsp%3B%20The%20file%20attached%20is%20anonymised%20ie%20not%20the%20live%20one%20so%20I%20could%20do%20with%20knowing%20where%20the%20links%20are%20so%20I%20can%20remove%20them%20from%20the%20live%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1561164%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561228%22%20slang%3D%22de-DE%22%3ESubject%3A%20Remove%20external%20references%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561228%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747170%22%20target%3D%22_blank%22%3E%40StephenMB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHello%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EAfter%20translation%2C%20you%20want%20to%20remove%20the%20links.%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EHere%20is%20an%20example%20with%20VBA%2C%20press%20the%20button%20and%20all%20hyperlinks%20in%20the%20workbook%20are%20gone.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20find%20this%20helpful%2C%20please%20mark%20it%20as%20Like%20(click%20thumbs%20up)%20and%20as%20%22Best%20Answer%22%2C%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561336%22%20slang%3D%22en-US%22%3ERE%3A%20Remove%20external%20references%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561336%22%20slang%3D%22en-US%22%3EI%20highly%20recommend%20this%20tool%3A%20%3CA%20href%3D%22https%3A%2F%2Fmanville.org.uk%2Fsoftware%2Ffindlink.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fmanville.org.uk%2Fsoftware%2Ffindlink.htm%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561825%22%20slang%3D%22en-US%22%3ERE%3A%20Remove%20external%20references%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561825%22%20slang%3D%22en-US%22%3ESorted!%3C%2FLINGO-BODY%3E
New Contributor

My file shows links to two external Excel workbooks which I want to remove.  I have followed the instructions to find external references (formulas, Charts, Text Objects, Named Ranges) and can't find them anywhere.  I'm using M365, Windows 10.  The file attached is anonymised ie not the live one so I could do with knowing where the links are so I can remove them from the live file

4 Replies

@StephenMB 

Hello,
After translation, you want to remove the links.
Here is an example with VBA, press the button and all hyperlinks in the workbook are gone.

 

 

If you find this helpful, please mark it as Like (click thumbs up) and as "Best Answer", it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

best response confirmed by StephenMB (New Contributor)
Solution
I highly recommend this tool: https://manville.org.uk/software/findlink.htm
Sorted!
Hello @NikolinoDE

This thread seems to apply to an issue I am seeing, but I have to ask in a more detailed fashion. I have Excel365 Workbooks (though this issue has appeared in earlier versions). I have quarterly editions of the database and each new edition is copied from the prior quarter and given a new location and name.

The pivot tables show the data source as an external link to the location of the data. (i.e 'https://DOMAIN-my.sharepoint.com/personal/USER/Documents/PATH/[PRIOREDITION.xlxs#NUMSTRING=1.0]TABNA...

I want it to just continue pointing to the 'TABNAME'!Cellref internal to the file. I can do this manually by going into every pivot table and change source data, but that is a lot of effort for the number of tables. The name manager includes only internal references in the scope of workbook.

The Queries & Connections TAB lists 0 Connections but then existing connections on the Get & Transform Data TAB shows the name of the PRIOR EDITION with a list of tables.

To be clear, I want it to stop plugging in external references for each copy (which should be a no brainer for Excel, but isn't). Do you know of a way to do this, and if not, does your tool provide the desired outcome, or does it replace all of the links with static values?

Thank you for your assistance.