topic Data from internal table to separate Excel sheets in Application Development and Automation Discussions

Data from internal table to separate Excel sheets

JozsefSzikszai — Tue, 16 Oct 2007 07:44:45 GMT

hi All,

I have to write a program which saves data from an internal table to separate Excel sheets (of one Excel Workbook) - broken by Controlling Area. If any of you did this before, pls. copy sample programs here. Any piece of valuable information of handling Excel in ABAP is welcome as well. Pls. do not copy external links, as I am not able to acces the Internet (except SDN).

thanks in advance for all answers

Re: Data from internal table to separate Excel sheets

Former Member — Tue, 16 Oct 2007 07:48:46 GMT

Hi,

use below FM

CALL FUNCTION ‘MS_EXCEL_OLE_STANDARD_DAT’

IMPORTING

FILE_NAME = (file path)

TABLES

DATA_TAB = I_FINAL

FIELDNAMES = I_COLNAMES

EXCEPTIONS

FILE_NOT_EXIST = 1

FILENAME_EXPECTED = 2

COMMUNICATION_ERROR = 3

OLE_OBJECT_METHOD_ERROR = 4

OLE_OBJECT_PROPERTY_ERROR = 5

INVALID_FILENAME = 6

INVALID_PIVOT_FIELDS = 7

DOWNLOAD_PROBLEM = 8

OTHERS = 9

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

Thanks!

Brunda Palla

'Reward if useful'

Re: Data from internal table to separate Excel sheets

Former Member — Tue, 16 Oct 2007 07:53:20 GMT

Hey Eric,

Hope you are doing good.

Have you seen Rich's blog on this. Here is the link.

/people/rich.heilman2/blog/2005/09/12/manipulate-excel-with-ole-abap

Hope it helps.

Regards,

Srihari

Re: Data from internal table to separate Excel sheets

Former Member — Tue, 16 Oct 2007 07:53:25 GMT

Hi Eric,

Please follow the steps in the below mentioned blog:

/people/sudheer.junnuthula2/blog/2006/12/27/creating-excel-with-more-than-one-page

Also, you can try using the Function Module

MS_EXCEL_OLE_STANDARD_OLE-To download the data in different sheets of the same excel file.

Else,you can refer to the below mentioned code as well:

report zole123.

INCLUDE ole2incl.

DATA: count TYPE i,

application TYPE ole2_object,

workbook TYPE ole2_object,

excel TYPE ole2_object,

sheet TYPE ole2_object,

cells TYPE ole2_object.

CONSTANTS: row_max TYPE i VALUE 256.

DATA index TYPE i.

changed

DATA: BEGIN OF itab1 OCCURS 0,

first_name(10),

field_2(10),

END OF itab1.

end of change

DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.

DATA: BEGIN OF itab3 OCCURS 0, place(50), END OF itab3.

************************************************************************

*START-OF-SELECTION

START-OF-SELECTION.

changed

APPEND: 'surname1' TO itab2,

'worli' TO itab3,

'surname2' TO itab2,

'chowpatty' TO itab3,

'surname3' TO itab2,

'versova' TO itab3,

'surname4' TO itab2,

'grant road' TO itab3,

'surname5' TO itab2,

'gaon' TO itab3,

'surname6' TO itab2,

'mahim' TO itab3.

itab1-first_name = 'name1'.

itab1-field_2 = 'other1'.

append itab1.

itab1-first_name = 'name2'.

itab1-field_2 = 'other2'.

append itab1.

itab1-first_name = 'name3'.

itab1-field_2 = 'other3'.

append itab1.

itab1-first_name = 'name4'.

itab1-field_2 = 'other4'.

append itab1.

itab1-first_name = 'name5'.

itab1-field_2 = 'other5'.

append itab1.

itab1-first_name = 'name6'.

itab1-field_2 = 'other6'.

append itab1.

end of change

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

CREATE OBJECT excel 'EXCEL.APPLICATION'.

IF sy-subrc NE 0.

WRITE: / 'No EXCEL creation possible'.

STOP.

ENDIF.

SET PROPERTY OF excel 'DisplayAlerts' = 0.

CALL METHOD OF excel 'WORKBOOKS' = workbook .

SET PROPERTY OF excel 'VISIBLE' = 1.

Create worksheet

SET PROPERTY OF excel 'SheetsInNewWorkbook' = 1.

CALL METHOD OF workbook 'ADD'.

DO 3 TIMES.

IF sy-index GT 1.

CALL METHOD OF excel 'WORKSHEETS' = sheet.

CALL METHOD OF sheet 'ADD'.

FREE OBJECT sheet.

ENDIF.

ENDDO.

count = 1.

DO 3 TIMES.

CALL METHOD OF excel 'WORKSHEETS' = sheet

EXPORTING

#1 = count.

perform get_sheet_name using scnt sname.

CASE count.

WHEN '1'.

SET PROPERTY OF sheet 'NAME' = 'firstName'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab1.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 = column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Value' = itab1-first_name.

changed

index = index + 1.

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Value' = itab1-field_2.

end of change

ENDLOOP.

WHEN '2'.

SET PROPERTY OF sheet 'NAME' = 'LastName'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab2.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Formula' = itab2-last_name.

SET PROPERTY OF cells 'Value' = itab2-last_name.

ENDLOOP.

WHEN '3'.

SET PROPERTY OF sheet 'NAME' = 'place'.

CALL METHOD OF sheet 'ACTIVATE'.

" add header here

LOOP AT itab3.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name " for headings change the - 1 to + 1 to accomodate 2 extra lines

CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = index.

SET PROPERTY OF cells 'Formula' = itab3-place.

SET PROPERTY OF cells 'Value' = itab3-place.

ENDLOOP.

ENDCASE.

count = count + 1.

ENDDO.

Save excel speadsheet to particular filename

GET PROPERTY OF excel 'ActiveSheet' = sheet.

CALL METHOD OF sheet 'SaveAs'

EXPORTING #1 = 'c:\temp\exceldoc1.xls' "filename

#2 = 1. "fileFormat

In case you have any further clarifications,do let me know.

Regards,

Puneet Jhari.

Re: Data from internal table to separate Excel sheets

JozsefSzikszai — Tue, 16 Oct 2007 07:53:56 GMT

hi Brunda,

thank you, but the requirement is to have multiple sheets in one Excel workbook.

Re: Data from internal table to separate Excel sheets

Former Member — Tue, 16 Oct 2007 08:10:31 GMT

Hi,

Then go through the below code

DATA : EXCEL TYPE OLE2_OBJECT,

WORKBOOKS TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

CELLS TYPE OLE2_OBJECT,

SHEET TYPE OLE2_OBJECT,

FONT TYPE OLE2_OBJECT,

H_COM1 TYPE OLE2_OBJECT,

F TYPE RLGRAP-FILENAME,

FILEP TYPE DRAW-FILEP,

G_FILE_DEST TYPE STRING,

G_FILE_TEMP TYPE STRING,

G_SYSUBRC TYPE SY-SUBRC,

G_FILE_SRC TYPE DRAW-FILEP.

START THE EXCEL APPLICATION

CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

CALL METHOD OF EXCEL 'WORKBOOKS' = workbooks.

SET PROPERTY OF EXCEL 'VISIBLE' = 0.

CALL METHOD OF WORKBOOKS 'OPEN' EXPORTING

#1 = filep.

For Sheet1

CALL METHOD OF excel 'Worksheets' = sheet

EXPORTING #1 = 1.

CALL METHOD OF sheet 'Activate'.

FREE OBJECT sheet.

CALL METHOD OF EXCEL 'CELLS' = cells

exporting #1 = 1

#2 = 15.

SET PROPERTY OF CELLS 'VALUE' = 'ABCD'.

CALL METHOD OF CELLS 'FONT' = font.

set property of font 'SIZE' = '20'.

free OBJECT cells.

GET PROPERTY OF EXCEL 'ACTIVESHEET' = sheet.

For Sheet2

CALL METHOD OF excel 'Worksheets' = sheet

EXPORTING #1 = 2.

CALL METHOD OF sheet 'Activate'.

FREE OBJECT sheet.

CALL METHOD OF EXCEL 'CELLS' = cells

exporting #1 = 1

#2 = 6.

SET PROPERTY OF CELLS 'VALUE' = 'XYZ'.

CALL METHOD OF CELLS 'FONT' = font.

set property of font 'SIZE' = '20'.

GET PROPERTY OF EXCEL 'ACTIVESHEET' = sheet.

CALL METHOD OF SHEET 'SAVEAS' EXPORTING

#1 = g_file_dest

#2 = 1.

CALL METHOD OF WORKBOOKS 'CLOSE'.

CALL METHOD OF EXCEL 'QUIT'.

FREE OBJECT EXCEL.

Thanks!

Brunda

'Reward if useful'

Re: Data from internal table to separate Excel sheets

JozsefSzikszai — Tue, 16 Oct 2007 14:06:58 GMT

thanks for all valuable comments... I am getting closer... but still there is an interesting problem:

if I double click on the newly created Excel file (in Windows Explorer), the Excel menu appears, but the grid (and the data in it) does not. is it some problem with my file, or with SAP, or with Windows? because until some point it seems to work fine, i. e. I was able to create and save test file (with test data) with multiple sheets, but when I want to do it in my real program, than this kind of error comes and after that my test file is not viewable neither.

Re: Data from internal table to separate Excel sheets

JozsefSzikszai — Tue, 16 Oct 2007 14:29:46 GMT

OK, I've got it now: I have to go back to selection screen to be able to open the saved Excel file. Anyone knows the reason for this? Can it be avoided? (I am FREEing each OLE object in the program.)