Excel Options for updating external links

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.

 

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA, Uncategorized and tagged . Bookmark the permalink.

5 Responses to Excel Options for updating external links

  1. hello says:

    There’s no Edit Links button on the Data tab on the ribbon in Excel for mac 2011. I don’t want to be alerted so I uncheck ‘ask to update automatic links’ under the Edit icon in Excel preferences. Then in vba set Application.DisplayAterts = False so it doesn’t bother me with “cannot find…..”

  2. I don’t have Excel for Mac anymore, but I thought it was in the Edit menu? Excel 2011 doesn’t have a Ribbon as I recall.

    According to this post it’s in Excel 2016 for Mac:
    http://answers.microsoft.com/en-us/mac/forum/macexcel/linked-spreadsheets/b8bb3ba7-b212-45d1-b0fb-0079c01cab8d
    “The command has been moved to the Data menu & appears as Edit Links. There’s also an Edit Links button on the Data tab of the Ribbon.”

    I don’t think it’s a good idea to set Application.DisplayAlerts = False, you could miss important prompts.

    • hello says:

      Excel for mac 2011 has a riibbon, but the vbe doesn’t have a ribbon. Edit links is not in either Edit menu. Sett Application.DisplayAlerts = True after the workbook opens.

  3. Zachary Morris says:

    Hello, is it possible to replace links in excel if I list them under column A and revised links will be under column B. If so, can you please provide a sample VBA code for this? Let’s say there are 5 external links. But in some instances I have over 10 links in workbook so updating them manually is very tedious. thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s