Call Excel VBA from Word

Question

We have a standard form in MS Word to manually fill in details and launch a macro. This macro will open a MS Excel register to auto populate it from the fields in the Word document whilst doing other things.

The current solution has a keyboard shortcut for the macro in the Excel spreadsheet and the Word document macro sends the keys strokes to the opened Excel spreadsheet to launch the macro. This only works for a few people in the office.

Can I call my Excel macro directly from the Word macro while my Excel spreadsheet is in focus or is there a Windows setting that is blocking my send keys to launch the macro?

Solution

This is just a hunch, but would it be possible to migrate the Excel macro from the Excel spreadsheet to the Word document? Then you could add a bit of code to launch the correct Excel spreadsheet and perform the necessary field manipulations. So, basically, let your Word document do all the work on the Excel spreadsheet.

Alternately, you could follow the procedure discussed here:

http://support.microsoft.com/kb/177760

Here is the relevant code listing:

 Sub XLTest()

     Dim XL as Object

     Set XL = CreateObject("Excel.Application")

     XL.Workbooks.Open "C:\My Documents\ExcelFile.xls"

     ' If there is more than one macro called TestMacro,
     ' the module name would be required as in
     '
     ' XL.Run "Module1.TestMacro"
     '
     ' to differentiate which routine is being called.
     '
     XL.Run "TestMacro"

End Sub