[SOLVED] Modify embedded Excel workbook in another Excel workbook VBA

Modify embedded Excel workbook in another Excel workbook VBA

I have an Excel workbook in which I have embedded another Excel workbook. I am able to open it with VBA, but I have no idea how to refer and edit some cells in embedded workbook. Any idea how to do that? Thanks a lot in advance.

Sub openembeddedXL2()

Sheets("sheet1").OLEObjects("SalesFile").Activate

End Sub

Solution

As long as a workbook is open you can directly refer to it by its name.

Workbooks("workbook name") etc.

Since you open the workbook with Sheets("sheet1").OLEObjects("SalesFile").Activate the workbook related to the object will then be opened as a file called "Worksheet in your current workbook". You can therefore use:

Dim wb as workbook
Sheets("sheet1").OLEObjects("SalesFile").Activate
set wb = Workbooks("Worksheet in " & ThisWorkbook.Name)
Thisworkbook.sheets("Sheet1").Range("A1").value = wb.sheets("Sheet1").range("A1").Value 'etc. etc.
wb.Close

Thisworkbook is a handy tool here, as it will always refer to the workbook the macro is in, despite which workbook is currently active.