Accessing excel worksheet in a word document (VB macro).

[WordScript]

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 can only activate the table for example like this: ActiveDocument.Fields(1).OLEFormat.Activate - this leads to the same result as double clicking on the table object...

... I know how to access MS Word tables, but the problem here is that I have to work with an Excel table.. I suppose only an Excel macro can access it and modify it... And if I write an excel macro to do so, how can I call it from my MS word macro script? Does solving of this problem involve OLE, DDE...? Maybe I should run the Excel application to modify the table and then close it.. so far I have no success in doing that.. this has to be implemented in a single MS word macro.

Thank you!

[williamrojas78]

Hi

Try this:

Dim tabl As Table
Set tabl = ActiveDocument.Tables(1)
tabl.Cell(1, 1).formula("xxx")

I am not sure about the sytax of the formula, but that will get you to the specified cell.

Regards

[WordScript]

Thank you! But this doesn't help in my case.
Maybe I haven't explained the situation right.
Here is another 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..

Thanks again!

[fchianura]

Thank you! But this doesn't help in my case. Maybe I haven't explained the situation right. Here is another 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..

Thanks again!

Dear Sir,

I get the same problem, have some one get a solution ?

THanks

[sabarishjm]

hi