topic Write in an Excel File in Application Development and Automation Discussions

Write in an Excel File

Former Member — Thu, 23 Oct 2008 15:53:51 GMT

hello

I have this problem: I have to write some informations in an excel file; the excel is not empty with SAP data I have to fill some cells of the "template" xls file. I know how to CREATE an excel file in its aspects, but I can't find a way for OPEN an existing file, modifying it and then save it (in a new file for example); this is my code; I can't see where is the error:

(in path_wb and in full_path there are the path for template input file and the path for the output file)

" excel data
  DATA excel TYPE ole2_object.
  DATA workbook TYPE ole2_object.
  DATA sheet TYPE ole2_object.
  DATA cell TYPE ole2_object.

  " create object to execute the application
  CREATE OBJECT excel 'EXCEL.APPLICATION'.
  " open the workbook
  CALL METHOD OF excel 'Workbooks' = workbook.
  CALL METHOD OF workbook 'OPEN' EXPORTING #1 = path_wb.
  " open the sheet
  CALL METHOD OF excel 'WORKSHEETS' = sheet.
  CALL METHOD OF sheet 'OPEN' EXPORTING #1 = 'Taxonomy'.

  " seek one cell
  CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'D10'.
 " put something in it
  SET PROPERTY OF cell 'VALUE' = 'DATO AGGIUNTO'.

  " store file
  CALL METHOD OF workbook 'SAVEAS'
    EXPORTING
    #1 = fullpath
    #2 = 1.
  CALL METHOD OF workbook 'CLOSE'.
  CALL METHOD OF excel 'QUIT'.

has anyone do it before?

thanks

Re: Write in an Excel File

Former Member — Thu, 23 Oct 2008 17:23:56 GMT

Hello,

Take a look on this: [ABAP - Upload data from Excel to Sap using OO |https://wiki.sdn.sap.com/wiki/x/xOw]. This wiki shows how to access certain excel cell and spreadsheets.

Regards.

Re: Write in an Excel File

Former Member — Fri, 24 Oct 2008 08:13:39 GMT

I found it: the error was the way I was opening the sheet. for opening the sheet the correct way is this:

DATA sheet_name(51).
  WHILE sheet_name <> 'THE_SHEET_NAME_I_WANT_TO_OPEN'.
    CALL METHOD OF excel 'Worksheets' = sheet EXPORTING #1 = sy-index.
    GET PROPERTY OF sheet 'Name' = sheet_name.
  ENDWHILE.
CALL METHOD OF sheet 'Activate'.

  " now I can manipulate the sheet as I want
  CALL METHOD OF excel 'RANGE' = cell EXPORTING #1 = 'D10'.
*  PERFORM set_title.
  SET PROPERTY OF cell 'VALUE' = 'Data added On Existing Sheet'.

Re: Write in an Excel File

Former Member — Fri, 24 Oct 2008 08:14:15 GMT

hope this helps