Tom’s Tutorials For Excel: Updating Links (or Not)
You’ve no doubt seen this pop-up message upon opening a workbook:
To automatically update links sans VBA, if the work being done is always on your computer…
…In versions 2003 or before: Tools > Options > Edit > select Ask to update automatic links.
…In versions 2007 or after: Click the Office button or File tab > Options > scroll to the General section and select Ask to update automatic links.
Or if you want to disable the prompt before your macro opens your workbook, and enable it immediately thereafter, put this in your macro:
Application.AskToUpdateLinks = False Workbook.Open Filename:="C\Your\File\Path\YourFileName.xls" Application.AskToUpdateLinks = True
Or
Workbooks.Open _ Filename:="C\Your\File\Path\YourFileName.xls", UpdateLinks:=0
Note, arguments for UpdateLinks:
0 Does not update any references.
1 Updates external references only, not remote references.
2 Updates remote references only, not external references.
3 Updates both remote and external references.
To easily access your workbook module, in Excel versions 2003 or before, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the immediate left of the File menu option. Right click on that icon, and left click on View Code. In Excel versions 2007 or after, from your worksheet press Alt+F11, then press Ctrl+R, find your workbook name in the “Project – VBAProject” left vertical pane, expand the Microsoft Excel Object folder for your workbook, right-click on ThisWorkbook and left-click to select View Code. Paste the above procedure into the large white area that is the workbook module, then press Alt+Q to return to the worksheet.
I bet other people reading this will have ideas for more ways to update links or avoid updating them. As always, anyone is welcome to comment on what as worked best for them.
What is the purpose of;
Private Sub Workbook_Open()
ThisWorkbook.UpdateLink _ Name:=ThisWorkbook.LinkSources
End Sub
Hi Daniel, thanks for your question. There was an html error in that code line you referred to, which would have resulted in a run time error if that code was run as shown on the web page.
The line of code should have looked like this:
ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSourcesI’ve deleted that procedure example altogether from this blog entry because I’ve found that code line to be unreliable.
To answer your question, the code updates all links in the host workbook when said workbook opens. UpdateLink is an expression that returns a Workbook object. The LinkSources method returns an array of links in the workbook, the names of which are the names of the linked workbooks, that is, the link source(s).
Establishing links is something I practically never do in my project designs. Links always seem to find a way to eventually bedevil Excel users so I develop all my client projects without links. As with all my blog entries, anyone reading this is welcome and encouraged to post what they have found works for them for updating links.
FYI, when you want to delete phantom links, I also posted a blog entry here…
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-finding-and-deleting-phantom-links/
…for times when unwanted links exist but are difficult to find and delete.
Tom,
I am searching for a way to code an Excel 2010 workbook that will open only if a certain file is within a different folder. The purpose of this will be to have a hidden file on a computer root that is required in order to allow the workbook to open. The user would not be aware that this is present. This will further prevent unauthorized users from opening. Is this possible?
Hi and thanks for following my blog page. See if my blog post at the below link gets you closer to what you want to do. Notice in the code that the process stops if the particular workbook is not found in the specified path.
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tips-for-excel-how-to-verify-if-a-workbook-exists/
Thanks, exactly what I needed! However, for me (office 365) the Application.AskToUpdateLinks = False did not work. Only the option with UpdateLinks:=0 worked in my VBA code. But that is enough :-).
Thanks for that info, Erwin. I was not aware of that difference in 365. Good to know.