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 cpearson.com.
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.
Thanks!
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 cpearson.com.
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.
Thanks!
ASKER
Thanks.
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.CustomDocument
but it doesn't recognize the object startingdoc and gives me an error
I must be missing some fundamental concepts here.
Can you help?
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.CustomDocument
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks for your help.
- 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?