We have several thousand historical MS Word documents with embedded MS Excel Worksheets.

This data predates a relational database system which now manages all this data. Sound familiar!

We need to create a spreadsheet which has a row for each embedded MS Excel Worksheet with columns for specified values. We have no problem aggregating multiple spreadsheets into a single spreadsheet.

We have been unable to automatically extract The MS Excel worksheets from the MS Word documents.

We have tried two general approaches:
Approach 1, Obtain Control of the Excel object from VB running inside Word
Approach 2, Select the Excel object inside Word and save as a separate file

Sample code for both approaches below.

Comments indicating which lines of code are working and not working.


Approach 1, Obtain Control of the Excel object from VB running from Word

'We launch the code from a form which will later loop through the 1000+ documents.

Private Sub CommandButton1_Click()

 

'Start a new instance of Microsoft Word with the target document

Dim oWordDoc As Word.Document 

Set oWordDoc = GetObject("C:\SourceDocument.doc", "Word.Document")

'This works, Word opens with the document.

 

Dim intCount As Integer

intCount = oWordDoc.InlineShapes.count

' This works, we see the single OLE object embedded.

 

Dim oInlineShape As InlineShape

Set oInlineShape = oWordDoc.InlineShapes.Item(1)

'This works, we obtain control of the OLE object.



'Dim oExcelSheet As Excel.Worksheet

'Set oExcelSheet = oInlineShape

'This DOES NOT work.

'Have tried several means to cast to an Excel object.

'Am not sure this type of cast can be done at all, and, if the object is a Worksheet.



'Once we have it as an object we might manipulate it in place to get the fields

' Or, at minimum save it to disk as an Excel document.

'oExcelSheet.SaveAs ("C:\SourceDocument.xls")



'Clean up here.



End Sub

Approach 2, Select the Excel object inside Word and save as a separate file

'We launch the code from a form which will later loop through the 1000+ documents.

Private Sub CommandButton1_Click()

 

'Start a new instance of Microsoft Word with the target document

Dim oWordDoc As Word.Document 

Set oWordDoc = GetObject("C:\SourceDocument.doc", "Word.Document")

'This works, Word opens with the document.

 

Dim intCount As Integer

intCount = oWordDoc.InlineShapes.count

' This works, we see the single OLE object embedded.

 

Dim oInlineShape As InlineShape

Set oInlineShape = oWordDoc.InlineShapes.Item(1)

'This works, we obtain control of the OLE object.



oInlineShape.OLEFormat.DoVerb (wdOLEVerbShow)

'This works, opens the OLE Excel object in Excel.  Very cool.




'But now we attempt to gain control of the Excel Worksheet which was opened

' and can not seem to find it.



'The idea here is to find the Active Excel window which was opened by the code above.

Dim xlWindow As Excel.Window

Set xlWindow = xlApp.Windows.Item(1)

'The Window count is zero and there are no items to grab.

xlWindow.Activate



Set xlWorkbook = xlApp.ActiveWorkbook

'This too is empty

xlWorkbook.SaveAs ("C:\SourceDocument.xls")

xlWorkbook.Close



'Clean up here.



End Sub

*Wipes His Forehead*

I'll get back to you on this one.

Thank you! Will test shortly and respond.

If you are still interested, I know how!

Robert


We have several thousand historical MS Word documents with embedded MS Excel Worksheets.

This data predates a relational database system which now manages all this data. Sound familiar!

We need to create a spreadsheet which has a row for each embedded MS Excel Worksheet with columns for specified values. We have no problem aggregating multiple spreadsheets into a single spreadsheet.

We have been unable to automatically extract The MS Excel worksheets from the MS Word documents.

We have tried two general approaches:
Approach 1, Obtain Control of the Excel object from VB running inside Word
Approach 2, Select the Excel object inside Word and save as a separate file

Sample code for both approaches below.

Comments indicating which lines of code are working and not working.


Approach 1, Obtain Control of the Excel object from VB running from Word

'We launch the code from a form which will later loop through the 1000+ documents.
 
Private Sub CommandButton1_Click()
 
 
 
'Start a new instance of Microsoft Word with the target document
 
Dim oWordDoc As Word.Document 
 
Set oWordDoc = GetObject("C:\SourceDocument.doc", "Word.Document")
 
'This works, Word opens with the document.
 
 
 
Dim intCount As Integer
 
intCount = oWordDoc.InlineShapes.count
 
' This works, we see the single OLE object embedded.
 
 
 
Dim oInlineShape As InlineShape
 
Set oInlineShape = oWordDoc.InlineShapes.Item(1)
 
'This works, we obtain control of the OLE object.
 
 
 
'Dim oExcelSheet As Excel.Worksheet
 
'Set oExcelSheet = oInlineShape
 
'This DOES NOT work.
 
'Have tried several means to cast to an Excel object.
 
'Am not sure this type of cast can be done at all, and, if the object is a Worksheet.
 
 
 
'Once we have it as an object we might manipulate it in place to get the fields
 
' Or, at minimum save it to disk as an Excel document.
 
'oExcelSheet.SaveAs ("C:\SourceDocument.xls")
 
 
 
'Clean up here.
 
 
 
End Sub

Approach 2, Select the Excel object inside Word and save as a separate file

'We launch the code from a form which will later loop through the 1000+ documents.
 
Private Sub CommandButton1_Click()
 
 
 
'Start a new instance of Microsoft Word with the target document
 
Dim oWordDoc As Word.Document 
 
Set oWordDoc = GetObject("C:\SourceDocument.doc", "Word.Document")
 
'This works, Word opens with the document.
 
 
 
Dim intCount As Integer
 
intCount = oWordDoc.InlineShapes.count
 
' This works, we see the single OLE object embedded.
 
 
 
Dim oInlineShape As InlineShape
 
Set oInlineShape = oWordDoc.InlineShapes.Item(1)
 
'This works, we obtain control of the OLE object.
 
 
 
oInlineShape.OLEFormat.DoVerb (wdOLEVerbShow)
 
'This works, opens the OLE Excel object in Excel.  Very cool.
 
 
 
 
'But now we attempt to gain control of the Excel Worksheet which was opened
 
' and can not seem to find it.
 
 
 
'The idea here is to find the Active Excel window which was opened by the code above.
 
Dim xlWindow As Excel.Window
 
Set xlWindow = xlApp.Windows.Item(1)
 
'The Window count is zero and there are no items to grab.
 
xlWindow.Activate
 
 
 
Set xlWorkbook = xlApp.ActiveWorkbook
 
'This too is empty
 
xlWorkbook.SaveAs ("C:\SourceDocument.xls")
 
xlWorkbook.Close
 
 
 
'Clean up here.
 
 
 
End Sub

If you are still interested, I know how!

Robert

I would like to know. I have 5000+ Word files in 13 directories that I need to extract the embedded Excel data from.

Hi,

I would be happy to help you out. This is a domain that I know very well for I have developed a Word VBA macro for a customer that automatically converts and replaces all linked and embedded Excel sheets in a Word document by Word tables.

There are four types of linked / embedded Excel sheets in a Word document. Each type requires special code to extract the Excel sheet. The difficult part is extracting information about the Excel range that is visible in the Word document.

Let me know more about what you need, and we could work something out.

Here is my email address: robert.baron@videotron.ca

Regards,

Robert Baron
Chambly, Canada

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.