+ Reply to Thread
Results 1 to 17 of 17

Can't find external links or remove them.

  1. #1
    Forum Contributor
    Join Date
    11-14-2004
    Location
    Watford, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    129

    Can't find external links or remove them.

    Hi All,

    I have a file that keeps on asking to update external links every time I open it. I have gone to the "Data" - "Edit Links" and tried to break links and edit links but it still keeps asking me to Update Links each time I open the file.

    links.JPG

    I know I can turn the Updates off but I'm unsure why it keeps asking me this question when I can find no external links.

    Thanks

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Can't find external links or remove them.

    Hi,

    Check for links embedded in range names or conditional formats. They're often the reason for not immediately being able to discover them.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    11-14-2004
    Location
    Watford, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    129

    Re: Can't find external links or remove them.

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Check for links embedded in range names or conditional formats. They're often the reason for not immediately being able to discover them.
    Thanks for the reply.....

    Had a look and could not find anything

  4. #4
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Can't find external links or remove them.

    From TechNet MS

    1. vb script - to update all links then all links to break
    Please Login or Register  to view this content.
    Remarks:
    a. "C:\Temp\Test.xls" - enter your own data
    b. from "vbs_upd_brk.vbs.txt" remove ".txt", then double click

    2. Also: https://support.microsoft.com/pl-pl/...zard-available


    Others

    1. code to remove all hyperlinks
    Please Login or Register  to view this content.
    2. code to remove all defined names
    Please Login or Register  to view this content.
    3. Also e.g. : https://www.exceltrick.com/how_to/ho...nces-in-excel/
    Please Login or Register  to view this content.
    4. Also e.g.: https://www.ozgrid.com/forum/forum/h...sheet?t=168864
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mjr veverka; 10-12-2017 at 11:59 PM.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Can't find external links or remove them.

    Hi fastcar,

    It looks like you can use the following. No VBA needed.

    https://support.office.com/en-us/art...5-54313a532ff1

    Or
    http://access-excel.tips/find-extern...-broken-links/
    for more hints.
    Last edited by MarvinP; 10-12-2017 at 11:56 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Can't find external links or remove them.

    Do you have any charts in the workbook? Better still upload the workbook so that we can give it the once over.

    Incidentally would you add a location to your profile. It often helps when we need to consider stuff like regional settings for list separators, time zones, date formats etc..

  7. #7
    Forum Contributor
    Join Date
    11-14-2004
    Location
    Watford, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    129

    Re: Can't find external links or remove them.

    Quote Originally Posted by Richard Buttrey View Post
    Do you have any charts in the workbook? Better still upload the workbook so that we can give it the once over.

    Incidentally would you add a location to your profile. It often helps when we need to consider stuff like regional settings for list separators, time zones, date formats etc..
    Hi There,

    Thanks for the reply, I would upload the file but an cautious about confidentiality. I will update my profile

  8. #8
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Can't find external links or remove them.

    Quote Originally Posted by fastcar View Post
    Hi There,

    Thanks for the reply, I would upload the file but an cautious about confidentiality. I will update my profile
    Depending on your level of comfort, its likely you can simple share the file with us stripped on all of the content on the sheets. If your willing make a copy of the file, on each sheet select all and delete. This will actually tell you/us something in the process.

    If doing this gets rid of the external link then it was a sheet level issue, we then just have to find where. If the external link is still present then its not sheet level. It could be in a named range, conditional format, data model, data connection, etc and those all would still be in the file for us to evaluate without really showing us any information that could be sensitive (unless people make a habit of hand jaming information into named ranges).

    Otherwise what version of Excel is it? If your in 2013+ then it includes an addin called Inquire. Enable it, then use the Workbook analysis button. Go through and check off anything that seems relevant to external links. For example, formulas, Conditoonal formats, named ranges, etc. Leave out things like with numeric constants.

    This generates a report for you, with a tab for each check box you checked. This can make it easier to find the external link and where it is stored.

    The problem here is that the external link is being used some place that prevents its removal as Excel cant just replace its value in a cell.

  9. #9
    Forum Contributor
    Join Date
    11-14-2004
    Location
    Watford, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    129

    Re: Can't find external links or remove them.

    Quote Originally Posted by Richard Buttrey View Post
    Do you have any charts in the workbook? Better still upload the workbook so that we can give it the once over.

    Incidentally would you add a location to your profile. It often helps when we need to consider stuff like regional settings for list separators, time zones, date formats etc..
    Hi There,

    Thanks for the reply, I would upload the file but an cautious about confidentiality. I will update my profile

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Can't find external links or remove them.

    You must remove any confidential material before uploading it here - this is a public forum.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  11. #11
    Forum Contributor
    Join Date
    11-14-2004
    Location
    Watford, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    129

    Re: Can't find external links or remove them.

    Hi All,

    I have tried a few of the option above with no success but I have some more info.

    If I copy the file to another folder and open it I do not get the "Update external Links" popup however once I have saved it and try to reopen it the popup comes up.

    I have also noted this.
    Capture.JPG
    These macros are all in the same file as the filename is "ADC File 1.7.7.xlsm" but I have a feeling this is where the issue is.

    Any thoughts.

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Can't find external links or remove them.

    Have you examined the macros? Are any building new links perhaps?

  13. #13
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Can't find external links or remove them.

    Bingo, you have macros referencing another file. Delete them and you no longer get the warning more than likely. From your Op you no longer had access to that file anyway to update the external link so those macros wouldnt have run for you anyway, the code for them is not within the workbook. If you need the code/macros to run in that file you either need to reestablish the path between it and the external file or copy the code into the file so the macros are within it, not external
    Last edited by AliGW; 10-21-2017 at 02:24 AM. Reason: Unnecessary quotation removed.

  14. #14
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Can't find external links or remove them.

    Let me better explain. When looking at that dialog a macro within the file wont have the file name in front of it.

    IE if the macros name is MyMacro

    -in the file = MyMacro
    -external = externalfilename.xlsm!MyMacro

  15. #15
    Forum Contributor
    Join Date
    11-14-2004
    Location
    Watford, England
    MS-Off Ver
    Microsoft Office 365
    Posts
    129

    Re: Can't find external links or remove them.

    The thing is all the macros are in the workbook and work fine if I update or don't update links.

    modules.JPG

    module2.JPG

    This is what the Macro's consist of.
    Last edited by AliGW; 10-21-2017 at 02:25 AM. Reason: Unnecessary quotation removed.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Can't find external links or remove them.

    Guys - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Most of the time, quoting is completely unnecessary. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  17. #17
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Can't find external links or remove them.

    I think you'd better upload the workbook so that we can see the problem in context. Pictures are rarely much use and often offputting.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-27-2017, 04:29 PM
  2. Replies: 15
    Last Post: 08-27-2016, 06:48 AM
  3. Find all external links in workbook?
    By OldManExcellor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2014, 03:08 AM
  4. How to remove external links from formulas
    By Thomas Andrews in forum Excel General
    Replies: 2
    Last Post: 06-10-2014, 03:38 AM
  5. Completely remove/delete all the external links (Break Links)
    By SunOffice in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2014, 05:31 AM
  6. [SOLVED] best way to remove external links
    By Gary Keramidas in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 12-21-2005, 04:10 AM
  7. [SOLVED] How do I remove external links from Excel 2000 workbook?
    By mlwest in forum Excel General
    Replies: 2
    Last Post: 02-04-2005, 08:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1