Running an Excel Macro using OLE from PB

[markv105]

Hello everyone,

I'm trying to execute an Excel report macro by using the following:

ole1.application.run ("'" + ls_reportInPath + "'!" + ls_macroname, ls_name, ls_startdate)

where ls_name and ls_startdate are parameters to the macro.
ls_reportInPath is my Excel report complete with path.

I get an exception raised. Can anyone offer a suggestion?

Thanks!

 

[mbalent]

I found this post on a Powerbuilder forum. It was from back in 2001. I'm not sure it applies to you but here you go.

- Matt

>Here's what I found regarding Excel 97 and macros. Macros >are very different in Excel 97. They can belong to many >sections of an Excel Project (Workbook, Worksheet, etc. ) >and the calling convention is now directly dependent upon >the location, due to the fact that macros are now called >like regular methods. Because of this, you can no longer >just say Run() because macros are now methods of >particular instances with Excel Project collections.
>
>In the following example, I coded the macro "showmessage" >within the workbook.
>
>oleobject.Application.WorkBooks(1).showmessage()
>
>I also tried it in a Worksheet and the calling command is >exactly as you would expect.
>
>oleobject.Application.WorkBooks(1).WorkSheets(1).showmessage()
>
>Admittedly, I didn't do a whole lot of testing on this. I >just wanted to find the syntax.
>
>HTH,
>
>Carson

 

[RayPeck]

I got the same error you did when trying to use the syntax that was recorded in Excel for running a macro. My assumption is you don't need the name of the worksheet in this case, since your ole1 refers to it when you opened it.

The following syntax works when calling an Excel macro (called "CopyCell&quot from PowerBuilder:

For an OLE Control placed on a Window:

ole_excel_window_test.Object.Application.Run("CopyCell&quot

For OLE Automation, I'm guessing you just drop the word "Object".