12 Replies Latest reply: Jul 21, 2005 4:38 PM by 447843 RSS

    Closeing Excel with OLE2

    20359
      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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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-Oracle
                      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
                        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
                          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
                            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
                              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
                                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