retrieve a variable from another application

[Al]

I have a word document that opens up and excel file, and runs a macro
stored in the excel file. I would like retrieve the value of a
variable in the excel macro for use in my word macro...

*** in Word ***

Sub my_word_sub
Set oExcelApp = CreateObject("Excel.Application")
oExcelApp.Workbooks.Open ("c:\myfile.xls"), UpdateLinks:=0
oExcelApp.Visible = False
oExcelApp.Run "my_excel_sub"

if var = "yes" then
msgbox "yes"
else
msgbox "no"
end if
end sub



*** in excel ***

sub my_excel_sub
dim var as string
var = "yes"
end sub


within excel, var does have a value of "yes". When my_excel_sub
finishes, and focus returns to my_word_sub, var has a value of "null".
How do I get the value set in excel to be sent over to word?

Thanks.

 

[RB Smissaert]

Quite simple:

Sub my_word_sub

Dim var As String

Set oExcelApp = CreateObject("Excel.Application")
oExcelApp.Workbooks.Open ("c:\myfile.xls"), UpdateLinks:=0
oExcelApp.Visible = False
oExcelApp.Run "my_excel_sub", var

if var = "yes" then
msgbox "yes"
else
msgbox "no"
end if
end sub


*** in excel ***

sub my_excel_sub(var As String)

var = "yes"
end sub


RBS