How to create macro in Excel from Forms with OLE2?
195109Dec 7 2008 — edited Dec 8 2008I need programmatically create macro in Excel spreadsheet with Oracle Forms6i OLE2 package.
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 handle 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 handle 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)?