topic Re: excel in Application Development and Automation Discussions

excel

Former Member — Fri, 18 May 2007 09:01:34 GMT

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.

Re: excel

Former Member — Fri, 18 May 2007 09:07:39 GMT

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

Re: excel

Former Member — Fri, 18 May 2007 09:08:36 GMT

Use the fun module

ALSM_EXCEL_TO_INTERNAL_TABLE

Reward points if useful

Regards

Anji

Re: excel

Former Member — Fri, 18 May 2007 09:19:58 GMT

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.

Re: excel

Former Member — Fri, 18 May 2007 09:23:26 GMT

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

Re: excel

Former Member — Fri, 18 May 2007 09:29:26 GMT

the alsm excel to iternal is exactly the opposite.

Message was edited by:

antonis nezos

Re: excel

Former Member — Fri, 18 May 2007 09:51:26 GMT

finally we didnt find any solution....

Re: excel

kostas_tsioubris — Fri, 18 May 2007 10:02:11 GMT

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

Re: excel

Former Member — Fri, 18 May 2007 10:06:39 GMT

se euxaristw therma!

tha to dokimasw se ligo kai elpizw na doulepsei

Re: excel

kostas_tsioubris — Fri, 18 May 2007 10:11:28 GMT

krata me enhmero

Re: excel

Former Member — Fri, 18 May 2007 10:47:46 GMT

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

Re: excel

kostas_tsioubris — Fri, 18 May 2007 11:41:00 GMT

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

Re: excel

Former Member — Fri, 18 May 2007 12:28:36 GMT

it seems to be working only for the line index.

Re: excel

kostas_tsioubris — Fri, 18 May 2007 12:32:31 GMT

Try like this

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

EXPORTING

#1 = '1' "line index

#2 = '1'. "column index

Re: excel

Former Member — Fri, 18 May 2007 12:51:20 GMT

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.

Re: excel

kostas_tsioubris — Fri, 18 May 2007 13:05:20 GMT

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

Re: excel

Former Member — Fri, 18 May 2007 13:28:47 GMT

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.

Re: excel

kostas_tsioubris — Fri, 18 May 2007 13:33:45 GMT

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.

Re: excel

Former Member — Fri, 18 May 2007 13:36:56 GMT

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

Re: excel

Former Member — Mon, 21 May 2007 10:13:28 GMT

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