[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9700
  • Last Modified:

Using Excel OLE Control in Forms 6i

I want to use excel ole control in a form developed in Forms 6i.
Following is the code. It gives me exception ORA-305500.


WHEN-NEW-FORM-INSTANCE

declare
     item_id ITEM;    
begin
   item_id := Find_Item('OLE_CONTROL5');
Forms_OLE.Initialize_Container(item_id, 'C:\Book1.xls');
SYNCHRONIZE;
if  forms_ole.server_active (item_id) then
            message('server Active');
end if;
if not forms_ole.server_active (item_id) then
       forms_ole.activate_server(item_id);
       message ('server activated');
end if;

end;


WHEN-BUTTON-PRESSED

declare
     item_id ITEM;
     d ole2.obj_type;
     c ole2.obj_type;
     lst ole2.list_type;
begin
     item_id := find_item('OLE_CONTROL5');
     d := forms_ole.get_interface_pointer(item_id);
     --d := ole2.CREATE_OBJ('Excel.Application');
     --c := ole2.invoke_obj(d,'Edit');
     --synchronize;
     --c := ole2.invoke_obj(d,'Open');
     lst := ole2.create_arglist;
  ole2.add_arg(lst,1);
  ole2.add_arg(lst,1);
--Exception occurs on the following line.
     c:=ole2.get_obj_property(d,'Cells',lst);
     --ole2.destroy_arglist(lst);
     --ole2.set_property(c,'Value',10);
     --ole2.release_obj(c);
end;


0
motorway
Asked:
motorway
1 Solution
 
jtriftsMI and AutomationCommented:
What version of Oracle are you running?
(I use Ora8i/Forms6i and I've never seen "Ora-..." messages larger than 31000.)

Is the form falling over in the when-new-form-instance trigger or the when-button-pressed?

It would appear you've built this based on the Forms6i help files...the WNFI looks fine...as does the WBP?!?!

I assume you're using this on MS or MAC (as OLE is not supported on Unix)

Is there any text with the error message?
Can you trap there error text and tell us what it says?

Regards,
JT
0
 
motorwayAuthor Commented:
I'm using Oracle 8i/Forms 6i.
Exception occurs on the following line.
    c:=ole2.get_obj_property(d,'Cells',lst);
I've tried it on Win98 and Windows 2000.
0
 
M-AliCommented:
This note may be of help:

Doc ID:  Note:1030046.6
Subject:  OLE AUTOMATION DOES NOT WORK AFTER UPGRADE TO OFFICE97

Problem Description:
====================
 
After you upgrade to Microsoft Office97, OLE calls in
Oracle (R) Developer applications no longer work.

Example 1
---------
In Forms, you use OLE automation to send data to a Microsoft Word
document and to print letters.  This worked in Word 6.0, but
when you upgrade to Word 8.0 (Office97), the letters do not print.

Example 2
---------
When you try to get an object handle to the Excel97 Workbooks
collection using the OLE2 Package, the following error occurs:

FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-305500

This code worked in Excel 7.0.

 
[ Search Words: Office 97 msoffice MS office object linking and embedding
                application GET_OBJ_PROPERTY workbook INVOKE_OBJ appshow  
                upgrade upgrading bug483090 olex.rdf demo FRM 40735
                ORA 305500 Windows ]

Solution Description:
=====================
 
WORD
----
Microsoft changed the upgraded version of Word which causes it
to come up as a hidden application.  Refer to Bug 336913, which
was closed as not a bug.

Workaround
----------
Invoke 'AppShow', a Microsoft Word method.
Add the following line of code:

   OLE2.INVOKE(application, 'AppShow');


EXCEL
-----
Microsoft changed the 'Workbooks' object, which is now a
property of the 'Excel.Application' object.  Refer to
Bug 483090, which was closed as a vendor problem.

Workaround
----------
Replace the following call:

   workbooks := OLE2.INVOKE_OBJ(application, 'Workbooks');

with:

   workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

Example
-------
PACKAGE BODY olewrap IS
   -- Declare the OLE objects
   application    OLE2.OBJ_TYPE;
   workbooks      OLE2.OBJ_TYPE;
   workbook       OLE2.OBJ_TYPE;
   worksheets     OLE2.OBJ_TYPE;
   worksheet      OLE2.OBJ_TYPE;
   cell           OLE2.OBJ_TYPE;
   args           OLE2.LIST_TYPE;

PROCEDURE init IS
BEGIN
   -- Start Excel and make it visible
   application := OLE2.CREATE_OBJ('Excel.Application');
   OLE2.SET_PROPERTY(application,'Visible', 'True');

   workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

   args := OLE2.CREATE_ARGLIST;
   OLE2.ADD_ARG(args, 'c:\test\ms\excel\test.xls');
   workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
   OLE2.DESTROY_ARGLIST(args);

   worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');

   worksheet := OLE2.GET_OBJ_PROPERTY(worksheets, 'Add');

   -- Return object handle to cell A1 on the new Worksheet
   args := OLE2.CREATE_ARGLIST;
   OLE2.ADD_ARG(args, 1);
   OLE2.ADD_ARG(args, 1);
   cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
   OLE2.DESTROY_ARGLIST(args);

   -- Set the contents of the cell to 'Hello Excel!'
   OLE2.SET_PROPERTY(cell, 'Value', 'Hello Excel!');

END;
 
PROCEDURE addstuff IS
BEGIN
   -- Return object handle to cell A1 on the new Worksheet
   args := OLE2.CREATE_ARGLIST;
   OLE2.ADD_ARG(args, 2);
   OLE2.ADD_ARG(args, 2);
   cell := OLE2.INVOKE_OBJ(worksheet, 'Cells', args);
   OLE2.DESTROY_ARGLIST(args);
 
   -- Set the contents of the cell to 'This is the added stuff'
   OLE2.SET_PROPERTY(cell, 'Value', 'This is the added stuff');
END;
 
PROCEDURE stop IS
BEGIN
   -- Release the OLE objects
   args := OLE2.CREATE_ARGLIST;
   OLE2.ADD_ARG(args, 'C:\OLETEST2.XLS');
   OLE2.INVOKE(worksheet, 'SaveAs', args);
   OLE2.DESTROY_ARGLIST(args);
   OLE2.RELEASE_OBJ(cell);
   OLE2.RELEASE_OBJ(worksheet);
   OLE2.RELEASE_OBJ(worksheets);
   OLE2.RELEASE_OBJ(workbook);
   OLE2.RELEASE_OBJ(workbooks);
   OLE2.RELEASE_OBJ(application);
END;
 
 
Solution Explanation:
=====================
 
Microsoft changed some of the OLE calling interfaces for the Office97
products.  Thus, you need to change existing OLE calls to comply with
the Office97 interface.

Consult with Microsoft and the Office97 product suite documentation
for information on the OLE interface.


Additional Information:
=======================
 
Related Bugs:
-------------
[BUG:336913] (Closed, Not a Bug)
CANNOT START WORD USING OLE2 PACKAGE FOR OLE AUTOMATION

[BUG:483090] (Closed, Vendor OS Problem)
FORM GIVES FRM-40735: UNHANDLED EXCEPTION ORA-305500 AGAINST EXCEL97.OFMPE0497
.

 

--------------------------------------------------------------------------------
 
 Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.  



HTH
Ali
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
motorwayAuthor Commented:
Every time I run this code It adds a new worksheet. I want to work with the existing worksheet in the .xls file. How can I do it.
0
 
M-AliCommented:
The sample code is adding a worksheet using this statement:
....
worksheet := OLE2.GET_OBJ_PROPERTY(worksheets, 'Add');
....

If you want to work on the existing worksheets do not use the statement. Your code to edit should read:

Declare
  application    OLE2.OBJ_TYPE;
  workbooks      OLE2.OBJ_TYPE;
  workbook       OLE2.OBJ_TYPE;
  worksheets     OLE2.OBJ_TYPE;
  worksheet      OLE2.OBJ_TYPE;
  cell           OLE2.OBJ_TYPE;
  args           OLE2.LIST_TYPE;

BEGIN
  -- Start Excel and make it visible
  application := OLE2.CREATE_OBJ('Excel.Application');
  OLE2.SET_PROPERTY(application,'Visible', 'True');

  workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

  args := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(args, 'c:\test\ms\excel\test.xls');
  workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
  OLE2.DESTROY_ARGLIST(args);
END;


HTH
Ali
0
 
motorwayAuthor Commented:
I've tried the following code. But it doesnt modidfy the excel file. Nothing is added into the file. Although it does ask for saving changes.

Begin
application := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(application,'Visible', 'True');

workbooks := OLE2.GET_OBJ_PROPERTY (application, 'Workbooks');

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 'c:\book1.xls');
workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
OLE2.DESTROY_ARGLIST(args);
         
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, 5);
OLE2.ADD_ARG(args, 5);
OLE2.SET_PROPERTY (workbook, 'Value', 'Test',args);
OLE2.DESTROY_ARGLIST(args);

args:=ole2.create_arglist;
ole2.add_arg(args,'C:\book1.XLS');
ole2.invoke(workbook,'SaveAs',args);
ole2.destroy_arglist(args);
ole2.invoke(application,'Quit');
End;
0
 
M-AliCommented:
OK, I forgot one statement. Try this:

Begin
  application := OLE2.CREATE_OBJ('Excel.Application');
  OLE2.SET_PROPERTY(application,'Visible', 'True');

  workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

  args := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(args, 'c:\book1.xls');
  workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
  OLE2.DESTROY_ARGLIST(args);

  /**/
  args := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(args,'Sheet1');
  worksheet := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets', args);
OLE2.DESTROY_ARGLIST(args);
  /**/

  args := OLE2.CREATE_ARGLIST;
  OLE2.ADD_ARG(args, 1);
  OLE2.ADD_ARG(args, 1);

/* THIS IS INCORRECT
---OLE2.SET_PROPERTY (workbook, 'Value', 'Test',args);
*/
/* THIS IS WHAT YOU SHOUlD USE */
  cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
  OLE2.DESTROY_ARGLIST(args);
  OLE2.SET_PROPERTY(cell, 'Value', 'Hello Excel!');
........

Do not forget to RELEASE all the objects, as it will leave excell processes running.

Ali
0
 
jpkempCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept M-Ali's comment as answer
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now