Open and Close linked file

[Naveen N]

Dear Reader,

I am using workbook open event to open the linked file. The linked workbook is running in background. I am not changing anything in the opened linked file.

I have a query, when I close my master sheet, will the linked workbook also get closed.

How do I close the opened link files, when the master sheet is closed?

The VBA code to open linked files.

Note: I needed to open the linked files since I have named ranges referring another workbook.

Private Sub Workbook_Open()

Dim LinkList As Variant
On Error Resume Next
LinkList = ThisWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(LinkList) Then
For i = LBound(LinkList) To UBound(LinkList)
Application.Workbooks.Open Filename:=LinkList(i), UpdateLinks:=False, ReadOnly:=True
ActiveWindow.Visible = False
Next i
End If

ThisWorkbook.Activate
End Sub

Regards,

Naveen N

 

[Luke M]

Put this also in the ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim LinkList As Variant
    Dim wbName As String
   
    Dim i As Long
    On Error Resume Next
    LinkList = ThisWorkbook.LinkSources(xlExcelLinks)
    On Error GoTo 0
    If Not IsEmpty(LinkList) Then
        For i = LBound(LinkList) To UBound(LinkList)
            wbName = LinkList(i)
            'Trim down to just workbook name, not full path
            wbName = Mid(wbName, InStrRev(wbName, "\") + 1)
           
            Application.Workbooks(wbName).Close savechanges:=False
        Next i
    End If
   
    ThisWorkbook.Activate
End Sub

Side note: To format the code, don't forget the square brackets on the [code] and [/code] tags.

 

[Awesome Wally]

@Luke M ,
I am trying to use the VBA code you guys have above but at close I get a Run-time error 9. Something with the

Application.Workbooks(wbName).Close savechanges:=False

Thanks in advance.

 

[Awesome Wally]

@Luke M
I figured out my mistake. I missed this piece about:

'Trim down to just workbook name, not full path

Great piece of code guys!