0

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
Dalmar
  • 3
  • 2
  • `Workbooks("SalesFile").Sheets(` etc. would work to reference to the open workbook. – Plutian Oct 24 '19 at 09:15
  • Possible duplicate of [Reference an excel sheet from another workbook without copying the sheet](https://stackoverflow.com/questions/31697629/reference-an-excel-sheet-from-another-workbook-without-copying-the-sheet) – Plutian Oct 24 '19 at 09:16
  • "SalesFile" is not the name of the Workbook, but the name of the object, so I dont think I can reference it like that. – Dalmar Oct 24 '19 at 10:39
  • It wasn't as straightforward as I assumed it to be, but after some rigorous testing I figured it out for you as per my answer. – Plutian Oct 24 '19 at 12:14

1 Answers1

0

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.

Plutian
  • 2,276
  • 3
  • 14
  • 23