Referencing Excel Macro from Progress - Query

D

daves_mot

New Member
Hi all,
I have been lurking around here for a little while checking out previous posts regarding opening an excel session from progress and running a macro, but I have one small(ish) query, if anyone can help me with it (the code below is cobbled together from previous threads so I am not entirely sure what/how I am doing it).

Running : Progress 9.1c, MS Excel 2000 (client will be moving to MS Excel XP soon after)

(Rough) Code:
/*application running from progress to import a progress generated report into excel, run macro to format report*/
DEF VAR chExcelApplication AS COM-HANDLE NO-UNDO.
DEF VAR chWorkbook AS COM-HANDLE NO-UNDO.
DEF VAR w-file-name AS CHARACTER NO-UNDO.
ASSIGN w-file-name = "C:\temp\test.xls".

PROCESS EVENTS.
CREATE "Excel.Application":U chExcelApplication NO-ERROR.
PROCESS EVENTS.

/*open an excel session*/
chExcelApplication:VISIBLE = TRUE.

/*populate with test.txt*/
chWorkbook = chExcelApplication:workbooks:OPEN(w-file-name).

/*opens macro.xls & applys macro1 to test.txt*/
chExcelApplication:ExecuteExcel4Macro("RUN(""'C:\temp\macro.xls'!Macro1"")").


I have been playing around with this and it works fine, but my issue is that it opens two sessions of excel (test.xls and macro.xls) when I only want one session open for the user (report in test.xls), and to refer to macro "C:\temp\macro.xls'!Macro1" - not open it. If the user has the open macro.xls session there is a high possiblity that they will delete it or interfere with it (sigh). Should I rethink the approach and attempt to put the VB Macro code into the progress procedure instead? Is it that a macro is inherently linked to a workbook that must be open in order for it to be referenced? Or should I just forget it and go and have a drink? (my favorite option but not really practical.)

Any suggestions greatly appreciated, and anyone heading to Madrid on Sunday - have a great time, wish I was there!

thanks,
Fiona.
 
i suspect you are getting two versions because the maco is in a differetn document than the data .
in my experience its better to run the stuff in progress rather than as a macro in youre office app. it resonably easy to transfer the logic across change the dots for : and try it.

good luck!
 
Thanks for the reply Phil, I had a suspicion that was the way I would have to go. I will give it a shot from Progress instead (which might mean a whole load of new questions!).

Thanks again,
Fiona.
 
You must log in or register to reply here.
Back
Top