vấn đề là tất cả các hướng dẫn của mọi người đều ko thực hiện được vì tất cả các nút đó đều ẩn, ngay từ đầu nó đã hiện nên 3 lựa chọn Update, don't update và help rồi, ấn cái nào cũng ko được. Mình muốn lấy lại toàn bộ dữ liệu ở file đấy thì làm sao đc đây Show
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
Steps to do what I did.
And my problem was gone. Thanks for your input rvasquez. - 11-15-2012, 08:20 PM 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 PMRe: 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 AMRe: 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 PMRe: 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 PMRe: 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 PMRe: 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 Regards Ford 12-16-2014, 12:36 PM 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 PMRe: 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 PMRe: 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 PMRe: 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 PMRe: 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:
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 AMRe: 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 PMRe: 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 PMRe: 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 PMRe: 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 AMRe: 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 AMRe: Find the links for the message "This workbook contains links to other data sources."- 11-04-2021, 02:20 PMRe: 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 AMRe: Find the links for the message "This workbook contains links to other data sources." |
