excel

[Former Member]

hi all,

i have an excel document with two tabs.

i need a function module (probably) that it will fill data to excel from an iternal table.

i dont want to fill the excel from the first cell(1 col - 1 raw ) but from the cell that i will define. that is the main problem. the other problem is that i want it in the second tab.

PLEASE HELP ME!!!

thank you.

[Former Member]

hi, the following code creates a multiple sheet Excel document

INCLUDE ole2incl.

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.

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

*START-OF-SELECTION

START-OF-SELECTION.

APPEND: 'Peter' TO itab1, 'Ivanov' TO itab2,

'=Sheet1!A1 & " " & Sheet2!A1' TO itab3,

'John' TO itab1, 'Smith' TO itab2,

'=Sheet1!A2 & " " & Sheet2!A2' TO itab3.

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'.

LOOP AT itab1.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name

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

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

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'.

LOOP AT itab2.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name

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

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

ENDLOOP.

• Create third Excel sheet

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING #1 = 3.

SET PROPERTY OF sheet 'Name' = 'Sheet3'.

CALL METHOD OF sheet 'Activate'.

LOOP AT itab3.

index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name

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

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

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

ENDLOOP.

• Save excel speadsheet to particular filename

CALL METHOD OF sheet 'SaveAs'

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

#2 = 1. "fileFormat

• Closes excel window, data is lost if not saved

• SET PROPERTY OF application 'visible' = 0.

Hope that helps

[Former Member]

yes thank you . but my problem is that i want to use a specific excel file. it will be helpful even if i dont know object oriented abap.

[Former Member]

Hi

Use the fun module

ALSM_EXCEL_TO_INTERNAL_TABLE

Reward points if useful

Regards

Anji

[Former Member]

Hi,

pla check this code

1.....................

TABLES: MARA.

*

DATA: BEGIN OF ITAB OCCURS 0,

MATNR LIKE MARA-MATNR,

MTART LIKE MARA-MTART,

END OF ITAB.

*

DATA: DATEI TYPE STRING VALUE 'D:\TEST.XLS'.

*

START-OF-SELECTION.

*

*

SELECT * FROM MARA UP TO 10 ROWS.

*

ITAB-MATNR = MARA-MATNR.

ITAB-MTART = MARA-MTART.

APPEND ITAB.

*

ENDSELECT.

*

• Download der Spools

CALL METHOD CL_GUI_FRONTEND_SERVICES=>GUI_DOWNLOAD

EXPORTING

FILENAME = DATEI

WRITE_FIELD_SEPARATOR = 'X'

CHANGING

DATA_TAB = ITAB[].

*

*

CALL METHOD CL_GUI_FRONTEND_SERVICES=>EXECUTE

EXPORTING

DOCUMENT = DATEI.

2.....................

use the following function module

ALSM_EXCEL_TO_INTERNAL_TABLE

pls reward if it helpful

Thanks

Vana

[Former Member]

the alsm excel to iternal is exactly the opposite.

Message was edited by:

antonis nezos

[Former Member]

finally we didnt find any solution....

[kostas_tsioubris]

Hi,

you can use the example posted above with a little change.

CREATE OBJECT application 'excel.application'.

SET PROPERTY OF application 'visible' = 1.

CALL METHOD OF application 'Workbooks' = workbook.

*CALL METHOD OF workbook 'Add'.

CALL METHOD OF workbook 'Open'

exporting

#1 = 'c:\myfile.xls'. "your excel file name here

Kostas

[Former Member]

se euxaristw therma!

tha to dokimasw se ligo kai elpizw na doulepsei

[kostas_tsioubris]

krata me enhmero

[Former Member]

ok . fenetai pws douleuei. alla akoma uparxei kapoio mikro provlimataki..

thelw na ksekinaei apo sugkekrimeni stili. tin grammi tin vrika. episis ti ginetai stin periptwsi mou pou thelw kapoia apo tis stiles tou excel pou einai idi simplirwmenes na min ginontai modify.

Message was edited by:

antonis nezos

[kostas_tsioubris]

Let's talk in English since there might be someone else in the future that has the same questions and cannot read Greek.

In order to write in a specific cell do like this

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = '1' "line index

#2 = '1'. "column index

Kostas

[Former Member]

it seems to be working only for the line index.

[kostas_tsioubris]

Try like this

CALL METHOD OF <b>application</b> 'Cells' = cells

EXPORTING

#1 = '1' "line index

#2 = '1'. "column index

[Former Member]

unfortunatelly not.

i want to start from A4 cell. the last column is I . some columns (for example B ) must be empty. so i have two problems. the one that we said something above but remains unsolved and the other is the field separator. ( i mean that i want in column A to be filled with the first field of my internal table then in column C (not B) for example to be filled with

the second field of the internal. Thank You.

[kostas_tsioubris]

I have tried like this and wrote the value in cell A3. Check that your code is correct and maybe post here some of your code

CALL METHOD OF application 'Cells' = cells

EXPORTING

#1 = 1

#2 = 3.

SET PROPERTY OF cells 'Value' = 'Kostas'.

[Former Member]

ok it is working. Perfect. the last problem now is that it filled only with the header line of the internal table. what about with the other lines of the itab. thank you for your time. big respect.

[kostas_tsioubris]

well done!

now it is up to you to manipulate the excel sheet the way you like it.

For example you can loop in your internal table and write it's contents in any cell you like.

e.g

data: lv_row type i.

loop at itab.

add 1 to lv_row.

CALL METHOD OF application 'Cells' = cells

EXPORTING

#1 = lv_row

#2 = 3.

SET PROPERTY OF cells 'Value' = itab-field1.

....

endloop.

.

[Former Member]

here is the solution of my problem.

CREATE OBJECT application 'excel.application'.

SET PROPERTY OF application 'visible' = 1.

CALL METHOD OF application 'Workbooks' = workbook.

*CALL METHOD OF workbook 'Add'.

CALL METHOD OF workbook 'Open'

exporting

#1 = 'C:\Temp\XXX0099_Support_20XX.xls'. "your excel file name here

*LOOP AT itab.

• Create second Excel sheet "Timesheets

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING #1 = 2.

SET PROPERTY OF sheet 'Name' = 'Timesheets'.

CALL METHOD OF sheet 'Activate'.

clear: w_index.

LOOP AT itab.

w_index = sy-tabix + 3.

• index = row_max * ( sy-tabix - 1 ) + 1. " 1 - column name

CALL METHOD OF application 'Cells' = cells

EXPORTING

#1 = w_index " line

#2 = 1. "col

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

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

*

SET PROPERTY OF cells 'Value' = itab-budat .

CALL METHOD OF application 'Cells' = cells

EXPORTING

#1 = w_index

#2 = 3.

SET PROPERTY OF cells 'Value' = itab-ltext .

ENDLOOP.

• Save excel speadsheet to particular filename

CALL METHOD OF sheet 'Save'

EXPORTING #1 = 'c:\temp\XXX0099_Support_20XX.xls'

"filename

#2 = 1.

"fileFormat

[Former Member]

what if i want to re-run the report?

i dont want the new index of the iternal to be overwritten in the excel but to be appended in empty lines