I'm trying to figure out why my Word 2010 VBA will not run an Excel macro from within a Word macro.
I can successfully do the reverse and run a Word macro from an Excel macro, but get an error when trying to
do it the other way around.
The working macros and their non-working versions are below. I'm sure I'm missing something simple, but it eludes me so far. Thanks for any help!
-----------------
These two macros WORKED to pass variables from the Excel macro into theWord macro:
Excel macro to run the TestGetVarFromXL macro:
'***********************************************
Sub RunTestGetVarFromXL()
'This worked.
'~~> Establish an Word application object
On Error Resume Next
Set wrdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wrdApp = CreateObject("Word.Application")
End If
Err.Clear
On Error GoTo 0
'Test assigning variables:
CourseStartRow = "44"
CourseEndRow = "54"
CourseTitle = "MS Word 2016"
'Using Call command to run this word macro and pass these variables into it:
Call wrdApp.Run("TestGetVarFromXL", CourseStartRow, CourseEndRow, CourseTitle) '
End Sub
'********************************************************
----------------------------------------------------------------------------
Word macro to receive variables from Excel macro that calls it:
'*****************************************************
Sub TestGetVarFromXL(CourseStartRow, CourseEndRow, CourseTitle)
'This Worked to receive these variable data from Excel macro: RunTestGetVarFromXL
MsgBox ("From Word TestGetVarFromXL macro:" & Chr(13) & Chr(13) & _
"CourseStartRow = " & CourseStartRow & Chr(13) & Chr(13) & _
"CourseEndRow = " & CourseEndRow & Chr(13) & Chr(13) & _
"CourseTitle = " & CourseTitle)
End Sub
'***************************************************
===================================================================
=======================================================================
*** These two macros below did NOT Work**********************
They are similar to the above macros but changed to do the reverse of calling an Excel macro from Word.
Get the error message:
"Run-time error '1004': Cannot run the macro 'RunTestGetVarFromWord". The macro may not be available in this workbook or all macros may be disabled":
Word macro to run the TestGetVarFromWord macro:
'***************************
Sub RunTestGetVarFromWord()
'This did NOT work, currently getting error message:
'"Run-time error '1004': Cannot run the macro 'TestGetVarFromWord". The macro may
'not be available in this workbook or all macros may be disabled"
'~~> Establish an Excel application object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlApp = CreateObject("Excel.Application")
End If
Err.Clear
On Error GoTo 0
'Test assigning variables to pass:
CourseTitle = "MS Word 2016"
CourseStartRow = "44"
CourseEndRow = "54"
'Run the word macro and pass these variables into it:
Call xlApp.Run("TestGetVarFromWord", CourseStartRow, CourseEndRow, CourseTitle) '
End Sub
'*********************************
Excel macro to receive variables from Word macro that calls it:
'**************************************************
Sub TestGetVarFromWord(CourseStartRow, CourseEndRow, CourseTitle)
'This did NOT work (was not called successfully from the Word macro: RunTestGetVarFromWord.
MsgBox ("From Excel TestGetVarFromWord macro:" & Chr(13) & Chr(13) & _
"CourseStartRow = " & CourseStartRow & Chr(13) & Chr(13) & _
"CourseEndRow = " & CourseEndRow & Chr(13) & Chr(13) & _
"CourseTitle = " & CourseTitle)
End Sub
'*****************************
Bookmarks