cancel
Showing results for 
Search instead for 
Did you mean: 

Excel template and Ole Technologie

Former Member
0 Kudos

Hi all,

What I want to do is using an Excel Template stored in BDN, open the template and fill it with data from an internal table.

How I can address the document and open it I already find out...and I also know how to pass data to the template using Ole technoloy...what I need to know is, what I have to do in the Excel Template, in order to be able to pass data from R/3 to Excel template?

Some kind of variable in cells etc....

Or is there a way to pass data only by telling the ABAP report in which cells he should put which data?

I hope I could explain myself.

Thanks in advanced.

Marcelo Moreira

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Give this a look.

REPORT MULTIEXCEL .

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.

Answers (2)

Answers (2)

vinod_gunaware2
Active Contributor
0 Kudos

hi

<b>EXCEL_OLE_STANDARD_DAT Just</b> calls MS_EXCEL_OLE_STANDARD_DAT

<b>MS_EXCEL_OLE_STANDARD_DAT</b> Downloads internal table and opens it in MS Excel.

<b>KCD_EXCEL_OLE_TO_INT_CONVERT</b> Uploads an *.xls file to internal table (max cell length = 32). Example. This function uses a range selection and copy-paste technique, therefore it quite fast.Q

There is another Example 2 that reads data from Excel file cell by cell.

<b>ALSM_EXCEL_TO_INTERNAL_TABLE</b> the same as <b>KCD_EXCEL_OLE_TO_INT_CONVERT but max cell length = 50

FTBU_START_EXCEL</b> just [download internal table to file and] start Excel (w/o OLE). See also WS_EXCEL

regards

vinod

former_member188685
Active Contributor
0 Kudos

Hi,

Check this link..

http://www.sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm

read this BLog.

/people/sap.user72/blog/2006/02/07/downloading-data-into-excel-with-format-options

Regards

Vijay