How to find embedded workbook

  • Thread starter Thread starter George Burdell
  • Start date Start date
G

George Burdell

Any suggestions on how to search a workbook for embedded workbooks? (and how
to activate them?) The macro recorder is not much help....

Thanks for any help,

George
 
Hi George,
Any suggestions on how to search a workbook for embedded workbooks? (and how
to activate them?) The macro recorder is not much help....

This does it for the active sheet, which I'm sure you can extend:

Sub test()

Dim oShp As Shape
Dim oEmbeddedWorkbook As Workbook

For Each oShp In ActiveSheet.Shapes

'Is it an embedded object?
If oShp.Type = msoEmbeddedOLEObject Then

'An Excel object?
If Left$(oShp.OLEFormat.progID, 11) = "Excel.Sheet" Then

'Activate it
oShp.OLEFormat.Activate

'Get access to the workbook in it
Set oEmbeddedWorkbook = oShp.OLEFormat.Object.Object

End If
End If
Next

End Sub


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top