Open Word and call Word macro from within Excel VBA?

[Ed]

There are times when coding a macro to deal with a Word doc within Excel VBA
is just a royal pain! It seems like it might be easier to code it in Word,
and just open the doc and then call Word VBA and run the macro. It would be
even better if I could also pass on the string with the doc path and name to
set the object in Word, rather than code for ActiveDocument. Is this
possible? Or am I dreaming?

Ed

 

[Doug Robbins - Word MVP]

See the article "Control Word from Excel" at:

http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm

Once you set the oWord object, you are basically programming with Word VBA


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Ed]

Thanks for the reply, Doug. I have programmed using both early and late
binding. The coding just seems much smoother inside Word VBA than with the
Word object inside Excel VBA. Today I got hung up on the Selection object,
which I had to use an application reference, vice the Range object which
refers to the document. It's just those kind of little things that flow
better in the native VBA. So I thought, "Gee - it's be nice if I could
launch a document and call a macro in Normal." Oh, well.

Ed

 

[Helmut Weber]

Hi Ed,

So I thought, "Gee - it's be nice if I could
launch a document and call a macro in Normal." Oh, well.

Sure you can.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ed]

I thought there probably was a way, Helmut, but I must be foggy today (my
employer obseved a long holiday weekend and I must have slept too many
times!), because I just can't seem to remember how.
Ed

 

[Helmut Weber]

Hi Ed,

Excel:

Sub PasstoWord()
Dim oWrd As Word.Application
' Word already running
Set oWrd = GetObject(, "Word.Application")
oWrd.Application.Run "ReceiveFromExcel", "Hi Word"
End Sub

Word:

Sub ReceiveFromExcel(FromExcel As String)
MsgBox FromExcel
End Sub

The macroname must be unique to the application.
"project.module.macroname" & list of arguments
doesn't work, as opposed to the documentation.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ed]

oWrd.Application.Run "ReceiveFromExcel", "Hi Word"
I thought that since oWrd _was_ the application object, "Application.Run"
was a redundancy error. I was trying to climb into VBE.VBProjects etc to
get to it! I like your way - it's much simpler! 8>)

Thank you, Helmut.
Ed

 

[Helmut Weber]

Hi Ed,

seems to work without "application".

Stripping code off redundancies is a very special challenge.

Sub PasstoWord()
Dim oWrd As Word.Application
' Word already running
Set oWrd = GetObject(, "Word.Application")
oWrd.Run "ReceiveFromExcel", "Hi Word"
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ed]

Helmut - If you're still here, I'm now officially confused! The macros
worked (but you knew that!) - but I can't figure out why! Specifically, how
did the Word macro know that "Hi Word" was the string FromExcel? That
string was never declared with that name. I can see the parameter
(FromExcel As String) in the Word macro - but how did the macro know that it
should contain "Hi Word"? Did it just grab the only string being passed?
If so, then I would need to exercise care when passing more than one
parameter, yes? Or am I not understanding something?

Ed

 

[Helmut Weber]

Hi "Ed",

Specifically, how did the Word macro know that
"Hi Word" was the string FromExcel?
That string was never declared with that name.

No, it was a literal constant,
but i'm not quite sure about the terminology.

The Word-sub takes the string after it's name in Excel as variable.

But explaining all aspects is rather difficult.
So: Exempla trahunt, as the Romans said.

Pass several arguments:

' in excel
Sub PasstoWordx()
Sub PasstoWordx()
Dim oWrd As Word.Application
Dim s1 As String, s2 As String
Dim s3 As String, s4 As String
Dim v As Variant
s1 = "Hi "
s2 = "Word"
s3 = "Thanks "
s4 = "Excel"
' Word already running
Set oWrd = GetObject(, "Word.Application")
' maybe this way of coding is clearer
v = oWrd.Application.Run("ReceiveFromExcel", s1, s2, s3, s4)
End Sub

'in Word
Sub ReceiveFromExcel(w1$, w2$, w3$, w4$)
MsgBox w1$ & w2$
MsgBox w3$ & w4$
End Sub

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ed]

' maybe this way of coding is clearer

v = oWrd.Application.Run("ReceiveFromExcel", s1, s2, s3, s4)

No, not really, because v is never used in the Word macro, and the macro was
called just fine without it. Does it help to have it? (Sorry to be so
dense about this!)

I would assume, then, that
s1, s2, s3, s4
corresponded with
w1$, w2$, w3$, w4$
due to the order in which they were sent and received?
w1$ = "Hi" only because s1 was listed first;
had it been s2 then w1$ = "Word"?

Ed

 

[Helmut Weber]

Hi Ed,

I would assume, then, that
s1, s2, s3, s4
corresponded with
w1$, w2$, w3$, w4$
due to the order in which they were sent and received?
w1$ = "Hi" only because s1 was listed first;
had it been s2 then w1$ = "Word"?

Yes, indeed. You got it.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Ed]

<<GGG>>>
(would attach a sound clip of cheering and applause, but it's not supported
by my text editor)
Thank you for your time and efforts yet once again to squeeze a bit more
knowledge into my head!
Ed