How to find embedded workbook

[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

 

[Stephen Bullen]

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