[Solved] Open Embedded Excel Files Automatically

[Jlkirk]

I have a file ("Master File") that has several (>5) embedded/linked excel files ("Children") that in turn have several (>10) embedded/linked files as well ("Grandchildren"). What I woul like to happen is that when I open the Master File, all of the Children and Grandchildren would open automatically as well.
Any ideas?

 

[Seemanto Saha]

Hello Jlkirk,

Thanks for reaching us. I understand that you want to open all embedded/linked Excel files automatically when the master file is opened. According to your description, your Excel files include multi-level linking (children files and grandchildren files). To open all these linked files automatically, we can run a VBA Workbook Event macro.
​

Here, I have taken a Master File that is Linked to 3 children files.

​

Each of these 3 children files is linked to 3 grandchildren files.

​

Therefore, I have a total of 9 grandchildren files. Each of these grandchildren files is formatted in the following format:​

​

Now, to open all these files automatically, go to the Master File and press Alt + F11 keys to open the Visual Basic Editor window. Double Click on the ThisWorkbook option and enter the following code. Save the code.​

Private Sub Workbook_Open()

    Dim i As Integer
    Dim oleObj As OLEObject
    Dim embeddedFilePath As String
    
    For i = 1 To ThisWorkbook.Sheets.Count
        Set ws = ThisWorkbook.Sheets(i)
        For Each oleObj In ws.OLEObjects
            If InStr(1, oleObj.progID, "Excel.Sheet", vbTextCompare) > 0 Then
                oleObj.Activate
            End If
        Next oleObj
    Next i
    
    Dim wb As Workbook

    For Each wb In Application.Workbooks
        If wb.Name <> "MasterFile.xlsm" Then
            For i = 1 To wb.Sheets.Count
                Set ws = wb.Sheets(i)
                For Each oleObj In ws.OLEObjects
                    If InStr(1, oleObj.progID, "Excel.Sheet", vbTextCompare) > 0 Then
                        oleObj.Activate
                    End If
                Next oleObj
            Next i
        End If
    Next wb
    
End Sub

Now, exit the Workbook and re-open it. All children and grandchildren files will open consequently. Click on the following video link to view the opening of the linked files.

Video​

The given VBA macro should work for any number of linked children or grandchildren files. The files may also contain multiple worksheets.

Hopefully, this solution will resolve your problem. Let us know your feedback.
Regards,
Seemanto Saha
ExcelDemy

 

[Jlkirk]

I need your help...where do I put it in?

 

[Seemanto Saha]

Hello Jlkirk,

Open your master file. From the Developer tab click on the Visual Basic option or use the keyboard shortcut Alt + F11 to open the Visual Basic Editor.​

​

Afterward, double-click on the ThisWorkbook option and enter the code. Make necessary changes (name of the master file) and Save the code.​

​

Close the Workbook and re-open it. All the linked workbooks will open automatically.

Regards,
Seemanto Saha
ExcelDemy​

 

[Jlkirk]

I still cant get it to work...

 

[Seemanto Saha]

Hello Jlkirk,
Here is a video that shows the process of inserting the VBA macro in the workbook.

Video​

However, you have to Save your Master Workbook as a .xlsm file to get the required result. And if there is any problem with the VBA macro, please share your Master Workbook with us.

Regards,
Seemanto Saha