select excel object in word

[Sander]

Hi,

I have a Word document with a number of Excel sheets embedded.
The original sheet is an autotext.
With VB I added the sheet to the document a number of times.
Now I need to fill the sheets with data.
How can I access the sheets?
I allready have a worksheet object ready to use. I need to know the code for
selecting the object from the word document
Each sheet has it's own bookmark..

Thanks,

Sander

 

[Cindy Meister]

Hi Sander

Basically, it goes like this:

Sub ActivateExcelCell()
Dim ils As Word.InlineShape
Dim doc As Word.Document
Dim of As Word.OLEFormat
Dim xlWB As Excel.Workbook

Set doc = ActiveDocument
Set of = doc.Bookmarks("index").Range.InlineShapes(1).OLEFormat
of.DoVerb wdOLEVerbPrimary
Set xlWB= of.Object
'Put your Excel code here
Set xlWB = Nothing
Exit For
End If
Next ils
End Sub

If the Excel sheets are formatted with text flow in the Word document, then
you need to use the SHAPES, rather than InlineShapes collection. You need to
set a reference to the Excel object library in order to use early binding (as
shown above).

If you need to cycle through multiple sheets, then you need to use a
"DoVerb" that opens each workbook into its own Excel window. At the end of
each loop: xlWB.Application.Quit to close the window so that you can process
the next embedded object.

-- Cindy

 

[Sander]

Thanks Cindy,

This was exactly what I needed.. )