# Microsoft Office Application Help - Excel Help forum > Excel General >  > [SOLVED] Find the links for the message "This workbook contains links to other data sources."

## 111StepsAhead

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?

----------


## rvasquez

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

----------


## 111StepsAhead

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.

----------


## rvasquez

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.
```

----------


## 111StepsAhead

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.

----------


## titan9999

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

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

----------


## maurile

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.

----------


## nxf

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.

----------


## bgwong2476

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.
```

----------


## jhg1226

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.

----------


## FDibbins

Thanks for the feedback, jhg, that would have been a tough 1 to find  :Smilie:

----------


## danno161

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.

----------


## charlie.sondag

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!

----------


## stevetalaga

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

----------


## rdedwards

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.

----------


## McMonkMonk

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 formulasCell conditional formattingCell data validationChart formulasShape formulasShape assigned macrosForm Control linked cellsPivot Table data sourcesRegular Table data sourcesNamed 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.
```

----------


## overclock

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

----------


## klungseth

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.

----------


## oleost

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

----------


## Ouka

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.

----------


## Rongxanh

This VBA code works for me:




```
Please Login or Register  to view this content.
```

----------


## RaVa

Amazing... it's solve my problem.  :Smilie:  :Smilie:  :Smilie:

----------


## 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?

----------


## FDibbins

> 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.  :Smilie: 

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

----------


## laxmikant.mishra2000

Very useful!!

----------

