Extract Embedded XLS from Word DOC

[gregcscott]

Hi,

this is my first time posting, and I am looking for a way to auto process a batch of documents I have. It is a standard purchase order doc, with an embedded XLS in it, and I need to roll up all of the data. So I want to run a macro on a doc, have it save the embedded XLS as its own file with the additional structure of "_data.xls" added onto it. I have looked around the web for this, and patched together some of my own code with others and this is what I have:


Sub Extract_XLS()

Dim xlWorkbook As Excel.Workbook
Dim oDoc As Document
Dim oDcOle As Word.OLEFormat
Dim strDocName As String

ActiveDocument.Shapes("Object 5").Select
Selection.ShapeRange(1).OLEFormat.DoVerb VerbIndex:=1

strDocName = ActiveDocument.FullName
Set oDoc = ActiveDocument

Set xlWorkbook = oDcOle.Object

intPos = InStrRev(strDocName, ".")
strDocName = Left(strDocName, intPos - 2)
strDocName = strDocName & "_data" & ".xls"

xlWorkbook.SaveAs FileName:=strDocName
xlWorkbook.Close

Set xlWorkbook = Nothing
Set oDoc = Nothing
Set oDcOle = Nothing
End Sub


This runs until the bolded line, it successfully opens the embedded object in excel, but does not get any further. This is a time sensitive issue, and any expedited help would be greatly appreciated.

[fumei]

With just a quick glance (i.e not actually worked on)...

Dim oDcOle As Word.OLEFormat

Selection.ShapeRange(1).OLEFormat.DoVerb VerbIndex:=1

Set xlWorkbook = oDcOle.Object

You are trying to Set xlWorkbook to an object that has NOT been Set. oDocOle = Nothing. It has nothing. Now I am not sure if this will work, but try it.
Dim oDcOle As Word.OLEFormat
Dim strDocName As String

ActiveDocument.Shapes("Object 5").Select
Set oDocOle = Selection.ShapeRange(1).OLEFormat.DoVerb VerbIndex:=1

Set xlWorkbook = oDcOle.Object
or maybe even:
Dim oDcOle As Word.OLEFormat
Dim strDocName As String

ActiveDocument.Shapes("Object 5").Select

strDocName = ActiveDocument.FullName
Set oDoc = ActiveDocument

Set xlWorkbook = Selection.ShapeRange(1).OLEFormat.DoVerb VerbIndex:=1
I do not have time to work on this right now, but possibly make a new instance of Excel...........




wait a second. Oh, never mind. I was going to say you do not have an instance of Excel, but you do, from the DoVerb instruction. However, perhaps make an explicit instance of Excel, then the DoVerb (which puts the object into Excel), then use the explicit instance of Excel to do the Excel stuff.

[TonyJollans]

Gerry seems to have it but not thought it through - oDcOle is not set. This should do it:

ActiveDocument.Shapes("Object 5").Select
Set oDcOle = Selection.ShapeRange(1).OLEFormat
oDcOle.DoVerb VerbIndex:=1

strDocName = ActiveDocument.FullName
Set oDoc = ActiveDocument

Set xlWorkbook = oDcOle.Object