This discussion is archived
12 Replies Latest reply: Jul 21, 2005 2:38 PM by 447843 RSS

Closeing Excel with OLE2

20359 Newbie
Currently Being Moderated
Does anybody know how to close Excel once you have started it via the OLE2 package? I can close the spreadsheet but the Excel application still stays active.
  • 1. Re: Closeing Excel with OLE2
    Grant Ronald Employee ACE
    Currently Being Moderated
    Once you make calls to release your objects (like)

    RELEASE_OBJ() you need to call
    OLE2.INVOKE (app, 'Quit');

    I know this works with Word but give it a go with Excel.
    Grant Ronald
    Forms Product Management
  • 2. Re: Closeing Excel with OLE2
    20359 Newbie
    Currently Being Moderated
    This removes Excel from the desktop but the application still runs. If you view the task mamager you see all the excel aps there taking up all the recourse.
  • 3. Re: Closeing Excel with OLE2
    kprakash Newbie
    Currently Being Moderated
    Jaco,

    Can you please post a simple example code which shows
    the problem.

    Regards

    Kavitha Prakash
    Oracle Support
  • 4. Re: Closeing Excel with OLE2
    20359 Newbie
    Currently Being Moderated
    This is the test proc.


    PROCEDURE read_excel_file IS

    /***********************/
    /* Declare 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;
    hFontObj OLE2.OBJ_TYPE;
    hBorderObj OLE2.OBJ_TYPE;

    /*****************************************/
    /* Declare handles to OLE argument lists */
    /*****************************************/
    args OLE2.LIST_TYPE;


    /*************************************************************************************/
    /* Declare the PL/SQL variables which will hold the data returned from the database. */
    /*************************************************************************************/


    v_row NUMBER;
    v_value VARCHAR2(300);


    BEGIN

    SET_APPLICATION_PROPERTY(CURSOR_STYLE,'busy');


    /***************************************/
    /* Create handle to application object */
    /***************************************/
    application := OLE2.CREATE_OBJ('Excel.Application');


    /**********************************************************/
    /* Create a Workbooks collection and add new Workbook to */
    /* Workbooks collection */
    /**********************************************************/
    workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

    /********************/
    /* Get the workbook */
    /********************/
    args := OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, 'C:\Medscheme\Play\Barlows.xls');
    workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
    OLE2.DESTROY_ARGLIST(args);

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

    args := OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, 'Sheet1');
    OLE2.INVOKE(worksheets, 'Activate');
    OLE2.DESTROY_ARGLIST(args);

    worksheet:=OLE2.GET_OBJ_PROPERTY(workbook,'ActiveSheet');



    /*************************************************************/
    /* Enable user to view the Excel application to see results. */
    /*************************************************************/
    OLE2.SET_PROPERTY(application, 'Visible', 'True');

    /**********************************************************************************************************************/
    GO_BLOCK('excel_data');
    :system.message_level := 20;
    v_row := 1;
              
    /*******************************/
    /* Loop until we exit the loop */
    /*******************************/
    LOOP
         
         v_value := NULL;
         
         /*************/
         /* Goto cell */
         /*************/
    cell := goto_cell(worksheet , v_row , 1);
         
         /*******************************/
         /* Get the value from the cell */
         /*******************************/
         v_value := OLE2.GET_NUM_PROPERTY(cell, 'Value');
              
    /*******************************************************/
    /* If there is no data in the first cell exit the loop */
    /*******************************************************/
    IF v_value IS NULL OR
         v_value = 0 THEN
         
         exit;
         
    END IF;     

    /************************************/
         /* Set the value in the forms block */
         /************************************/
         :excel_data.col1 := v_value;


    /*************/
         /* Goto cell */
         /*************/
    cell := goto_cell(worksheet , v_row , 2);
         
         /*******************************/
         /* Get the value from the cell */
         /*******************************/      
         v_value := OLE2.GET_NUM_PROPERTY(cell, 'Value');
         
         /************************************/
         /* Set the value in the forms block */
         /************************************/
         :excel_data.col2 := v_value;

    /************************************/
         /* Create a new record in the block */
         /************************************/      
         next_record;
         
         /*****************************/
         /* Increment the row counter */
         /*****************************/
         v_row := v_row + 1;
         
    END LOOP;

         






    /**********************************************************************************************************************/


    /***************/
    /* Close Excel */
    /***************/

    /**********************************/
    /* Release all OLE object handles */
    /**********************************/
    OLE2.RELEASE_OBJ(cell);
    OLE2.RELEASE_OBJ(worksheet);
    OLE2.RELEASE_OBJ(worksheets);
    OLE2.RELEASE_OBJ(workbook);
    OLE2.RELEASE_OBJ(workbooks);

    /***************/
    /* Close Excel */
    /***************/
    OLE2.INVOKE(application, 'Quit'); -- This closes excel but the app still runs on the system


    OLE2.RELEASE_OBJ(application);

    SET_APPLICATION_PROPERTY(CURSOR_STYLE,'default');


    exception when OLE2.OLE_ERROR then
         --dsp_msg('OLE error');
         raise;
    when others then
    message(sqlerrm);
    END;
  • 5. Re: Closeing Excel with OLE2
    kprakash Newbie
    Currently Being Moderated
    Hi,

    OLE2.INVOKE(worksheets, 'Activate')is preventing excel.exe
    from closing down. Try recoding as shown below:

    args := OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, 'C:\Medscheme\Play\Barlows.xls');
    workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
    OLE2.DESTROY_ARGLIST(args);

    /* you can comment out the below line if you don't need it */
    worksheets:=OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');

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

    OLE2.DESTROY_ARGLIST(args);

    -----
    -----
    -----
    ------

    Regards

    Kavitha Prakash
    Oracle Support

  • 6. Re: Closeing Excel with OLE2
    20359 Newbie
    Currently Being Moderated
    Hi Kavitha Prakash

    I change the code but it still does not close Excel. I used VB and I get the same results when I try to quit out of the app.
    I though it might be the version of Excel but all the versions of Excel do this. Any other ideas?

    Thank you.




    PROCEDURE read_excel_file IS

    /***********************/
    /* Declare 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;
    hFontObj OLE2.OBJ_TYPE;
    hBorderObj OLE2.OBJ_TYPE;

    /*****************************************/
    /* Declare handles to OLE argument lists */
    /*****************************************/
    args OLE2.LIST_TYPE;


    /*************************************************************************************/
    /* Declare the PL/SQL variables which will hold the data returned from the database. */
    /*************************************************************************************/


    v_row NUMBER;
    v_value VARCHAR2(300);


    BEGIN

    SET_APPLICATION_PROPERTY(CURSOR_STYLE,'busy');


    /***************************************/
    /* Create handle to application object */
    /***************************************/
    application := OLE2.CREATE_OBJ('Excel.Application');


    /**********************************************************/
    /* Create a Workbooks collection and add new Workbook to */
    /* Workbooks collection */
    /**********************************************************/
    workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

    /********************/
    /* Get the workbook */
    /********************/
    args := OLE2.CREATE_ARGLIST;
    OLE2.ADD_ARG(args, 'C:\Medscheme\Play\Barlows.xls');
    workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
    OLE2.DESTROY_ARGLIST(args);

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

    --args := OLE2.CREATE_ARGLIST;
    --OLE2.ADD_ARG(args, 'Sheet1');
    --OLE2.INVOKE(worksheets, 'Activate');
    --OLE2.DESTROY_ARGLIST(args);

    --worksheet:=OLE2.GET_OBJ_PROPERTY(workbook,'ActiveSheet');

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

    OLE2.DESTROY_ARGLIST(args);


    /*************************************************************/
    /* Enable user to view the Excel application to see results. */
    /*************************************************************/
    OLE2.SET_PROPERTY(application, 'Visible', 'True');

    /**********************************************************************************************************************/
    GO_BLOCK('excel_data');
    :system.message_level := 20;
    v_row := 1;
              
    /*******************************/
    /* Loop until we exit the loop */
    /*******************************/
    LOOP
         
         v_value := NULL;
         
         /*************/
         /* Goto cell */
         /*************/
    cell := goto_cell(worksheet , v_row , 1);
         
         /*******************************/
         /* Get the value from the cell */
         /*******************************/
         v_value := OLE2.GET_NUM_PROPERTY(cell, 'Value');
              
    /*******************************************************/
    /* If there is no data in the first cell exit the loop */
    /*******************************************************/
    IF v_value IS NULL OR
         v_value = 0 THEN
         
         exit;
         
    END IF;     

    /************************************/
         /* Set the value in the forms block */
         /************************************/
         :excel_data.col1 := v_value;


    /*************/
         /* Goto cell */
         /*************/
    cell := goto_cell(worksheet , v_row , 2);
         
         /*******************************/
         /* Get the value from the cell */
         /*******************************/      
         v_value := OLE2.GET_NUM_PROPERTY(cell, 'Value');
         
         /************************************/
         /* Set the value in the forms block */
         /************************************/
         :excel_data.col2 := v_value;

    /************************************/
         /* Create a new record in the block */
         /************************************/      
         next_record;
         
         /*****************************/
         /* Increment the row counter */
         /*****************************/
         v_row := v_row + 1;
         
    END LOOP;

         






    /**********************************************************************************************************************/


    /***************/
    /* Close Excel */
    /***************/

    /**********************************/
    /* Release all OLE object handles */
    /**********************************/
    OLE2.RELEASE_OBJ(cell);
    OLE2.RELEASE_OBJ(worksheet);
    OLE2.RELEASE_OBJ(worksheets);
    OLE2.RELEASE_OBJ(workbook);
    OLE2.RELEASE_OBJ(workbooks);

    /***************/
    /* Close Excel */
    /***************/
    OLE2.INVOKE(application, 'Quit');


    OLE2.RELEASE_OBJ(application);

    SET_APPLICATION_PROPERTY(CURSOR_STYLE,'default');


    exception when OLE2.OLE_ERROR then
         --dsp_msg('OLE error');
         raise;
    when others then
    message(sqlerrm);
    END;

  • 7. Re: Closeing Excel with OLE2
    kprakash Newbie
    Currently Being Moderated
    Hi Jaco,

    Please try using OLE2.RELEASE_OBJ(cell) twice inside your loop so that all cell Objects are released after they are used. Also, delete OLE2.RELEASE_OBJ(cell) outside the loop. Please find below the required code changes:

    Regards

    Kavitha



    LOOP

    v_value := NULL;

    /*************/
    /* Goto cell */
    /*************/
    cell := goto_cell(worksheet , v_row , 1);

    /*******************************/
    /* Get the value from the cell */
    /*******************************/
    v_value := OLE2.GET_NUM_PROPERTY(cell, 'Value');

    OLE2.RELEASE_OBJ(cell); /* Add this Line */

    /*******************************************************/
    /* If there is no data in the first cell exit the loop */
    /*******************************************************/
    IF v_value IS NULL OR
    v_value = 0 THEN

    exit;

    END IF;

    /************************************/
    /* Set the value in the forms block */
    /************************************/
    :excel_data.col1 := v_value;


    /*************/
    /* Goto cell */
    /*************/
    cell := goto_cell(worksheet , v_row , 2);

    /*******************************/
    /* Get the value from the cell */
    /*******************************/
    v_value := OLE2.GET_NUM_PROPERTY(cell, 'Value');

    OLE2.RELEASE_OBJ(cell); /* Add this Line */

    /************************************/
    /* Set the value in the forms block */
    /************************************/
    :excel_data.col2 := v_value;

    /************************************/
    /* Create a new record in the block */
    /************************************/
    next_record;

    /*****************************/
    /* Increment the row counter */
    /*****************************/
    v_row := v_row + 1;

    END LOOP;

    ***************/
    /* Close Excel */
    /***************/

    /**********************************/
    /* Release all OLE object handles */
    /**********************************/
    -- OLE2.RELEASE_OBJ(cell); /* Remove this Line */
    OLE2.RELEASE_OBJ(worksheet);
    OLE2.RELEASE_OBJ(worksheets);
    OLE2.RELEASE_OBJ(workbook);
    OLE2.RELEASE_OBJ(workbooks);

  • 8. Re: Closeing Excel with OLE2
    122352 Newbie
    Currently Being Moderated
    Hi all,

    I use a JavaScript solution to transfer data from Forms to EXCEL created as a 'code-file':

    A WHEN-BUTTON-PRESSED Trigger code:

    declare
    file_id text_io.file_type;
    w varchar2(250);

    code varchar2(1000):='
    // Windows Script Host Sample Script
    // This sample will display Windows Scripting Host properties in Excel.

    var vbOKCancel = 1;
    var vbInformation = 64;
    var vbCancel = 2;

    var L_Welcome_MsgBox_Message_Text = "This script will display Windows Scripting Host properties in Excel.";
    var L_Welcome_MsgBox_Title_Text = "Windows Scripting Host Sample";
    //Welcome();

    //////////////////////////////////////////////////////////////////////////////////
    //
    // Excel Sample
    //
    var objXL = WScript.CreateObject("Excel.Application");

    objXL.Visible = true;

    objXL.WorkBooks.Add;

    objXL.Columns(1).ColumnWidth = 30;
    objXL.Columns(2).ColumnWidth = 5;
    objXL.Columns(3).ColumnWidth = 8;
    objXL.Columns(4).ColumnWidth = 15;
    objXL.Columns(5).ColumnWidth = 15;
    objXL.Columns(6).ColumnWidth = 20;
    objXL.Columns(7).ColumnWidth = 5;
    objXL.Columns(8).ColumnWidth = 10;

    objXL.Cells(1, 1).Value = "EMPNO";
    objXL.Cells(1, 2).Value = "ENAME";
    objXL.Cells(1, 3).Value = "JOB";
    objXL.Cells(1, 4).Value = "MGR";
    objXL.Cells(1, 5).Value = "HIREDATE";
    objXL.Cells(1, 6).Value = "SAL";
    objXL.Cells(1, 7).Value = "COMM";
    objXL.Cells(1, 8).Value = "DEPTNO";

    objXL.Range("A1:E1").Select;
    objXL.Selection.Font.Bold = true;
    objXL.Selection.Interior.ColorIndex = 1;
    objXL.Selection.Interior.Pattern = 1; //xlSolid
    objXL.Selection.Font.ColorIndex = 2;

    objXL.Columns("B:B").Select;
    objXL.Selection.HorizontalAlignment = -4131; // xlLeft

    var intIndex = 2;

    function Show(str1, str2, str3, str4, str5, str6, str7, str8) {
    objXL.Cells(intIndex, 1).Value = str1;
    objXL.Cells(intIndex, 2).Value = str2;
    objXL.Cells(intIndex, 3).Value = str3;
    objXL.Cells(intIndex, 4).Value = str4;
    objXL.Cells(intIndex, 5).Value = str5;
    objXL.Cells(intIndex, 6).Value = str6;
    objXL.Cells(intIndex, 7).Value = str7;
    objXL.Cells(intIndex, 8).Value = str8;
    intIndex++;
    objXL.Cells(intIndex, 1).Select;
    }';

    begin

    go_block('EMP');
    execute_query;
    first_record;
    file_id := text_io.fopen('C:\temp\excel-emp.js', 'W');
    text_io.put_line(file_id, code);

    loop
              text_io.put_line(file_id, 'Show('     ||:EMPNO||',"'||:JOB||'",'||:MGR||',"'
                                  ||to_char(:HIREDATE,'DD.MM.YYYY')     ||'",' ||:SAL||','||'"'||:COMM||'",'
                                  ||'"'||:DEPTNO          ||'"'
                                  ||');          ');
         exit when :SYSTEM.LAST_RECORD='TRUE';
         next_record;
    end loop;
    text_io.fclose(file_id);

    HOST('cmd /c start C:\temp\excel-emp.js');

    end;


    It works fine and we need also JavaScript know how in the future: thin clients ;-} .

    Regards
    Friedhold

  • 9. Re: Closeing Excel with OLE2
    122352 Newbie
    Currently Being Moderated
    Sorry,

    a little correction, increase the code length :

    declare
    file_id text_io.file_type;
    w varchar2(250);

    code varchar2(2000):='
    // Windows Script Host Sample Script
    // This sample will display Windows Scripting Host properties in Excel.

    ...

    Friedhold

  • 10. Re: Closeing Excel with OLE2
    20359 Newbie
    Currently Being Moderated
    Thank you Kavitha Prakash. This solved my problem. Nice to know we have Oracle people willing to help us.
  • 11. Re: Closeing Excel with OLE2
    415517 Newbie
    Currently Being Moderated
    Dear Jaco,
    I am a beginner to write OLE2 on Oracle.
    I have copied your code but the complier warn me that OLE_ERROR is not declared,
    exception when OLE2.OLE_ERROR
    What wrong?
    And I need to transfer data from Oracle to Excel.
    How your goto_cell coded to navigate the cells? how to assign value to excel cell?

    Thanks.
    -Jasper Lai
  • 12. Re: Closeing Excel with OLE2
    447843 Newbie
    Currently Being Moderated
    Hi

    Read a Oracle PDF file wich explains the basic concepts of OLE automation and in particular how to use the OLE automation facilities within Oracle Forms v4.5 and above to automate Microsoft Excel. Include code Samples.

    You cand download it from: www.leosaavedra.com/forms.pdf
    Any questions: lesaaved@icesi.edu.co