Transfer Control and Data from Word to Excel then back to Word

I'm using VBA in Word 2010 to build a proposal writing tool.  I'm to the point where the user has selected (by drilling into folders) a product and model to be inserted, and now I want them to specify options that go with that model.  Because the options and their corresponding pricing change frequently, they live in an Excel workbook.  I want them to be able to specify options in Excel, then bring those back to Word.

I'm in a non-modal userform where the user has selected the model.  Then they click a "choose options" button.  My Word VBA code opens (or launches) Excel and opens the pricing book and activates the correct worksheet based on the model number previously chosen in Word.  Then I bring the Excel window to the front using a method borrowed from  

So far so good.  Now the user uses Excel to select the options he wants for the chosen model.  When they are finished (this is where I need help), I envision them clicking a button in the Excel ribbon to activate a macro which 1) scans the sheet and transfers the selected options and corresponding prices back to Word, 2) transfers control back to the Word application, and 3) (optionally) closes Excel.  

I'm a bit foggy on how to accomplish this.  Word has a handle on the Excel app, but how does Excel send variables back to Word?  It's complicated by the fact that I jumped to Excel from a userform, and I want to return to the userform and let them do other stuff.  Do I need some kind of "loop and watch for this global variable to change then resume what I was doing in Word" kind of routine?

Let me know if you need to see any code.

Bryce BassettFreelance VBA programmerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Look at it this way:-
- Word shows you a document
- Excel shows you a workbook
- VBA controls both of them as well as all variables
So, when you close the Excel application "control" automatically reverts to Word, except that it doesn't because it was never ceded to Excel. VBA still has control. All variables you fed with values from either Excel or Word still retain their values.
In fact, the Excel and Word applications just use different DLLs. A MSWord.Range is a different animal from a MSExcel.Range, but i As Integer is just the same for either application because it is owned by VBA. It doesn't matter whether its value came from Excel or from Word, and you can use it on a Word object or an Excel object as you please.
Does this help?
Bryce BassettFreelance VBA programmerAuthor Commented:

I understand the concept that control stays with Word, but I'm struggling with the mechanics. I tried this simple proof of concept:

1.  (in Word) Public myVariable as string   (does this need to be public?)
2.  Word macro launches Excel.
3.  Excel macro assigns myVariable = "Hello" then closes the Excel application
4.  Word macro: msgbox myVariable = ""

Actually, I want to assign the values chosen in Excel to custom Document Properties back in the original Word document, but I'm not having any luck there either. I tried this:

1.  (in Word)  set startingdoc = ActiveDocument  (does this need to be a public variable?)
2.  Word macro launches Excel.
3.  Excel macro:  startingdoc.CustomDocumentProperties.Add Name:="Test", LinkToContent:=False, Value:="Hello", Type:=msoPropertyTypeString

but it doesn't recognize the object startingdoc and gives me an error

I must be missing some fundamental concepts here.  

Can you help?
Deleted - will try later
Control doesn't stay with Word or Excel. It stays with VBA.
No, you don't need to declare variables any differently that you are used to.
Private Sub Test()
    Dim myVar As String

    myVar = "Something"
    ' Create an Excel Object and do things with it
    MsgBox myVar                         ' returns "Something"
End Sub

Open in new window

Now, you can make this a whole lot more complicated if the Excel application object opens a workbook and that workbook has an On_Open procedure and that procedure tries to assign a value to myVar. In this scenario you would have another instance of VB. I hope you can avoid that.

You will start making progress when you stop thinking in terms of "Excel macros" and "Word macros". Look at everything from the side of VBA. Your code (not Excel's and not Word's) startingdoc.CustomDocumentProperties.Add Name:="Test", LinkToContent:=False, Value:="Hello", Type:=msoPropertyTypeString should have the desired effect so long as it is contained in your code. However, you can't declare variables in one instance of VBA and have them available in another, no matter how you declare them.

Creating an application object in VBA is like creating any other object. You can have many of them. Your code uses these objects and manipulates them. It doesn't surrender control to them and, of course, there are no Excel macros or Word macros. Instead, there are procedures in your code that manipulate the Word application object or the Excel application object or both.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bryce BassettFreelance VBA programmerAuthor Commented:
I was indeed starting a second instance of VB by running Macros in Excel (not On_Open, but ones I had put in the ribbon), which is why variables weren't carrying over. Instead, I've now got all my procedures in the Word application.  Word creates a sort of "remote control" form to open Excel, do whatever processing needs to happen in Excel and shut it down when the time comes.  Works like a charm.  

Thanks for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.