There are many options in Excel that control this depending on the kind of link it is. I’ll cover the usual external links in formulas first, then finish up with the other kinds.
An external link formula shows the full path to the source if that workbook is closed, otherwise it shows only the workbook name in the path. For more information, see the MS article ‘Create an external reference (link) to a cell range in another workbook’
='F:\Spreadsheets\[LinkSource.xlsx]Sheet1'!$B$3
='F:\Spreadsheets\LinkSource.xlsx'!Myrange
You can link to cell addresses or names; obviously named ranges are safer because addresses can be affected by insertions or deletions in the source worksheet. To find where links are used in a workbook, search in formulas for “.xl*!”; that is, an Excel file name followed by an exclamation mark.
As well as intentionally created links you enter in formulas, links can be created by Excel when you copy sheets from one workbook to another, and those sheets contain references to other sheets either obviously in formulas or less obviously in range names. If you get a prompt to update links when you open a workbook, but you cannot find any cells containing links, the link is probably in a name. Use the Name Manager (either Excel’s own or the free third-party JKP Name Manager ) to check all names for links, particularly phantom links which are not used but clutter up the list.
To control whether values are updated when the workbook containing the links (the ‘target’ workbook) is opened, use the Edit Links button in the Data tab of the Ribbon. Click the ‘Startup Prompt’ button, and select one of these three:
1: Let users choose to display the alert or not
Now, what happens at startup depends on another setting:
Options > Advanced > General: Ask to update automatic links. (VBA: Application.AskToUpdateLinks). If true, the user is alerted and can choose whether to update or not. If false, the links are updated.
2: Don’t display the alert and don’t update automatic links
Links are not updated at startup, regardless of the setting of Application.AskToUpdateLinks.
3: Don’t display the alert and update links
Links are updated at startup, regardless of the setting of Application.AskToUpdateLinks.
In VBA, ThisWorkbook.UpdateLinks has the values 1,2, or 3 above.
If the source workbooks are open at the same time as the target, the formulas are part of the recalculation chain as normal. The link values from closed files are not refreshed in a recalculation. Excel provides no way to automatically refresh the values of external link formulas to CLOSED workbooks. To manually update values at any time while using a workbook, use Edit Links, and for each link click ‘Update Values’. You can add a macro that calls ActiveWorkbook.UpdateLink. To update all links, pass it the array of link sources as follows:
ActiveWorkbook.UpdateLink _
Name:=ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
If you omit the parameter, by default Excel uses xlLinkTypeExcelLinks. The other type of XlLInkType is xlLinkTypeOLELinks, see below for those.
If any links are bad, that raises an error. To update them one by one and trap the error:
Sub UpdateAllLinks()
UpdateLinks xlLinkTypeExcelLinks
UpdateLinks xlLinkTypeOLELinks
End Sub
Sub UpdateLinks(LinkType As XlLinkType)
Dim vSource As Variant
Application.DisplayAlerts = False ' comment out to see the prompt for missing link sources
For Each vSource In ActiveWorkbook.LinkSources(LinkType)
On Error Resume Next
Debug.Print vSource
Application.DisplayAlerts = False
ActiveWorkbook.UpdateLink Name:=vSource, Type:=LinkType
If Err <> 0 Then
Debug.Print "^Error "; Err; Err.Description
End If
Next vSource
Application.DisplayAlerts = True
End Sub
You could call that macro in the Worksheet_Calculate event to update link values every time a recalculation happens. That will slow down performance, particularly for links over a network rather than to local drives.
To automate the calling of this macro at regular intervals, you can use the .Ontime method. Again, be aware when you do this of the length of time it takes to update the values.
In the ThisWorkbook class module:
Sub Workbook_Open()
Auto_UpdateExcel Links
End Sub
In a standard module:
Sub Auto_UpdateExcelLinks()
UpdateExcelLinks
Application.OnTime DateAdd("s",10,Now()), "Auto_UpdateExcelLinks")
End Sub
Other ways to link to external data
OLE: Object Linking and Embedding can be used for other Office applications such as linking Word or PowerPoint to Excel. You could link Excel to Word if you like, odd though it looks. Copy the data in Word then in Excel Paste Special, Link, and select the format you want. ‘HTML’ suits Word tables, ‘Microsoft Word Document Object’ inserts an object with the formatting as in Word.
The Edit Links dialog allows you to choose Automatic or Manual update only for OLE links. Excel links always have the Manual option button greyed out. If it is automatic then it is refreshed live in Excel as soon as the source document is changed, even without saving the document. If it is manual then it can be updated through Edit Links, select the link, click Update Values.
The Excel setting ‘Options > Advanced > When calculating this workbook: Update links to other documents’ controls the updating of OLE links only. (VBA: ThisWorkbook.UpdateRemoteReferences ) When unchecked, OLE links do not update, even if you select ‘Update Values’ in the Edit Links dialog for that link.
Connections: set up by the Get External Data button. These create queries which are refreshed by the Refresh button. You can link to text files (eg CSV), other workbooks, databases, and web pages. For further details see MS support and many other websites:
https://support.office.com/en-us/article/Create-edit-and-manage-connections-to-external-data-89d44137-f18d-49cf-953d-d22a2eea2d46
RTD / DDE: Real Time Data and Dynamic Data Exchange. These are ways of getting data from COM instances on a server. For more information, see MS support and the Wilmott forums.
https://support.microsoft.com/en-us/kb/289150
Macros: Data can be read and written by VBA code with Workbooks.Open, Querytables, ADO and SQL. Worksheet shapes can contain links to macros in other workbooks, so clicking them may cause Excel to attempt to open those other workbooks.
