Excel VBA to use worksheet embedded in Word Doc

[Dan Meller]

I want to be able to process a worksheet which is embedded in a Word document
using VBA code in an Excel Workbook. I have the following code which starts
Word, opens the document, and finds the field which contains the Excel
worksheet, but what property of the field can I use as a worksheet object.

Dim appWord As Word.Application
Dim wDoc As Document
Dim wFld As Field
Dim wWksht As Worksheet
Set appWord = CreateObject("Word.Application")
appWord.Visible = True
appWord.Documents.Open _
"docdataset.doc", _
ReadOnly:=True, AddToRecentFiles:=False
Set wDoc = appWord.Documents(appWord.Documents.Count)
For Each wFld In wDoc.Fields
If wFld.Type = wdFieldEmbed Then
If Left$(wFld.Code.Text, 11) = "EMBED Excel" Then
Exit For
End If
End If
Next wFld

Set wWksht = wFld.???????????

 

[keepITcool]

Dan,

this can't be done the way you want.

Editing the embedded worksheet with VBA from word is not so difficult
When you edit an embedded object MSWord will start a new instance of
excel.. and finding it is easy (IF excel wasn't running beforehand!!!)
see code below

But you propose to use Excel to call Word to call a new Excel:
Thus
Excel1
Word
Excel2

It is impossible to tell which instance of excel will be
be returned by the GetObject function. (see MSDN)




'RUN THIS FROM WORD VBA
Sub EditEmbeddedWkb()
Dim wdOle As OLEFormat
Dim xlApp As Object
Dim xlWkb As Object

'Get he inline worksheet
Set wdOle = ActiveDocument.InlineShapes(1).OLEFormat
'Start editing (this will start an excel instance)
wdOle.DoVerb 'wdOLEVerbOpen will edit in a fully visible window

'Now get the excel application.object
'will work only if excel isn't running yet.
Set xlApp = GetObject(, "Excel.Application")
Set xlWkb = xlApp.workbooks(1)
'Do your stuff
xlWkb.worksheets(1).Cells(3, 2) = "Edited from MSword vba at " & Now()

'Close the references to excel objects
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub







--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Dan Meller wrote :