Accessing Excel worksheets in a Word document (VB Word macro).

[MS Word macro]

Hello, would you please assist me in solving this problem:

I have an Excel object (Excel.Sheet.1) in MS Word document. I'm trying to
write a Visual Basic macro for MS Word to change the data in cell A1 of this
table. How can I access the cell?

I'll try to describe the table I have to work with:
It's not a Word table. It's rather an Excel object present in my Word
document. When it's activated a real Excel table with 3 sheets appears.. and
if it's not activated, I just see a simple table which doesn't differ from a
regular Word table. I would say it's an Excel OLE placed in the Word document
I have to deal with.
I was adviced to open Excel by using a filename but I don't have a separate
Excel document which I open by using its name... I've applied the next to
find out the name of my object:

MsgBox ActiveDocument.Fields(1).OLEFormat.ProgID

The result is: Excel.Sheet.1

What I've surely succeeded to do is activating the object. And it seems
there are various ways of obtaining that, for example:
ActiveDocument.Fields(1).DoClick
ActiveDocument.Fields(1).OLEFormat.Edit
ActiveDocument.Fields(1).OLEFormat.Activate
....
and no way known to me to edit the object's contents...

I suppose I have to apply DDE to the object in order to open Excel, edit it
and then close Excel... I don't know how to do it yet... I hope that it's an
easy task some one has already an idea of..

Thank you!

 

[Helmut Weber]

Hi,

have a look at this one:

Sub Test()
' reference to Excel library set
Dim objOLE As OLEFormat
Dim objEXL As Excel.Workbook
Set objOLE = ActiveDocument.InlineShapes(1).OLEFormat
objOLE.Activate
Set objEXL = objOLE.Object
With objEXL.ActiveSheet
.Cells(1, 1).Value = .Cells(1, 1).Value + 1
End With
SendKeys "{ESC}"
Selection.MoveRight
End Sub

Note:
Your Excel object could be a shape (!) as well.
I couldn't find a way to return to Word,
except by the error prone sendkeys method.

I discussed this in the German groups two years ago,
and nobody came up with a 100 percent proof solution.

When experimenting with this challenge,
I was confronted with all kinds of
error messages I never came across in two decades.

Besides that, I don't know, how different methods
of inserting the object would influence the code above.

Well, if it has to be, try your luck.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[MS Word macro]

Thank you!
I experienced the unwanted messages and the impossibility to close the
edited table. I have to try some ideas I have and as soon as I finish the
macro I'll post my solution here.
Thanks!

 

[Cindy M -WordMVP-]

Hi Helmut,

Your Excel object could be a shape (!) as well.
I couldn't find a way to return to Word,
except by the error prone sendkeys method.

I discussed this in the German groups two years ago,
and nobody came up with a 100 percent proof solution.

This is discussed in "the book". With Excel you can't exit
in-place editing in a reliable manner, using automation.

You have to open the Excel object in its own application
window. In that case, the .Quit method works quite nicely.

This is in contrast to MS Graph (that was actually designed
to work only as an embedded object) - it supports the Quit
command in-place.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Mini]

Hi,
I tried your code but somehow I got this error:
"User-defined type not defined".
I'm using MS word 2003.
Could you help?
thanks,
Minh

 

[Doug Robbins - Word MVP]

In the Visual Basic Editor, click on the Tools menu and then on References
and set a reference to the Microsoft Excel 11.0 Object Library.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com