How to use the Function $Application_Run() defined in file "Excel.f90"

[cyfeng]

Dear all,

Recently, I tried the example: "AutoDice" in the compiler directory "SAMPLESCOM" andfound thereis a complete moduleEXCEL defined in "Excel.f90".

Since the module contains the Automation interfaces of the EXCEL, may I use the Function $Application_Run($OBJECT, Macro,Arg1, Arg2,..., $STATUS) to executea macro pre-defined in an excel file ?

Thank you.

[Michael8]

Yes, you can use this function to run an Excel Macro.

Michael

[cyfeng]

Thank you, Michael. : )

As the argument 'Macro' defined in the $Application_Run($Object, Macro, Arg1, ..., $Status) is declared as TYPE(Variant), how should I passthe information of certain EXCEL Macro correctly into it?

For example, ifthere was amacro named asSHOWdefined in UH.xls Module1,is the following way assigning infomation into argument Macro correct?

Thank you again.

---

CALL VariantInit(CRO)

CRO%VT = VT_BSTR

CRO%VU%PTR_VAL = ConvertStringToBSTR('UH.xls!Module1.SHOW')

TEST = $Application_Run(workbook, Macro=CRO, $STATUS=STATUS)

[Michael8]

My post wasn't purposely trying to be facetious, I just wasn't sure exactly what your question was getting at. Your second post clarifies your question.

I'm no expert on this stuff, but I've gotten it to work, so I can tell you how I did it -- that'snot to say other ways are wrong.

Adapting from your example, I would just do the following:

TEST = $Application_Run(excelapp, 'SHOW', $STATUS=STATUS)

Notice that the first argument I pass in is excellapp, not workbook. But since I have the workbook open and active in the Excel application, I don't need to tell it where to find the macro (by using 'UH.xls!Module1').

Michael

[cyfeng]

Hi, Michael.

Thank you very much.

I tried the way you suggested.

However, the Fortran Compiler 10.1.021 threw the following message:

Error: The type of the actual argument differs from the type of the dummy argument. ['SHOW']

Is there any function convert the string to variant?

[Michael8]

Sorry about that. (I hate posting code without actually testing it first...)

What I should have said was that you should convert the string as you had done, using ConvertStringToBSTR, but just use 'SHOW' instead of 'UL.xls!Module1!Show'.

Michael

[cyfeng]

It works!

Michael, thank you very much. :D

[Michael8]

Great! I'm glad to hear it.

Thanks for letting me know.
Michael

[kooka]

i didn't read all the posts in this topic, but I think the dice example is made for excel 97, so you need to use the fortran module wizard to extract function interfaces to the version of excel you are using for a better performance, I recomend you to read the excel programing part in the msdn so you can take advanteges of all the functions you can use via com automation. http://msdn.microsoft.com/en-us/library/bb979621.aspx

[Steven_L_Intel1]

The AUTODICE sample uses the COM interfaces from EXCEL97 but it seems to work fine with later versions of Excel.

[cyfeng]

Thanks for kooka's suggestion and Lionel'sresponse : )

By the way, the version of excel I used is Excel 2003, and the function also works for Excel 2002.