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?

Could you show some code where it is failing? Does the Word macro have a reference to Excel? — Jon49
– Jon49, Commented Nov 10, 2011 at 23:41

devuxer · Accepted Answer · 2011-11-10 23:13:18Z

4

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

edited Nov 10, 2011 at 23:13

answered Nov 10, 2011 at 23:07

devuxer

42.5k48 gold badges187 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jneasy Over a year ago

Unfortunately I have already tried this, the macro fails on a compile error for function "Cells()". I’m thinking this might be because it’s an MSword macro attempting to perform excel function.

devuxer Over a year ago

jneasy, did you try the second solution?

Collectives™ on Stack Overflow

Call Excel VBA from Word

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related