Home » Developer & Programmer » Forms » Create Macro in Excel with OLE2 (Forms6i, MS Office 2003)
Create Macro in Excel with OLE2 [message #363323] Fri, 05 December 2008 17:17 Go to next message
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 #364332 is a reply to message #363323] Mon, 08 December 2008 00:47 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
These are the best links for 'excel' in Forms.

Working with Excel via OLE2
http://www.orafaq.com/forum/t/72811/67467/
http://www.orafaq.com/forum/m/84230/67467/
http://www.orafaq.com/forum/t/101906/67467/
Merge two cells in Excel using ole2
http://www.orafaq.com/forum/t/77825/67467/
Error closing Excel file generated using OLE2
http://www.orafaq.com/forum/t/77351/67467/
How to wrap text in excel cell using ole2
http://forums.oracle.com/forums/thread.jspa;jsessionid=8d92200830d62336f40f5aad44efb36ee981f4e6c19e.e34QbhuKaxmMai0MaNeMb3eKb390?mess ageID=1242535&#1242535
Excel via DDE
http://www.orafaq.com/forum/m/260061/67467/#msg_260061
Import from Excel
http://www.orafaq.com/forum/mv/msg/32353/86299/67467/#msg_86299
Forms to Excel example with webutil
http://forums.oracle.com/forums/thread.jspa?messageID=1430799
Another Forms to Excel example
http://www.orafaq.com/forum/t/81537/67467/
How to kill EXCEL.exe from Task Manager from Oracle Forms
http://www.orafaq.com/forum/t/88643/67467/
how do i display all excel worksheets name in form
http://www.orafaq.com/forum/t/89775/67467/
More Excel stuff
http://www.orafaq.com/forum/t/90502/67467/
To run an excel macro
http://www.orafaq.com/forum/t/47685/67467/
Worked example for using Excel through ODBC
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4406709207206#18830681837358
create excel chart in form6I/How to SetSourceData into excel.chart in form6i
http://www.orafaq.com/forum/t/99738/67467/
Call Excel file from Forms
http://www.orafaq.com/forum/t/121433/67467/

David
Re: Create Macro in Excel with OLE2 [message #364350 is a reply to message #364332] Mon, 08 December 2008 02:36 Go to previous messageGo to next message
Grasshoper_B
Messages: 7
Registered: September 2005
Location: Chicago, USA
Junior Member
Thank you so much, David, for sharing your collection of excel-related posts. It's very kind of you. But for my case it is of no use.
I found the way to solve this puzzle, anyway. Not quite obvious, though Wink

[Updated on: Mon, 08 December 2008 21:18]

Report message to a moderator

Re: Create Macro in Excel with OLE2 [message #364527 is a reply to message #364350] Tue, 09 December 2008 00:17 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Share, so that I can add your thread to my list.
A nice code lump would be very helpful.

David
Re: Create Macro in Excel with OLE2 [message #364739 is a reply to message #364527] Tue, 09 December 2008 20:21 Go to previous message
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.

Previous Topic: OCX control in 10g
Next Topic: My OC4J instance stopped working
Goto Forum:
  


Current Time: Sun Dec 04 19:10:55 CST 2016

Total time taken to generate the page: 0.28640 seconds