VBA Update External Link cause Run Time Error

Question

A very straightforward VBA code to update the excel external link is

ThisWorkbook.UpdateLink Name:=ThisWorkbook.linkSources, Type:=xlLinkType.xlLinkTypeExcelLinks

But I find one thing here, and I don't find any docs online to explain this. If the linked excel is open, or to say if the linkStatus is xlLinkStatusSourceOpen, the above code will cause run time error: 1004, "Method UpdateLink of Object _Workbook failed" You can easily replicate the error I think. And if you manually go to the Data tab on the Excel Ribbon, click Edit links, click update Values. Everything fine. The inconsistency of the VBA behavior and excel manually operation behavior is weird. Somebody knows why? Thanks

Answer 1

If the source workbook is open then

Application.Calculate

will do the job for you !

---

Or technically, the longer code to understand and resolve the issue would be below:

Public Function IsWorkbookOpen(sFileName) As Boolean
    On Error Resume Next
    Open sFileName For Binary Access Read Lock Read As #1
    Close #1
    FileInUse = IIf(Err.Number > 0, True, False)
    On Error GoTo 0
End Function


Sub Update_Links()
    Dim wbkPath As String

    'Update formulas/links for all the open source workbooks
    Application.Calculate
    For Each wbkPath In ThisWorkbook.LinkSources
        'Update formulas/links for all the closed source workbooks
        If Not IsWorkbookOpen(wbkPath) Then
            ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources, Type:=XlLinkType.xlLinkTypeExcelLinks
            Exit For
        End If
    Next

End Sub