Re: DEV2000 & OLE Automation--Please Help

From: Dag Lem <lem_at_oslonett.no>
Date: 1995/12/04
Message-ID: <49v5co$b8v_at_news.dax.net>#1/1

mboduch_at_interaccess.com (Michael T. Boduch) wrote:

>What I'd like to do is this:
 

>Use the OLE2 package delivered with Oracle Forms 4.5 to, in the background,
>create a new Excel 5.0 worksheet. Populate that worksheet with data from
>within the form (a multi-row block) and then save that worksheet to the
>file system. Later I could load it into an OLE container item within a
>form or just access it with Excel directly.

<text deleted>

>Which makes me wonder: 1) Can I create both Application and Sheet
>level objects and have them both refer to the same thing? In other
>words, if I have both objects defined, will application-level methods
>invoked on the application object be in contact with the sheet-level
>object? So if I asked the Application-level object what the active
>workbook is, will it refer to the workbook that contains the sheet-level
>object that I created with my call to ole2.obj_create? See what I mean?

If you create a Worksheet object (Excel.Sheet.5), you get to the Application object by reading the Parent of the Worksheet's Parent (Worksheet -> Workbook -> Application).

<text deleted>

>So, please, if *anyone* has blazed this trail before I'd be eternally
>grateful if you'd share your knowledge.
 

>Thanks in advance for any assistance.
 

>Mike

Sample code demonstrating how to insert data in a worksheet follows below (using Excel.Chart.5 - stored with the form).

Now it's my turn to ask questions: :-)

OLE2.Add_Args only supports NUMBER and VARCHAR2. It seems to me I cannot use objects as parameters to object methods. In the code below, I want to reset the Chart's source range for the data i insert into the Worksheet. This does not seem possible using:

OLE2.Invoke(chart, 'ChartWizard', args)

because I'd need to pass a Range object as parameter. I tried to use the Application's Run method to call a macro to do the job instead, but no luck:

Sub SetChartRange()
  Charts(1).ChartWizard source:=Worksheets(1).UsedRange End Sub

If anyone could come up with a solution, I'd be very grateful. And please hurry, I need it yesterday :-)

PACKAGE BODY XL IS
  ole_item VARCHAR2(10) := 'OLE.XL';

  PROCEDURE PlotJobids IS
    app OLE2.OBJ_TYPE;
    book OLE2.OBJ_TYPE;

    chart OLE2.OBJ_TYPE;
    sheet OLE2.OBJ_TYPE;
    range OLE2.OBJ_TYPE;

    args OLE2.LIST_TYPE;
  BEGIN
    IF NOT FORMS_OLE.Server_Active(ole_item) THEN       FORMS_OLE.Activate_Server(ole_item);     END IF;

• Get Chart. chart := FORMS_OLE.Get_Interface_Pointer(ole_item);
• Get Workbook. book := OLE2.Get_Obj_Property(chart, 'Parent');
• Get Application. app := OLE2.Get_Obj_Property(book, 'Parent');
• Get Worksheet. args := OLE2.Create_Arglist; OLE2.Add_Arg(args, 1); -- Index sheet := OLE2.Invoke_Obj(book, 'Worksheets', args); OLE2.Destroy_Arglist(args);
• Loop to transfer data should go here.
• Get Range and set Value. args := OLE2.Create_Arglist; OLE2.Add_Arg(args, 1); -- Row OLE2.Add_Arg(args, 1); -- Column range := OLE2.Invoke_Obj(sheet, 'Cells', args); OLE2.Set_Property(range, 'Value', 'BOWXYZ'); OLE2.Destroy_Arglist(args); OLE2.Release_Obj(range);
• Use Visual Basic subroutine to reselect Chart range.
• *** This code does not work - why? *** args := OLE2.Create_Arglist; OLE2.Add_Arg(args, 'SetChartRange'); OLE2.Invoke(app, 'Run', args); OLE2.Destroy_Arglist(args);

    OLE2.Release_Obj(sheet);
    OLE2.Release_Obj(chart);
    OLE2.Release_Obj(book);
    OLE2.Release_Obj(app);

  END;   PROCEDURE PostForm IS
  BEGIN
    IF FORMS_OLE.Server_Active(ole_item) THEN       FORMS_OLE.Close_Server(ole_item);
    END IF;
  END;
END; Received on Mon Dec 04 1995 - 00:00:00 CET