Q
qualityprocess
We have several thousand historical MS Word documents with embedded M
Excel Worksheets.
This data predates a relational database system which now manages al
this data. Sound familiar!
We need to create a spreadsheet which has a row for each embedded M
Excel Worksheet with columns for specified values. We have no proble
aggregating multiple spreadsheets into a single spreadsheet.
We have been unable to automatically extract The MS Excel worksheet
from the MS Word documents.
We have tried two general approaches:
Approach 1, Obtain Control of the Excel object from VB running insid
Word
Approach 2, Select the Excel object inside Word and save as a separat
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 fro
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 objec
is a Worksheet.
'Once we have it as an object we might manipulate it in place to ge
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 separat
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 wa
opened
' and can not seem to find it.
'The idea here is to find the Active Excel window which was opened b
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 Su
Excel Worksheets.
This data predates a relational database system which now manages al
this data. Sound familiar!
We need to create a spreadsheet which has a row for each embedded M
Excel Worksheet with columns for specified values. We have no proble
aggregating multiple spreadsheets into a single spreadsheet.
We have been unable to automatically extract The MS Excel worksheet
from the MS Word documents.
We have tried two general approaches:
Approach 1, Obtain Control of the Excel object from VB running insid
Word
Approach 2, Select the Excel object inside Word and save as a separat
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 fro
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 objec
is a Worksheet.
'Once we have it as an object we might manipulate it in place to ge
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 separat
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 wa
opened
' and can not seem to find it.
'The idea here is to find the Active Excel window which was opened b
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 Su