04-17-2012, 09:35 AM #1  Forum Contributor
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.
04-17-2012, 09:45 AM
#2 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
04-17-2012, 10:18 AM
#3 Forum Contributor
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.
04-17-2012, 10:36 AM #4 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.
04-17-2012, 10:36 AM #5 Forum Contributor
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.
11-15-2012, 08:20 PM
#6 Registered User
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
12-28-2012, 02:52
PM #7 Registered User
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.
04-03-2013, 02:39 AM #8 Registered User
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.
09-10-2013, 05:38 PM
#9 Registered User
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.
Last edited by jeffreybrown; 09-10-2013 at 06:29 PM. Reason: Code tags added.
11-09-2014, 05:44 PM
#10 Registered User
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-09-2014, 05:48 PM #11 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-16-2014, 12:36 PM #12 Registered User
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.
04-14-2015, 01:26 PM #13 Registered User
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!
07-06-2015, 10:24 PM #14 Registered User
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!!
09-10-2015, 04:55 PM #15
Registered User
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.
12-22-2015, 03:42 PM #16 Registered User
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:- Cells formulas
- Cell conditional formatting
- Cell data validation
- Chart formulas
- Shape formulas
- Shape assigned macros
- Form Control linked cells
- Pivot Table data sources
- Regular Table data sources
- 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!
01-22-2016, 10:21 AM
#17 Registered User
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.
02-03-2016, 03:26
PM #18 Registered User
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.
03-03-2016, 07:18 PM #19 Registered User
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 Last edited by oleost; 03-03-2016 at 07:20 PM.
09-24-2016, 09:14
PM #20 Forum Contributor
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.
11-01-2016, 09:17 AM
#21 Registered User
Re: Find the links for the message "This workbook contains links to other data sources." This VBA code works for me: About me: http://qnqcontainer.com
07-20-2017, 12:08 AM
#22 Registered User
Re: Find the links for the message "This workbook contains links to other data sources."
11-04-2021, 02:20 PM
#23 Registered User
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?
11-05-2021, 12:34 AM
#24 Re: Find the links for the message "This workbook contains links to other data sources." Originally Posted
by Veloce166 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
03-21-2022, 08:01 AM #25
Registered User
Re: Find the links for the message "This workbook contains links to other data sources."
|
