Interacting with Excel from Word 1

[ChrisBurch]

From Word 97 I need to be able to open a specific Excel 97 workbook, and trigger an Excel sub/macro with a string passed from the Word vba code. Is this possible?

If not, then I guess that I could use the on open event of the workbook to trigger my code. However, I would still need to do the following:-

1)From Word open an Excel workbook.
2)Pass a string from Word to an Excel sub/macro.
3)Do what needs to be done in Excel (via my excel sub), and then pass control back to Word. This would probably be done by my excel code closing the workbook when finished.

Anyone got any thoughts?

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[chandlm]

Hi Chris,

I had to do a similar thing and eventually worked out this.

In your word code module type

sub runxlmacro ()

Set xl = CreateObject("Excel.application&quot

xl.Application.Workbooks.Open "c:\myxlfile.xls"
xl.Application.Visible = True
xl.Application.run "'myxlfile.xls'!runmacro" ' This executes the named macro in the xl sheet

' The excel macro will now execute and resume the word macro at this point when it has finished.
Set xl = 'Nothing 'sets object xl to nothing to release memory.

end sub

Remember to have the following at the end of the excel macro.

ActiveWorkbook.Save
Application.Quit 'These 2 lines will save the workbook and close the application giving the focus back to the word macro.




With regard to sending a string to the excel macro I normally write the string to a temporary text file then get the excel macro to read it in, thenget the word macro to remove the tempfile when the xl macro has finished. I am sure that there is a better way to do it but I don't know how myself. Appreciate it if anyone knows how to do this if they could post on this thread.

HTH

Matt

 

[ChrisBurch]

Thanks Matt,

That gets me going nicely.

Chris

It worked yesterday.
It doesn't work today.
That's Windows!