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