DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

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

•

Join Date: Mar 2006

Posts: 2

Reputation: WordScript is an unknown quantity at this point

Solved Threads: 0

WordScript WordScript is offline

Offline

Newbie Poster

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

Mar 27th, 2006

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!

•

Join Date: Jun 2005

Posts: 85

Reputation: williamrojas78 is an unknown quantity at this point

Solved Threads: 4

williamrojas78 williamrojas78 is offline

Offline

Junior Poster in Training

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

Mar 27th, 2006

Hi

Try this:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Dim tabl As Table
Set tabl = ActiveDocument.Tables(1)
tabl.Cell(1, 1).formula("xxx")
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

•

Join Date: Mar 2006

Posts: 2

Reputation: WordScript is an unknown quantity at this point

Solved Threads: 0

WordScript WordScript is offline

Offline

Newbie Poster

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

Mar 28th, 2006

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!

•

Join Date: Aug 2008

Posts: 1

Reputation: fchianura is an unknown quantity at this point

Solved Threads: 0

fchianura

Offline

Newbie Poster

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

Aug 6th, 2008

•

Originally Posted by 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!