| Create Macro in Excel with OLE2 [message #363323] |
Fri, 05 December 2008 17:17  |
Grasshoper_B
Messages: 7
Registered: September 2005
Location: Chicago, USA
|
Junior Member |
|
|
I need programmatically create macro in Excel spreadsheet with Oracle Forms6i OLE2 package. The goal is to modify 'SaveAs' functionality. Users want default unique file names.
In VB for Applications this seems to be simple:
ActiveWorkbook.VBProject.VBComponents.Item(1).CodeModule.AddFromString ("Private Sub Workbook_BeforeSave..." etc.)
But working with OLE2 I cannot get access to "ThisWorkbook" object:
I can easily get handler for VBComponents collection:
excel_app := ole2.create_obj ('Excel.Application');
workbooks:= ole2.get_obj_property(excel_app,'Workbooks');
workbook := ole2.invoke_obj(workbooks,'Add');
vbproject:=ole2.get_obj_property(workbook,'VBProject');
vbcomponents:=ole2.get_obj_property(vbproject,'VBComponents');
But next I need a handler to first member of this collection, it can be addressed like VBComponents(1) or VBComponents.Item(1) with VB, but when I run
args := ole2.create_arglist;
ole2.add_arg(args,1);
item:=ole2.get_obj_property(vbcomponents,'Item',args);
I always get error hex80020003 which is 'member not found'
This approach always works with other Excel collections, but not with VBComponents. What I am doing wrong?
If I manage to get handler to VBComponents(1), then I am going to get CodeModule and then invoke
ole2.invoke(codemodule,'AddFromString',args);
But how to get that VBComponents(1)?
|
|
|
|
|
|
|
|
|
|
| Re: Create Macro in Excel with OLE2 [message #364739 is a reply to message #364527] |
Tue, 09 December 2008 20:21  |
Grasshoper_B
Messages: 7
Registered: September 2005
Location: Chicago, USA
|
Junior Member |
|
|
Well, tricky part is that .item collection must be addressed not with
ole2.get.obj_property,
but with 'ole2.invoke_obj'.
Looks strange enough.
Also, this works if you want to add one of those 'auto-exec' macros that substitutes standard Excel functionality, like, f.e., "Save" or "Save as" in my case.
If you need to add just regular macro, use
item:=ole2.invoke_obj(VBCompoments, 'Add', args)
where args=1 which corresponds to vbext_ct_StdModule constant.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
