Variables between Excel and Word Macros?

[ectoid]

Hello,
I am writing an Excel macro that includes a Word marco which opens an existing Word document.
The word document contains 3 tables. The 3rd table is a template that will be copied x-number of times based on a value in the Excel spreadsheet...lets call that Excel variable SheetNum.

I can define, calculate and set SheetNum in Excel without a problem.
How do I then call SheetNum within the Word macro such that it understands the Excel defined variable?

Thanks.

 

[AgentSmith]

I suggest you do the whole thing in Excel. You can create a Word object in VBA from any office application. Just do a search for "Word automation" and you should turn up a few thousand suggestions!

PS - the one thing these pages often forget to tell you about is "references". In the VB editor behind Excel select "Tools" then "References". Find "Microsoft Word ... Object Library" and tick the box next to that. Then you're rockin n rollin.

 

[ectoid]

Yeah, I've got the word object created and referenced accordingly within my Excel macro...no worries there.
The problem is, I need to have the Word marco (as written within Excel) create a number of tables based on an Excel variable.
I guess, I don't know the syntax within the Word VB to appropriately call the Excel dimensioned variable.

 

[gwkenny]

If you are calling the Excel subroutine in Word, then I'd set this variable "sheet num" as a passing variable.

Another hard way to force the issue, is to have the Excel subroutine write the value into a cell, and then when you run your stuff in word, just reference the value in the cell.