Getting Data From Excel Embedded in Word Doc

[johnnymagee]

I want to be able to either:
(1) use an excel sheet embedded in a word doc like any other Excel book, or
(2) copy and paste the Excel object into Excel

I've no idea about doing (1) and am stuck on (2)

Set owd = New Word.Application

Set owdSource = owd.Documents.Open("blahblahblah.doc")

'ADD NEW SPREADSHEET
Set owbkTemp = oXL.Workbooks.Add
Set rPaste = owbkTemp.Sheets("Sheet1").Range("A1")
owdSource.Shapes(1).Select
Selection.Copy

rPaste.PasteSpecial xlPasteValues

 

[DPlank]

So you want to Open a word document, embed into it an Excel workbook, and then edit the workbook in Excel?

Why not just open the workbook in Excel in the first place?

...if I understood you right, this is the easiest option.

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[johnnymagee]

Sorry,no. Every day I get sent a word document with an Excel sheet embedded in it. I need to be able to open the document, and get the data out of the embedded Excel sheet, to store elsewhere

 

[PHV]

Have you tried to do it manually while the macrorecorder is running ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[johnnymagee]

Yeah, I tried that but it didnt record anything beyond

Shapes(1).Select

which is where I got stuck

 

[PHV]

you may investigate in this direction:
ActiveDocument.Shapes(1).OLEFormat.Open
The DoVerb method may also be of interest.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[combo]

Assuming you work with first worksheet of first embedded object (workbook):

ThisDocument.InlineShapes(1).OLEFormat.Object.Worksheets(1).Range("A1") = "Hello"

or, after referencing excel library:

Dim xlWbk As Excel.Workbook
Set xlWbk = ThisDocument.InlineShapes(1).OLEFormat.Object
xlWbk.Worksheets(1).Range("A1") = "Host doc info: " & Format(Now, "hh:mm:ss")

combo

 

[johnnymagee]

Cheers, guys. Lifesaver. It didnt like InlineShapes (didnt find any items in the collection) but I got this to work:

Dim owdSource As Word.Document
Dim owd As Word.Application
Dim oCopy As Word.Shape
Dim rCopy As Range

Set owd = New Word.Application
Set owdSource = owd.Documents.Open(sWordDoc)
Set oCopy = owdSource.Shapes(1)

oCopy.OLEFormat.Open

Set rCopy = oCopy.OLEFormat.Object.Worksheets("Sheet2").Range("A1:I16")