topic SaveAs in excel using OLE doesn't work in Application Development and Automation Discussions

SaveAs in excel using OLE doesn't work

Former Member — Thu, 28 May 2009 21:43:50 GMT

Hi,

I'm trying to create an excel, using OLE.

Excel opens up just fine, with the correct number of sheets with data in the columns. But it doesn''t save although sy-subrc = 0 after the SaveAs method is called.

Could you please have a look at my code, and try and help me out - Thanks!

Regards,

Vibeke

DATA: file(255) TYPE c VALUE 'c:\test1.xls'.

 LOOP AT gt_excel INTO gs_excel.

*   Create new sheet
    ON CHANGE OF gs_excel-sheet_name.

*--For the first loop, Excel is initiated and one new sheet is added
      IF sy-tabix = 1 .
        CREATE OBJECT excel 'EXCEL.APPLICATION' .
        SET PROPERTY OF excel 'Visible' = 1 .
        GET PROPERTY OF excel 'Workbooks' = workbook.

        GET PROPERTY OF workbook 'Application' = application .
        SET PROPERTY OF application 'SheetsInNewWorkbook' = 1 .
        CALL METHOD OF workbook 'Add' = wbook .
        GET PROPERTY OF application 'ActiveSheet' = activesheet .
        SET PROPERTY OF activesheet 'Name' = gs_excel-sheet_name.
*--For the rest of loops, other sheets are added
      ELSE .
        GET PROPERTY OF wbook 'Sheets' = sheets .
        CALL METHOD OF sheets 'Add' = newsheet .
        SET PROPERTY OF newsheet 'Name' = gs_excel-sheet_name.
        g_row = 0.
      ENDIF.

    ENDON.

    g_row = g_row + 1.
    g_col = 1.

    CALL METHOD OF excel 'cells' = cells EXPORTING #1 = g_row #2 = g_col.
    SET PROPERTY OF cells 'value' = gs_excel-col1.
    SET PROPERTY OF cells 'ColumnWidth' = '90'.
     g_col = g_col + 1.
    CALL METHOD OF excel 'cells' = cells EXPORTING #1 = g_row #2 = g_col.
    SET PROPERTY OF cells 'value' = gs_excel-col2.
    SET PROPERTY OF cells 'ColumnWidth' = '30'.
  
  ENDLOOP.


* save excel speadsheet
  CALL METHOD OF workbook 'SaveAs' EXPORTING #1 = file.
  CALL METHOD OF workbook 'Close'.
  CALL METHOD OF excel 'Quit'.



  FREE OBJECT: application, workbook, sheets, sheet, cells, interior.
  FREE OBJECT: excel, wbook, activesheet, newsheet              .

Re: SaveAs in excel using OLE doesn't work

Former Member — Fri, 29 May 2009 00:36:23 GMT

Hi.

Does not the file already exist?

At a new file.
ActiveWorkbook.Save

CALL METHOD OF E_WORK 'Save'.

For the existing file.
ActiveWorkbook.SaveAs Filename:="C:\test.xls"

CALL METHOD OF E_WORK 'SaveAs'

EXPORTING

#1 = P_FILE.

Re: SaveAs in excel using OLE doesn't work

Former Member — Fri, 29 May 2009 03:52:00 GMT

Hi,

Kindly refer the code below,

It will download the two internal data into 2 sheets of one excel,



INCLUDE ole2incl.                       "include used for providing classes used for using create object for creating application and worksheets
DATA: application TYPE ole2_object,
       workbook TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.

*DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
*DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.

TABLES: vbap,mara.

TYPES: BEGIN OF itab,
       lifnr TYPE lfa1-lifnr,
       land1 TYPE lfa1-land1,
*       name1 TYPE lfa1-name1,
*       ort01 TYPE lfa1-ort01,
       END OF itab.

DATA: BEGIN OF itab2 OCCURS 0,
matnr TYPE mara-matnr,
ersda TYPE mara-ersda,
ernam TYPE mara-ernam,
END OF itab2.
 data : v_row type sy-tabix.

DATA: itab1 TYPE STANDARD TABLE OF itab WITH HEADER LINE.

DATA: IT_XLSTAB TYPE STANDARD TABLE OF ITAB ,
      WA_XLSTAB LIKE LINE OF IT_XLSTAB.

START-OF-SELECTION.

  SELECT lifnr land1 fROM lfa1  INTO CORRESPONDING FIELDS OF TABLE itab1 UP TO 5 ROWS.

  SELECT matnr
  ersda
  ernam
  FROM mara
  INTO CORRESPONDING FIELDS OF
  TABLE itab2 UP TO 5 ROWS.

START-OF-SELECTION.

  CREATE OBJECT application 'excel.application'.
  SET PROPERTY OF application 'visible' = 1.
  CALL METHOD OF application 'Workbooks' = workbook.
  CALL METHOD OF workbook 'Add'.

* Create first Excel Sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 1.
  CALL METHOD OF sheet 'Activate'.
  SET PROPERTY OF sheet 'Name' = 'Sheet1'.
  clear v_row.
  LOOP AT itab1.
     v_row = sy-tabix.
      perform fill_cell  using  v_row 1  itab1-lifnr.
      perform fill_cell  using  v_row 2 itab1-land1.
  ENDLOOP.

* Create second Excel sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 2.
  SET PROPERTY OF sheet 'Name' = 'Sheet2'.
  CALL METHOD OF sheet 'Activate'.
  clear v_row.
  LOOP AT itab2.
     v_row = sy-tabix.

      perform fill_cell using  v_row 1 itab2-matnr.
      perform fill_cell using  v_row 2 itab2-ersda.
      perform fill_cell using  v_row 3 itab2-ernam.

  ENDLOOP.

* Save excel speadsheet to particular filename
  CALL METHOD OF sheet 'SaveAs'
                  EXPORTING #1 = 'c:\temp\excelgeet.xls'     "filename
                            #2 = 1.                          "fileFormat

*  Closes excel window, data is lost if not saved
  SET PROPERTY OF application 'visible' = 0.

*  call method of sheet 'CLOSE'
*
*  EXPORTING #1 = 'YES'.
*&---------------------------------------------------------------------*
*& both the below coding closes the apllication permanently from the task manager also.
*&---------------------------------------------------------------------*

SET PROPERTY OF application 'DisplayAlerts' = 0.
   free OBJECT application.


**  call method of application 'QUIT'.
**
**  FREE OBJECT: APPLICATION,
**               SHEET.

*&---------------------------------------------------------------------*
*&      Form  fill_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_V_COL  text
*      -->P_ITAB1_LIFNR  text
*----------------------------------------------------------------------*
form fill_cell  using row  col val.

    CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = row  #2 = col.
    SET PROPERTY OF cells 'Value' = val.


endform.                    " fill_cell

Hope it helps

Regards

Mansi

Re: SaveAs in excel using OLE doesn't work

Former Member — Fri, 29 May 2009 08:23:19 GMT

Hi Tayori,

It's a new file.

I tried your suggestion but added the filename to it.

I cannot get it to work.

Best Regards,

Vibeke

Re: SaveAs in excel using OLE doesn't work

Former Member — Fri, 29 May 2009 08:24:31 GMT

Hi Mansi,

I can't get your example to work.

Best Regards,

Vibeke

Re: SaveAs in excel using OLE doesn't work

Former Member — Fri, 29 May 2009 08:42:16 GMT

Solved it.

I had a wrong reference.