SaveAs in excel using OLE doesn't work

[Former Member]

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              .

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

Hi Mansi,

I can't get your example to work.

Best Regards,

Vibeke

[Former Member]

Solved it.

I had a wrong reference.