Add/Run macro programatically OLE error

[Woody]

I'm trying to add/run a macro programatically via ole. I have a test file
named formatreports.bas:

Attribute VB_Name = "FormatReports"
Sub A1All(oWorkbook As Workbook)
Dim x As Integer

For x = oWorkbook.Sheets.Count To 1
Sheets(x).Activate
Sheets(x).Range("A1").Select
Next x

End Sub


In my app, I do this:

TRY
loExcelApp.VBE.ActiveVBProject.VBComponents.Import(
goApp.cCurrentPath + "formatreports.bas" )
loExcelApp.Run( "A1All", loExcelApp )
CATCH TO loErr

ENDTRY


The import line works fine, but when I invoke the macro, I get an error
like:

"OLE IDispatch exception code 0 from Microsoft Office Excel: The macro
'A1All' cannot be found..."

I also tried "FormatReport.A1All" for the macro argument, but I get the
same error.

What am I missing here?

Thanks,
Woody

 

[Guest]

Try:

Application.Run( "A1All", loExcelApp )

 

[Woody]

=?Utf-8?B?RGFtb24gTG9uZ3dvcnRo?=

Try:

Application.Run( "A1All", loExcelApp )

that's what I'm doing already:

loExcelApp.Run( "A1All", loExcelApp )

loExcelApp is an Excel application object.

Woody

 

[Guest]

Have you tried using the Run statement or the Call statement after you get
the error and reset the routine. Try this in a new sub with only the command
calling the A1All routine. This will help identify if you are able to use
the syntax in your original routine.

 

[Woody]

=?Utf-8?B?RGFtb24gTG9uZ3dvcnRo?=

Have you tried using the Run statement or the Call statement after you
get the error and reset the routine. Try this in a new sub with only
the command calling the A1All routine. This will help identify if you
are able to use the syntax in your original routine.

I'm not following you. Can you give an example?

Thanks,
Woody

 

[Guest]

You run the import routine. You get an error. I assume you verified the
module was imported. Reset the error. Create another sub, and call the macro.
If it runs ok, then your run statement is not the problem.

You may need a short wait to allow the module to be imported.

 

[Guest]

Woody
That should probably be
"FormatReport!A1All"
(bang not dot)
You might also need a workbook ref somewhere as in
"targetWB.xls!FormatReport!A1All"

I tend to use a sheet code module so the code is neater, more controllable
and statys in VB, as in:

workbooks("TargetName").worksheets("CodeAccessSheet").A1All(any params etc)

Cheers
Simon

 

[Woody]

Woody
That should probably be
"FormatReport!A1All"
(bang not dot)
You might also need a workbook ref somewhere as in
"targetWB.xls!FormatReport!A1All"

I tend to use a sheet code module so the code is neater, more
controllable and statys in VB, as in:

workbooks("TargetName").worksheets("CodeAccessSheet").A1All(any params
etc)

Cheers
Simon

Simon,

I got past it by putting the code module in the template I create the
workbooks from. After I run, the macros I delete the code module, so the
user doesn't get those annoying "Security Warning 'abc.xls' contains
macros".

Woody