944,093 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > Accessing excel worksheet in a word document (VB macro).
Ad:
Permalink
Mar 27th, 2006
0

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

Expand Post »
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!
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
WordScript is offline Offline
2 posts
since Mar 2006
Permalink
Mar 27th, 2006
0

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

Hi

Try this:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. Dim tabl As Table
  2. Set tabl = ActiveDocument.Tables(1)
  3. 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
Reputation Points: 23
Solved Threads: 10
Junior Poster
williamrojas78 is offline Offline
111 posts
since Jun 2005
Permalink
Mar 28th, 2006
0

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

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!
Reputation Points: 10
Solved Threads: 0
Newbie Poster
WordScript is offline Offline
2 posts
since Mar 2006
Permalink
Aug 6th, 2008
0

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

Click to Expand / Collapse  Quote 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!

Dear Sir,

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

THanks
Reputation Points: 10
Solved Threads: 0
Newbie Poster
fchianura is offline Offline
1 posts
since Aug 2008
Permalink
Nov 5th, 2009
0
Re: Accessing excel worksheet in a word document (VB macro).
hi
Reputation Points: 10
Solved Threads: 0
Newbie Poster
sabarishjm is offline Offline
1 posts
since Nov 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Slider Control
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: vb6 populating with first record information





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC