PDA

View Full Version : Get Word VBA macro variables passed back to parent Excel macro that called it



Rarkin55
04-23-2014, 06:50 PM
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

snb
04-24-2014, 12:35 AM
You dont' need to call Word macros. You can run excel's VBA in the worddocument too:

In Excel

e.g. to filter all paragraphs in the Word document that contain the string 'product 1' and put those paragraphs in column E of the Excel sheet


Sub M_snb()
with getobject("G:\OF\specifications.docx")
sn=filter(split(.content,vbcr),"product 1")
.close false
end with

for j=0 to ubound(sn)
cells(j+1,5)=sn(j)
next
End sub

Kenneth Hobs
04-25-2014, 07:38 AM
You could poke variable names and variable values into the registry and then get them as needed from any other run of code in any application. Check out SaveSetting() and GetSetting().