Solved

Using Excel OLE Control in Forms 6i

Posted on 2002-06-10
8
9,226 Views
Last Modified: 2011-09-20
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
Comment
Question by:motorway
8 Comments
 
LVL 4

Expert Comment

by:jtrifts
ID: 7067585
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
 

Author Comment

by:motorway
ID: 7068392
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
 
LVL 6

Expert Comment

by:M-Ali
ID: 7069187
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
 

Author Comment

by:motorway
ID: 7077303
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Expert Comment

by:M-Ali
ID: 7080108
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
 

Author Comment

by:motorway
ID: 7080317
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
 
LVL 6

Accepted Solution

by:
M-Ali earned 200 total points
ID: 7081130
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
 
LVL 5

Expert Comment

by:jpkemp
ID: 9034261
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now