Open Word and call Word macro from within Excel VBA?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ed - 21 Feb 2006 18:01 GMT

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

Reply to this Message

Doug Robbins - Word MVP - 21 Feb 2006 18:36 GMT

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

Signature

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

> There are times when coding a macro to deal with a Word doc within Excel
> VBA
[quoted text clipped - 8 lines]
>
> Ed

Reply to this Message

Ed - 21 Feb 2006 19:02 GMT

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

> See the article "Control Word from Excel" at:
>
[quoted text clipped - 14 lines]
> >
> > Ed

Reply to this Message

Helmut Weber - 21 Feb 2006 19:15 GMT

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.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Ed - 21 Feb 2006 19:47 GMT

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

> 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.

Reply to this Message

Helmut Weber - 21 Feb 2006 20:04 GMT

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.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Ed - 21 Feb 2006 20:19 GMT

> 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

> Hi Ed,
>
[quoted text clipped - 16 lines]
> "project.module.macroname" & list of arguments
> doesn't work, as opposed to the documentation.

Reply to this Message

Helmut Weber - 21 Feb 2006 20:33 GMT

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

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Ed - 22 Feb 2006 15:02 GMT

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

> Hi Ed,
>
[quoted text clipped - 8 lines]
> oWrd.Run "ReceiveFromExcel", "Hi Word"
> End Sub

Reply to this Message

Helmut Weber - 22 Feb 2006 15:42 GMT

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

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Ed - 22 Feb 2006 16:49 GMT

> ' 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

> Hi "Ed",
>
[quoted text clipped - 36 lines]
>
> HTH

Reply to this Message

Helmut Weber - 22 Feb 2006 17:30 GMT

Hi Ed,

>I would assume, then, that
> s1, s2, s3, s4
[quoted text clipped - 3 lines]
>w1$ = "Hi" only because s1 was listed first;
>had it been s2 then w1$ = "Word"?

Yes, indeed. You got it.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

Reply to this Message

Ed - 22 Feb 2006 17:58 GMT

<<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

> Hi Ed,
>
[quoted text clipped - 7 lines]
>
> Yes, indeed. You got it.

Reply to this Message