Excel Macro call Word Macro with Parameters

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?

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" <[email protected]> wrote in message
news:[email protected]...
>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?
>

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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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?
> >
>
>
>

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" <[email protected]> wrote in
message news:[email protected]...
> 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" wrote:
>
>> 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" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >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,

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" wrote:

> 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" <[email protected]> wrote in
> message news:[email protected]...
> > 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" wrote:
> >
> >> 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" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> >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?
> >> >
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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" <[email protected]> wrote in
>> message news:[email protected]...
>> > 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" wrote:
>> >
>> >> 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" <[email protected]> wrote in
>> >> message
>> >> news:[email protected]...
>> >> >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,

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.

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in
> message news:[email protected]...
> > 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" wrote:
> >
> >> 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" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> >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,

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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> >> 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" <[email protected]> wrote in
> >> message news:[email protected]...
> >> > 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" wrote:
> >> >
> >> >> 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" <[email protected]> wrote in
> >> >> message
> >> >> news:[email protected]...
> >> >> >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?
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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


"Bill Sturdevant" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" wrote:
>> >
>> >> 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" <[email protected]> wrote in
>> >> message news:[email protected]...
>> >> > 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" wrote:
>> >> >
>> >> >> 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" <[email protected]> wrote in
>> >> >> message
>> >> >> news:[email protected]...
>> >> >> >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?
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>