Problems Refreshing a Workbook

When David opens a workbook, a message appears that says, "Unable to Refresh: We couldn't get updated values from a linked workbook." Next to this is a button to manage workbook links. David can click the button and go through the refresh process. His master workbook has over 2,500 links to about 100 other workbooks, so every day David spends time reconnecting links manually because the refresh does not work completely. This began about a year ago, and he has assumed Microsoft is aware of the problem and working on a solution. Now he is wondering if he is wrong and how long he should expect to spend time reconnecting.

Most people learned years ago that it does no good to wait for Microsoft. They will act when they feel it appropriate to act, and that timing typically cannot be affected unless you are a customer that orders 25,000 copies of Microsoft Office annually. (I'm not being cynical, just practical—large companies realistically only pay attention to very large customers. The rest of us are simply along for the ride.)

That being said, it can be frustrating for links to not update as you expect, especially with so many! One thing you might try is to turn off automatic updating of links by following these steps:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and later versions display the File tab of the ribbon and then click Options.)
2. Click Advanced at the left of the dialog box and scroll down to the General section. (See Figure 1.)



Figure 1. The Advanced options in the Excel Options dialog box.

3. Clear the Ask To Update Automatic Links check box.
4. Click on OK.

Now, open the workbook that you've historically had problems with. Excel will not try to update the links. At this point you could run a macro such as this one:

Sub RefreshExternalLinks()
    Dim link As Variant

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    On Error Resume Next
    For Each link In ThisWorkbook.LinkSources(xlExcelLinks)
        ThisWorkbook.UpdateLink Name:=link, Type:=xlExcelLinks
    Next link
    On Error GoTo 0
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Refresh completed.", vbInformation
End Sub

The macro steps through every link and updates it. If an error occurs in trying to update the link, then Excel skips that one and goes on to the next one. The result is that you should end up with all the links updated that can be updated.

The problem, of course, is that the macro doesn't identify what link(s) may be causing the problem. If you want some help in figuring that out, the following variation of the macro might fit the bill:

Sub RefreshLinksReport()
    Dim link As Variant
    Dim sMsg As String

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False

    sMsg = "Refresh completed." & vbCrLf & vbCrLf
    For Each link In ThisWorkbook.LinkSources(xlExcelLinks)
        Err.Clear
        On Error Resume Next
        ThisWorkbook.UpdateLink Name:=link, Type:=xlExcelLinks
        On Error GoTo 0

        If Err.Number <> 0 Then
            sMsg = sMsg & "Failed to update link:" & vbCrLf & _
              "  " & CStr(link) & vbCrLf & _
              "  Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf
        End If
    Next link

    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox sMsg, vbInformation
End Sub

If there is an error opening a link, a message is added to the sMsg string indicating the error. When the macro is done running, you'll see a report that indicates what errors, if any, occurred. This can help you identify what the exact problem is and the link where the problem arose.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13126) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.