VBA Code to Update Links if Linked-to Workbook Closed

Question

I have a workbook within which there are links to number of other workbooks. Ideally I'd like to code: When I open the workbook containing the links, will update the links, but only if the workbook that is being linked to is not open (either on my PC or another user's PC on the network, that can access it). This is because:

• a) if the workbook is already open the link should update anyway,
• b) updating links to workbooks that are already open seems to cause errors.

I hope I'm making sense, please tell me if I'm not. Thanks.

Answer 1

Try this

Sub UpdateLinks()
    Dim v As Variant, i As Long
    v = ThisWorkbook.LinkSources(XlLink.xlExcelLinks)
    For i = 1 To UBound(v)
        If Not FileInUse(v(i)) Then
            Workbooks.Open (v(i))
        End If
    Next i
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
End Sub

Public Function FileInUse(sFileName) As Boolean 'Checks if a workbook is open
    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