help with vb script

[SuitedAces]

I'm using this script from the forum to run a macro in excel.

//Put this VBScript block at top of script
VBSTART
Sub OpenAndRun(xlFileName, MacroName)
Set xlApp = CreateObject("Excel.Application")
'remove next line to make Excel invisible
xlApp.visible = true
Set xlBook = xlApp.WorkBooks.Open(xlFileName)
xlApp.Run(MacroName)
'Uncomment next line to quit Excel
'xlApp.quit
End Sub
VBEND

//Use this line when you want to run the Excel macro:
VBRun>OpenAndRun,c:\files\myexcel.xls,MyMacro

How can I get a return variable back ?
Is the any way to run the macro as I am in the code above from within a function.
The function then takes a value that is placed on the worksheet by the excel macro, which then allows me to use VBEval ?

Is that possible ?

Or do I need to do a seperate DDE request ?
If so is there anyway to do that with excel hidden like in the above code ?

[Marcus Tettmar]

You mean get a variable back from Excel?

The function call can't do that. Probably best way would be to have Excel put the value in a cell and then use a simple DDE call or VBscript to read the cell.

That code is just an example that creates an instance of Exce, opens an xls file and then runs the macro. You could rearrange that code however you like, and have the bit that runs the macro wherever you want.

[SuitedAces]

Yes but can I do the DDE with excel hidden ?

Do I need to complete this macro first and do a DDE after I close ?
Or can I use the code above edit out the close app command, do a DDE and then run the same code for the sole purpose of closing my excel file ?

[Marcus Tettmar]

I would use VBScript to read the cell before the close. See link I posted earlier on how to read Excel data. That article has VBscript for reading data from Excel. There's also a sample script that comes with Macro Scheduler with same code. Insert code that reads the data between the run and the close.