Skip to Main Content

Oracle Forms

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to create macro in Excel from Forms with OLE2?

195109Dec 7 2008 — edited Dec 8 2008
I 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)?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 5 2009
Added on Dec 7 2008
1 comment
992 views