+ Reply to Thread
Results 1 to 25 of 25

Find the links for the message "This workbook contains links to other data sources."

  1. #1
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    Find the links for the message "This workbook contains links to other data sources."

    I get the following message when I open my workbook.

    ///////////////////////////
    Microsoft Excel

    This workbook contains links to other data sources.

    *If you update the links, Excel will attempt to retrieve the latest data.
    *If you don't update the links, Excel will use previous information.

    Note that data links can be used to access and share confidential information without your permission and possibly perform other harmful actions. Do not update the links if you do no trust the source of this workbook.
    ///////////////////////////
    I took out all external links but am still getting the message. Is there an easy way to see what I am missing?
    Last edited by 111StepsAhead; 04-17-2012 at 10:37 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Find the links for the message "This workbook contains links to other data sources."

    Hello there,

    There might be cells whose formulas are referencing another workbook. You can view all cell formulas by selecting the formulas tab and then selecting the Show Formulas option from Formula Auditing. The cells that reference another workbook should look something like the below formula.

    ='[add contents from column A to all other sheets.xlsm]Sheet1'!$A$1

  3. #3
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    Re: Find the links for the message "This workbook contains links to other data sources."

    I searched each worksheet and found no external links. If there is no way to find that link easily is there a way to ignore the warning automatically? i.e. Just pick "don't update the links"?

    Thanks for your help.

  4. #4
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Find the links for the message "This workbook contains links to other data sources."

    Try the below code to find all external links. Insert the code into a module as a macro. To do this press alt and f11 to bring up Visual Basic. Select Insert from the top menu and then select the module option. Paste the below code in the module. To run the code, exit out of Visual Basic and then select the Developer tab adn the Macros option. Select the ListLinks from the pop up and select Run. This code should produce a new worksheet with a list of all external links within the workbook.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    Re: Find the links for the message "This workbook contains links to other data sources."

    Using the built in help feature I ended up "breaking" the link. Hopefully that does not hurt me in the future all though after a visual search over each piece of data I found nothing.

    Steps to do what I did.

    1) Select the Data tab.

    2) Connections group

    3) Edit Links

    4) Startup Prompt

    5) Select the option you want. (I chose to break the link.)

    And my problem was gone. Thanks for your input rvasquez.

  6. #6
    Registered User
    Join Date
    05-23-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Find the links for the message "This workbook contains links to other data sources."

    Is there an option to include the cell and tab of which the external link is in?

    For example, "...external link..." Sheet3 A27

  7. #7
    Registered User
    Join Date
    12-28-2012
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    From the workbook that currently gives me this notice ("This workbook contains links . . ."), I used to link to data in another workbook. But I've removed all references to the other workbook, and I'm still getting the same notice.

    I used rvasquez's macro to find that there's still a link to the other workbook, but I've used ctrl+F to search the workbook for any reference to the other workbook, and the only reference is on the new worksheet, Range("A1"), created by rvasquez's macro.

    I suppose I should just break the link like 111StepsAhead did?

    I've love to know where Excel thinks the reference to the other workbook is, because if there really is one, I need to remove it. But I'm fairly sure there isn't one.

    P.S. As an alternative to running rvasquez's macro, you can just open the Visual Basic editor, go down to the immediate window, and type "print ActiveWorkbook.LinkSources(xlExcelLinks)(1)" to see the first external link.
    Last edited by maurile; 12-28-2012 at 02:57 PM.

  8. #8
    Registered User
    Join Date
    10-31-2012
    Location
    St Asaph
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Find the links for the message "This workbook contains links to other data sources."

    Rvasquez's macro worked for me (many thanks). To use the macro I first had to enable the developer tab on the toolbar. This is done by;

    On the File tab, choose Options to open the Excel Options dialog box.

    Click Customize Ribbon on the left side of the dialog box.

    Under Choose commands from on the left side of the dialog box, select Popular Commands.

    Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.

    Click OK.

    In the developer tab now shown on the toolbar, select "Visual Basic", click "Insert" from the toolbar, then "Module". In the new window copy and paste Rvasquez's macro. Next select "Run" from the Visual Basic toolbar, then "Run Macro". This put the found link on a new tab, I then did a search (whole workbook) using Ctrl + F, and removed the link.

  9. #9
    Registered User
    Join Date
    09-09-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    I found that Excel Find is not searching for links in the Data Validation cells, resulting in "hidden" links.
    Here is a macro that dumps the external links found in the Data Validation cells.
    only tested on Excel 2007.

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 09-10-2013 at 06:29 PM. Reason: Code tags added.

  10. #10
    Registered User
    Join Date
    05-02-2010
    Location
    Tampa
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Find the links for the message "This workbook contains links to other data sources."

    I realize this is an old post and a solution was found, but in case anyone else has the same problem.

    In my case the external link was within Data Validation. I cleared the data validation for the entire sheet and got rid of that annoying message.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,879

    Re: Find the links for the message "This workbook contains links to other data sources."

    Thanks for the feedback, jhg, that would have been a tough 1 to find
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  12. #12
    Registered User
    Join Date
    05-20-2014
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    After trying all these possible solutions, I was finally able to find one for my case.

    Even after checking the data validation of the workbook, the links of the workbook using the macro, etc., it turned out I had conditional formatting rules that were linked to another location. After removing them, the error no longer occurred.

    Hopefully this helps anyone who wasn't able to solve their problems from the above solutions.

  13. #13
    Registered User
    Join Date
    04-14-2015
    Location
    Florida
    MS-Off Ver
    2013
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    WOW! After spending an hour and a half looking for the link and trying everything in this thread, I also found it in my conditional formatting!

    Thank you danno161 - that was a life saver!

  14. #14
    Registered User
    Join Date
    07-06-2015
    Location
    Manila, Philippines
    MS-Off Ver
    2013
    Posts
    7

    Re: Find the links for the message "This workbook contains links to other data sources."

    Thanks guys - Similarly I spent a long time searching and after reading this thread found mine in the data validation. Thanks!!

  15. #15
    Registered User
    Join Date
    05-06-2013
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    I found my external link. It was in the macro assigned to a Button (Form Control). The trick that 111StepsAhead suggested in going to the Data Tab and selecting 'Edit Links' worked great. However, I was still left scratching my head as to where the link was and how it was caused.

    Only when I tried clicking on the button and finding nothing happened did I realize that that was the trouble. This discussion was great in helping me find and stamp out an irritating issue. Thanks.

  16. #16
    Registered User
    Join Date
    06-03-2015
    Location
    NC, USA
    MS-Off Ver
    2010, 2013
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    After a little inspiration from this post (thanks bgwong2476), I've written some code that searches the workbook for any external links, and pastes pertinent information about them into a new sheet. So far it checks:
    1. Cells formulas
    2. Cell conditional formatting
    3. Cell data validation
    4. Chart formulas
    5. Shape formulas
    6. Shape assigned macros
    7. Form Control linked cells
    8. Pivot Table data sources
    9. Regular Table data sources
    10. Named Ranges RefersTo formulas

    Hopefully, this will save everyone a lot of time in the future. I know it's already saved me some time on a few occasions! And feel free to let me know if I've left something out!

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    01-22-2016
    Location
    Texas
    MS-Off Ver
    2013
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    Props to McMonkMonk! Your code found a bunch of named ranges referencing an external workbook. Thank you so much for your made code.

  18. #18
    Registered User
    Join Date
    12-03-2012
    Location
    Central IL
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    McMonkMonk, I believe you forgot a line of code. In the Conditional formatting section, r=r+1 needs to be added like the rest of instances.

  19. #19
    Registered User
    Join Date
    03-03-2016
    Location
    Norway
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    Hi. Tried everything in this post. But still no go.

    Tried running latest McMonkMonk codes, it does list alot. But not the location.

    Any ideas?

    Edit: Searched google for what names are, found it in:

    Change a Named Range
    After you create a named range, you might need to change the cells that it refers to. Follow these steps to change the range reference:

    On the Ribbon, click the Formulas tab
    Click Name Manager
    Attached Images Attached Images
    Last edited by oleost; 03-03-2016 at 07:20 PM.

  20. #20
    Forum Contributor
    Join Date
    06-02-2005
    Posts
    100

    Re: Find the links for the message "This workbook contains links to other data sources."

    I know this is an old post, but it was one of the top hits when googling for this problem.

    Wanted to add one more place where I found a link that hasn't been mentioned yet that I saw:

    Inside the Name Manager (Formulas -- Defined Names -- Name Manager)

    Dunno how a link to an external source got created in there, but there it was.

  21. #21
    Registered User
    Join Date
    11-01-2016
    Location
    HCM
    MS-Off Ver
    2016
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    This VBA code works for me:

    Please Login or Register  to view this content.
    About me: http://qnqcontainer.com

  22. #22
    Registered User
    Join Date
    08-19-2013
    Location
    Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    Amazing... it's solve my problem.

  23. #23
    Registered User
    Join Date
    11-04-2021
    Location
    London,England
    MS-Off Ver
    365
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    This is great, it returns a reference to an external link. However I still can't find the location of that external link in my Excel spreadsheet. Can the macro be modified to return the precise location of the link? If I can find it I can work out if it should be there and if so what to do about it. Is it in a formula, Name Manager, conditional formatting or where?

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,879

    Re: Find the links for the message "This workbook contains links to other data sources."

    Quote Originally Posted by Veloce166 View Post
    This is great, it returns a reference to an external link. However I still can't find the location of that external link in my Excel spreadsheet. Can the macro be modified to return the precise location of the link? If I can find it I can work out if it should be there and if so what to do about it. Is it in a formula, Name Manager, conditional formatting or where?
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  25. #25
    Registered User
    Join Date
    03-21-2022
    Location
    India
    MS-Off Ver
    365
    Posts
    1

    Re: Find the links for the message "This workbook contains links to other data sources."

    Very useful!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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