Updating an excel sheet from report

[ankit_munshi2]

Hello all,

I have a requirement to update an existing excel sheet present on presentation server, saving it off and then mailing it to certain users as attachment. I am using OLE automation classes for the same and am able to update the excel sheet.

However at the end of program the excel sheet doesn't 'Save off' and close on its own . I have to manually save it with the same name (so that it updates the previous version of excel) . Then I close it manually . is there any way to do it automatically ?

please note - i have already searched existing OLE posts on sdn and none of them worked. here is the code I m using :

CREATE OBJECT application 'Excel.Application'.

   SET PROPERTY OF application 'Visible' = 1.
   CALL METHOD OF
       application
       'Workbooks' = workbook.

*--Opening the existing document
   CALL METHOD OF
       workbook
       'Open'   = gs_wbook
     EXPORTING
       #1       = p_file.


* Create first Excel Sheet
   CALL METHOD OF
       application
       'Worksheets' = first_sheet
     EXPORTING
       #1           = 1.

   CALL METHOD OF
       application
       'Worksheets' = sheets.
   CALL METHOD OF
       sheets
       'Add'

     EXPORTING
       #1     = first_sheet.


   CONCATENATE sy-datum sy-uzeit INTO l_string SEPARATED BY space.

   GET PROPERTY OF application 'ActiveSheet' = sheet.
   SET PROPERTY OF sheet 'Name' = l_string.
   GET PROPERTY OF application 'ActiveWorkbook' = workbook.

   PERFORM fill_cell USING 1 1 1 'mandt'(001).
   PERFORM fill_cell USING 1 2 1 'vertrag'(002).
   PERFORM fill_cell USING 1 3 1 'anlage'(003).
   PERFORM fill_cell USING 1 4 1 'vkonto'(004).
   PERFORM fill_cell USING 1 5 1 'auszdat'(005).

   index = 1.

   LOOP AT lt_ever INTO lwa_ever."itab1.
     index = index + 1.
     PERFORM fill_cell USING index 1 1 lwa_ever-mandt.
     PERFORM fill_cell USING index 2 1 lwa_ever-vertrag.
     PERFORM fill_cell USING index 3 1 lwa_ever-anlage.
     PERFORM fill_cell USING index 4 1 lwa_ever-vkonto.
     PERFORM fill_cell USING index 5 1 lwa_ever-auszdat.

   ENDLOOP.


* Save excel speadsheet to particular filename
  CALL METHOD OF
      sheets
      'SaveAs'
    EXPORTING
      #1       = 'C:\Users\amunshi\Desktop\test1.xlsx'     "filename
       #2       = 1.                          "fileFormat

   IF 1 = 2.
   ENDIF.
   CALL METHOD OF
       workbook
       'CLOSE'

     EXPORTING
       #1       = 0
       #2       = 0
       #3       = 0.
   FREE OBJECT workbook.


   CALL METHOD OF
       sheets
       'APPCLOSE'.
   CALL METHOD OF
       sheets
       'QUIT'.
   FREE OBJECT sheets.
   FREE OBJECT application.

Any help would be appreciated!

Regards,

AM

[nikolayevstigneev]

Hi, Ankit!

I have revised my last report using OLE (it was long ago ), I used

   CALL METHOD OF
       application             <- please, check this
       'QUIT'.

there to close EXCEL.

I also used CALL FUNCTION 'FLUSH' before killing OLE objects - this helps when you've done what you wanted (and EXCEL closed) but it's process still hangs in Task Manager.

[ankit_munshi2]

Thanks , the problem is solved. here is what I did -

CALL METHOD OF
       workbook
       'SAVE'.

   CALL METHOD OF
       workbook
       'CLOSE'

     EXPORTING
       #1       = 0
       #2       = 0
       #3       = 0.
   FREE OBJECT workbook.


   CALL METHOD OF
       sheets
       'APPCLOSE'.
   CALL METHOD OF
       sheets
       'QUIT'.
   CALL FUNCTION 'FLUSH' .
   FREE OBJECT sheets.
   FREE OBJECT application.