Forms 4.5 calling Excel using OLE2

From: Mervin Samuels <Mervin.Samuels_at_capgemini.uk>
Date: Mon, 13 Jul 1998 11:09:55 +0100
Message-ID: <35A9DCF3.DAE_at_capgemini.uk>

[Quoted] I have a problem when using Forms 4.5 OLE2 commands to call an Excel worksheet that contains a macro. When the call is made, WINDOWS95 resources are drained to an unacceptable level.

The application I am working on currently extracts data from an ORACLE database and writes it to a .txt file using the forms TEXT_IO command. An Excel file containing macros (including Auto_Open) is then called which automatically reads the text file, formats it and sends it to a printer.

There are two methods. The first is currently in use and works satisfactorily. The other that I'm playing with at the moment is an enhancement using OLE2. It appears to drain Windows95 resources.

Method 1
The current method is to use the HOST command to run Excel as a separate process. This is called as follows:

host('C:\….\Excel.exe'||' '||excel macro file);

This works OK but Excel starts up in full view of the user.

Method 2
To make the process appear more transparent without the user seeing Excel startup, this OLE method has been used.

--Initialisation

exapp := OLE2.CREATE_OBJ('excel.application'); workbk_id := OLE2.GET_OBJ_PROPERTY(exapp,'WORKBOOKS'); arg1 := OLE2.CREATE_ARGLIST;
arg2 := OLE2.CREATE_ARGLIST;

OLE2.ADD_ARG(arg1,textfile);
OLE2.ADD_ARG(arg2,1);

• Open & run excel file. Printing is done here file_id:= OLE2.INVOKE_OBJ (workbk_id, 'open', arg1); OLE2.invoke(file_id,'RunAutoMacros',arg2); -- start macros

--Close OLE session with excel server and release objects

OLE2.invoke(exapp,'QUIT');
OLE2.DESTROY_ARGLIST(arg1);
OLE2.DESTROY_ARGLIST(arg2);
OLE2.RELEASE_OBJ(file_id);
OLE2.RELEASE_OBJ(workbk_id);
OLE2.RELEASE_OBJ(exapp);

The OLE method works however it drains WINDOWS resources. If more than 3-4 windows are open then WINDOWS warns that its resources are dangerously low and depending on the amount of processing involved will eventually crash. When WINDOWS Resource Manager is open it drops from 3 green bars to none when the macro is running. The HOST method however succeeds with many more Windows open and relatively minor utilisation of Windows resources.

Does anyone have any ideas? Is it the way I have called OLE from Excel? Is it an ORACLE feature? Is it a MS Windows feature?

Any suggestions would be gratefully received.

Mervin Samuels Received on Mon Jul 13 1998 - 12:09:55 CEST