Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
qualityprocess
 
Posts: n/a
Default Extract MS Excel Data embedded in MS Word


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


--
qualityprocess
------------------------------------------------------------------------
qualityprocess's Profile: http://www.excelforum.com/member.php...o&userid=33691
View this thread: http://www.excelforum.com/showthread...hreadid=534677

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract data (not in table) from Word to Excel hellokitty77 Excel Discussion (Misc queries) 1 January 14th 06 02:51 PM
Print labels by using Excel data in a Word mail into word Zoey Excel Discussion (Misc queries) 1 November 1st 05 10:08 PM
in an excel macro can you import data from word into a cell? Trefor Excel Discussion (Misc queries) 11 October 6th 05 01:49 PM
Link excel and word in mail merge without losing your data source? angie Excel Discussion (Misc queries) 5 July 1st 05 06:29 PM
Getting specific data from a word document into an excel sheet smintey Excel Worksheet Functions 3 December 7th 04 06:17 PM


All times are GMT +1. The time now is 12:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"