Accessing excel worksheet in 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 writing 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...

[confused]

cells(x,y) = x

or x = cells(x,y)

but of course you can select a range or specify which worksheet if need be.

[confused]

A1 is the same as 1,1 so you say you wanted to ge tthe current value and add 200 to it:

tmp = cells(1,1)
tmp = tmp + 200
cells(1,1)=tmp

which is of course the same as cells(1,1)=cells(1,1)+200

etc....

[WordScript]

Thanks, but this doesn't help me at all. 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.

[confused]

Right I see, sorry I skipped through your post a bit quick!

Not something I have ever done, but this might or might not give you a ponter or two in the right direction.

http://www.exceltip.com/st/Control_Excel_from_Word_using_VBA_in_Microsoft_Exc el/463.html

by the way you access excel cells as I mentioned above. Are you trying to use prober vba or a crappy macro?

[confused]

ok, try this, looks more like it from the way I understand your question :)

Try this:

'Requires a referece to the Microsoft Excel Object Library
'Tools | References

Option Explicit

Sub ExcelMacro()

Dim ObjExcel As Excel.Application
Dim Wkb As Excel.Workbook
Dim WS As Excel.Worksheet
Dim Path As String
Dim FName As String

Path = ThisDocument.Path
FName = "Book1.xls"
Set ObjExcel = New Excel.Application
'ObjExcel.Visible = True 'Add this line to make the Excel app visible
Set Wkb = ObjExcel.Workbooks.Open(FileName:=Path & "\" & FName)
Set WS = Wkb.Sheets("Sheet1")
WS.Range("A1").Value = "This"
WS.Range("A2").Value = "Is"
WS.Range("A3").Value = "A"
WS.Range("A4").Value = "Message"
WS.Range("A5").Value = "From"
WS.Range("A6").Value = "Word"
Wkb.Close True
ObjExcel.Quit

Set ObjExcel = Nothing
Set Wkb = Nothing
Set WS = Nothing

End Sub

[WordScript]

Thanks!

The comment "Requires a referece to the Microsoft Excel Object Library" was quite useful! I hadn't checked this one and had had problems many times before due to it..

I feel I'm almost on the verge of solving the problem...
I think that should work:
1) start Excel
2) modify A1
3) close Excel

I guess I should use Dynamic data exchange (DDE) in order to open Excel for modifying my object..

...
ActiveDocument.Fields(1).OLEFormat.Application.DDE .... <--?????
...

... There must be an easier method though...
I haven't done it yet so I would appreciate any further help. Thanks in advance!

[confused]

I think that should work:
1) start Excel
2) modify A1
3) close Excel
isnt that basically what the code above does?

[WordScript]

It opens a blank Excel document. My real problem is how to tell it to open and modify the Excel object which is present in my Word document and which name is: Excel.Sheet.1 (I have found that out by applying this: MsgBox ActiveDocument.Fields(13).OLEFormat.ProgID).

[WordScript]

Maybe I haven't explained the situation right.
I don't know really how to qualify the table I have to work with. Here is a try to explain: 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 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!