Excel Macro call Word Macro with Parameters

[Bill Sturdevant]

I have a macro in Excel that calls a macro in a Word document with this code:

wordApp.Visible = True
wordApp.Activate
wordApp.Run ("myMacro")

But when I changed the Word macro to require parameters, using the following
code, I get Run-time error '-2147352573 (80020003)' Unable to run the
specified macro:

wordApp.Visible = True
wordApp.Activate
wordApp.Run ("myMacro", 'String with spaces', numVal1, numVal2)

So I tried using the following code, but I get Object doesn't support this
property or method:

wordApp.Visible = True
wordApp.Activate
result = wordApp.Run ("myMacro", 'String with spaces', numVal1,
numVal2)

What code should I be using?

 

[Bernie Deitrick]

Bill,

Strings must be wrapped in double quotes, not single:

wordApp.Run ("myMacro", "String with spaces", numVal1, numVal2)

and your macro in the word document must be properly declared, along the lines of:

Sub myMacro(myStr As String, myVal1 As Double, myVal2 As Double)

HTH,
Bernie
MS Excel MVP

 

[Bill Sturdevant]

Thanks, Bernie, I am successfully calling the Word macro and passing it
parameters now, but... let me explain a bit more about what I am trying to
do and ask some further questions.

I have a macro in an Excel Workbook. It is looking at a range of rows and
where conditions are right, it needs to call a macro in Word passing it some
data from the chosen row.

The word macro creates a paragraph using the first parameter passed, then
using the rest of the parameters, builds 3 Excel Chart Objects, sizing them
so they fit on one line, then puts two blank lines after the 3rd Chart.

Then, control must pass back to the Excel macro which loops until it finds
another acceptable row. This loop continues until the end of the range of
rows is met.

At the end of the first pass through the Word macro, control gets returned
back to Excel. If I stop the macros and look at the Word document, the
charts are there, but the values are not what was passed in. If I don't stop
the macros, the second acceptable row is found in Excel, and the charts are
prepared in Word, but not all commands of the formatting logic is processed,
and upon returning to Excel to go after the 3rd acceptable row, I get a
message "Method 'Range' of object '_global' failed".

Is it possible the Excel macro is not waiting for the Word macro to finish?
What things should I look out for in this scenario?

 

[Bernie Deitrick]

Bill,

Why use the Word macro at all? You can do anything to Word from Excel that
you can do within Word, so that way, you would never pass control to Word,
and timing would never be an issue. Usually, you can just put the Word
Object as the object qualifier in the code, and it will run as well from
Excel as from Word.

Just a thought,
Bernie
MS Excel MVP

 

[Bill Sturdevant]

Bernie,

I love the idea! I understand you to mean that I take the code in the Word
macro and place it within the loop of the Excel macro, but surround it with a
"with" structure.

But, I am unsure of how to do that. Can you give me an example?

 

[Bernie Deitrick]

Bill,

You use Word's activewindow, kind of like this. Use the macro recorder in Word to get the syntax
correct, then just copy it over to Excel and use the With structure:

Dim oWord As Word.Application
Dim myDoc As Word.Document
Set oWord = CreateObject("word.application")

oWord.Application.Visible = True
Set myDoc = oWord.Application.Documents.Open("C:\My Documents\Test.doc")
With myDoc.ActiveWindow
.Selection.TypeText Text:="This is from Excel's cell A1: " & Range("A1").Value & "."
.Selection.TypeParagraph
.Selection.TypeParagraph
.Selection.TypeText Text:="This is a separate paragraph."
.Selection.TypeParagraph
.Selection.TypeParagraph
.Selection.TypeText Text:="This is bold."
.Selection.HomeKey Unit:=wdLine, Extend:=wdExtend
.Selection.Font.Bold = wdToggle
End With
oWord.Application.Quit
Set oWord = Nothing

HTH,
Bernie
MS Excel MVP

 

[Bill Sturdevant]

Bernie,

This is some of the code I am using in Word. How would I modify it to work
from Excel?

Dim oChart As Object
Dim oInlineShape As InlineShape
Dim myBMPpath As String

Selection.TypeText Text:=appName
Selection.TypeParagraph

Set oInlineShape =
Selection.InlineShapes.AddOLEObject(ClassType:="Excel.Chart.8", FileName:= _
"", LinkToFile:=False, DisplayAsIcon:=False)

oInlineShape.ScaleWidth = 50
etc., etc.

 

[Bill Sturdevant]

Bernie,

Thanks! I had just figured out how to do that, so I am grateful for your
example as it confirmed my approach.

But I am now getting another error. Remember that I am looping through a
range in Excel, adding Excel charts to a Word document.

Here is the basic code I am using. The first iteration works fine, but as
soon as I return to the top of the loop, I am getting the error "Method
'Range' of object '_global' failed.". Why is my range getting screwed up?

Set wordApp = CreateObject("Word.Application")
wordApp.documents.Add
Set wordDoc = wordApp.documents(1)
Do Until i > Range("MyRange").End(xlDown).Row - Range("MyRange").Row + 1
If Range("MyRange").Cells(i, 16).Value = strMySearchArg Then
Var1 = Range("MyRange").Cells(i, 17)
Var2 = Range("MyRange ").Cells(i, 28)
Var3 = Range("MyRange ").Cells(i, 29)
wordDoc.Content.Select
With wordDoc.Application.Selection
Set oInlineShape =
..InlineShapes.AddOLEObject(ClassType:="Excel.Chart.8", FileName:= _
"", LinkToFile:=False, DisplayAsIcon:=False)
‘Do commands to format the chart and populates its associated datasheet
with values
Set oInlineShape = nothing
End With
End If
i = i + 1
Loop

 

[Bernie Deitrick]

Bill,

When switching back and forth, we need to be more specific about your objects, so change all your
range objects to be fully qualified:

Range("MyRange").....

should be

ThisWorkbook.Worksheets("SheetName").Range("MyRange").....

HTH,
Bernie
MS Excel MVP

 

[Curt]

trying to do this and no luck have the macros built in excel & word. Also
have a doc to do the mail merge on .excel builds a wks sheet and sets print
area.called PrintE
excel macro is maile word macro is first word doc is entry doc. Am confused
as to where to inject these items. Seem all my places cause machine to stall.
Thanks to anyone who can advise