Getting Word VBA variables into parent Excel VBA macro

[rarkin]

Hi all. I'm running an Excel macro that opens a specific excel workbook, then calls some Word macros that capture different highlighted strings which then get placed back into the Excel workbook. I'm not having trouble passing Excel variables from the parent Excel macro into my Word macros, but I do need help getting variables from the Word macros sent back to the parent Excel macro.

For example, from the Excel VBA parent macro using row as an argument passes the row content to that Word VBA macro fine:

Call wrdApp.Run("FindWebOldPrice", row)

Likewise the Word macro using this same argument gets that value from the Excel macro no problem:

Sub FindWebNewPrice(row)

This FindWebNewPrice macro locates and highlights a desired text string from a Word document, and I had wanted to assign that string to a variable "NewPrice" ---then when the parent Excel macro continued have it be able use that NewPrice variable content. But I couldn't get that to work.

I wound up using this command in the Excel parent macro to get the highlighted string value:

SelectTxt = wrdApp.Selection

While this worked, it did have some other issues that were limiting. I'd much rather have been able to have the Word macro be able to assign multiple variables different values and be able to pass them all through to the parent Excel macro.

I was playing around with this command to try to do that, thinking there must be a way since it WAS capable of getting the Selection value... but couldn't figure it out. For example something like:

SelectTxt = wrdApp.NewPrice.Value

This of course did NOT work, but isn't there some syntax that would? I'm probably missing something simple---I still obviously have much to learn! Powerful stuff though...and addicting.

Thanks for any suggestions for the simplest way to pass multiple Word Macro variables back to the parent Excel macro that ran it. Cheers,

--Ray

 

[SkipVought]

Hi,

Welcome to Tek-Tips!

You have not given us enough specific info. You question states nothing about the contents of the variables or the code that is running.

I'd be more apt to have ALL my macros in Excel and reference the Microsoft Word n.m Object Library.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You can use MSForms DataForm to transfer text. The schema:

Word code:
[tt]Sub FindWebNewPrice(spv As MSForms.DataObject)
...
spv.SetText NewPrice
End Sub[/tt]

Excel code:
[tt]Dim spv As MSForms.DataObject
Set spv = New MSForms.DataObject
...
Call wrdApp.Run("FindWebNewPrice", spv)
MsgBox spv.GetText[/tt]

Both excel and word require reference to MSForms.


combo