Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/Run macro programatically OLE error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/Run macro programatically OLE error
Try:
Application.Run( "A1All", loExcelApp ) "Woody" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/Run macro programatically OLE error
?B?RGFtb24gTG9uZ3dvcnRo?=
wrote in : Try: Application.Run( "A1All", loExcelApp ) that's what I'm doing already: loExcelApp.Run( "A1All", loExcelApp ) loExcelApp is an Excel application object. Woody |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/Run macro programatically OLE error
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" wrote: ?B?RGFtb24gTG9uZ3dvcnRo?= wrote in : Try: Application.Run( "A1All", loExcelApp ) that's what I'm doing already: loExcelApp.Run( "A1All", loExcelApp ) loExcelApp is an Excel application object. Woody |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/Run macro programatically OLE error
?B?RGFtb24gTG9uZ3dvcnRo?=
wrote in : 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/Run macro programatically OLE error
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. "Woody" wrote: ?B?RGFtb24gTG9uZ3dvcnRo?= wrote in : 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/Run macro programatically OLE error
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("CodeAccessShee t").A1All(any params etc) Cheers Simon "Woody" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add/Run macro programatically OLE error
?B?U2ltb24gTXVycGh5?=
wrote in : 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("CodeAccessShee t").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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programatically adding macro to Excel - "ThisWorkbook" | Excel Discussion (Misc queries) | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Deleting macro code programatically | Excel Programming |