Excel Macro call Word Macro with Parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Sturdevant - 24 Aug 2005 17:35 GMT

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?

Reply to this Message

Bernie Deitrick - 24 Aug 2005 19:00 GMT

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

>I have a macro in Excel that calls a macro in a Word document with this code:
>
[quoted text clipped - 19 lines]
>
> What code should I be using?

Reply to this Message

Bill Sturdevant - 24 Aug 2005 20:53 GMT

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?

> Bill,
>
[quoted text clipped - 33 lines]
> >
> > What code should I be using?

Reply to this Message

Bernie Deitrick - 25 Aug 2005 01:45 GMT

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

> 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
[quoted text clipped - 73 lines]
>> >
>> > What code should I be using?

Reply to this Message

Bill Sturdevant - 25 Aug 2005 13:06 GMT

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?

> Bill,
>
[quoted text clipped - 85 lines]
> >> >
> >> > What code should I be using?

Reply to this Message

Bernie Deitrick - 25 Aug 2005 14:35 GMT

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

> Bernie,
>
[quoted text clipped - 93 lines]
>> >> >
>> >> > What code should I be using?

Reply to this Message

Bill Sturdevant - 25 Aug 2005 16:48 GMT

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

> Bill,
>
[quoted text clipped - 122 lines]
> >> >> >
> >> >> > What code should I be using?

Reply to this Message

Bernie Deitrick - 25 Aug 2005 17:06 GMT

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

> Bernie,
>
[quoted text clipped - 155 lines]
>> >> >> >
>> >> >> > What code should I be using?

Reply to this Message

Curt - 24 May 2007 00:21 GMT

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

> Bill,
>
[quoted text clipped - 122 lines]
> >> >> >
> >> >> > What code should I be using?

Reply to this Message

Bill Sturdevant - 25 Aug 2005 14:40 GMT

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,
>
[quoted text clipped - 85 lines]
> >> >
> >> > What code should I be using?

Reply to this Message