Forum Discussion
"This Workbook contains links to one or more external sources that could be unsafe."
Hello- I have spent months creating a file that documents my expereince hours so that I can apply for a board certification. We recently updated some formulas so that the spreadsheets would work better together.
Now all of a sudden we are getting the message mentioned in the subject line everytime we open the file. If you do choose the update button we LOOSE everything that we updated. Literally nine months of data!!!
We did not copy or paste any external links into the spreadsheet, ever. We manually typed in every formula and every data point.
The message will NOT SHOW us where these external links are located. It only tells us if we trust the links to then we can continue (by hitting Don't Update.) The problem is this message reoccurs 100% when you re-open the file. Those who we are sharing the file with also get this message and most do not know that they shoud hit Don't Update, and hit Update instead, thus reverting the file back to the old formulas and old data prior to the formula updates.
Is it really necessary to re-recreate the whole file and re-input nearly 14 months of data?
10 Replies
I know this is a few years old now, but I just had the same pop-up box everytime I opened one of my Profits & Losses Reports. After meticulously checking, double checking, and even breaking and reentering the formulas for the data, then I found the solution for my particular problem. The Workbook Links had been set to 'Ask to refresh'.
To fix this,
Click on File, then Info, then the 'Edit Links to Files', from there click on the settings gear, select 'Always Refresh'.
Problem solved with the annoying pop-up box saying "This Workbook contains links to one or more external sources that could be unsafe. If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have."
There was no problem with my data or the links, I'm guessing in an update MS changed that setting to 'Ask to Refresh'
Good day!
Have you tried the following options:
1) Page: https://support.microsoft.com/en-gb/office/customize-how-excel-starts-6509b9af-2cc8-4fb6-9ef5-cf5f1d292c19?ui=en-us&rs=en-gb&ad=gb#:~:text=Customize%20how%20Excel%20starts
Specific: https://support.microsoft.com/en-gb/office/customize-how-excel-starts-6509b9af-2cc8-4fb6-9ef5-cf5f1d292c19?ui=en-us&rs=en-gb&ad=gb#:~:text=Prevent%20automatic%20macros%20from%20running%20when%20you%20start%20Excel
or
2) In Excel review the settings at: File -> Options -> Trust Center -> External Content
Not sure if it solves your issue.I have seen this quite a few times.
Happens when you copy and paste data from one excel workbook to the other. Instead of pasting the data, excel pastes a link to the data. Possibly a manual error or the pasting settings in one of the paerson you share the file with.
If you ignore the security warning, the last data when copied would be there. If you click on update, the data would be retrived again from the linked document.
If you want to find the link, do this:
1. in find and replace box, type '.xls' and hit 'find all'. You'll get all links referring to xls and xlsx files.Once you find the cell, verify the existing data, then copy and paste as values on top of it.
Excel does that if you have formulas that refer to or from the tab that is moved away from the workbook. The only way out of it is to remove those links.
These may hide in many places. If you do not use buttons etc you can skip those and do the following:- search for http (if workbook was on sharepoint) or .xls if not - search should be for formulas. Modify the fields to remove the wrong ones
- look at data validation - some of those data validation settings may use formulas too. Remove ones that point to outside workbook
- look at conditional formatting - that too may have references to an external workbook.
Some of those things you can detect and replace with the VBA scripts much faster than by hand.
NowshadAhmed I'm having trouble with the pop up whenever I open an excel file about links to one or more external sources. I'd like just to get rid of it. I tried "find all xls" but it just comes up empty. Where should I look please?
- First, thank you for your help. When I click on Formulas, and then on Name Manager, all I get is Define Name, Create from Selection and Defines Names, none of which have anything in a sub menu. Can you please hold my hand through this process. I just want to skip the extra step when opening a file.
Why does the Trust Center settings (i.e., trusted locations) not prevent this security warning from appearing?
