How do I extract an embedded EXCEL worksheet from a word document.

[Benny YC Yu]

I am writing a VB program in EXCEL to extract certain information from a word
document. There are some EXCEL worksheet embedded in the word document (it is
not a link), and I want to extract these EXCEL worksheet. I have tried to
find the worksheets in the paragraph object without success.
I would be appreciated if someone can let me know what object under the word
document would allow me access to these embedded worksheets.

 

[Jezebel]

Depending on how the document was put together, the embedded objects will be
in the Shapes or InlineShapes collections. Unless you know in advance,
you'll need to iterate both collections. In both cases you'll need to check
the Type property of each item you find -- both collections can contain many
types of object. Embedded OLE objects in the Shapes collection have Type =
msoEmbeddedOLEObject; InlineShapes have Type =
wdInlineShapeEmbeddedOLEObject.

 

[Benny YC Yu]

Thank you Jezebel, I found the EXCEL is in the InlineShapes object. As you
can see from the codes that I can retrieve the name of the original EXCEL
workbook. But I have trouble try to point to the embedded EXCEL worksheet so
I can extract the information from it. I have tried 'Set wkbk =
inShapes.object' but it would not take it.

Sub test()
Set wddoc = GetObject("d:\data\benny\doc1.doc")

For Each inShapes In wddoc.InlineShapes
EXCELFileName = inShapes .OLEFormat.iconlabel
Next inShapes
End Sub