Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

OLE - multiple sheets not working?

former_member201275
Active Contributor
0 Kudos

Hi,

I have the following code (below) which should create an excel file containing 3 sheets. It, however, only creates 1 sheet. Any ideas as to why the second and third sheet are not created?

REPORT ZWBEXCEL.

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 & u201D u201D & Sheet2!A1' TO itab3,

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

*'=Sheet1!A2 & u201D u201D & 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

4 REPLIES 4

Former Member
0 Kudos

Hi Twin,

I used your code and for me it created 3 sheets with data in the first 2 sheet perfectly fine.

The code is working OK

Regards

Rajvansh

former_member201275
Active Contributor
0 Kudos

Ok.... weird.

I wonder if there are some setting changes I need to make in Excel itself?

former_member201275
Active Contributor
0 Kudos

I managed to fix the issue.

In excel I go to Tools->Options, then under General tab I set 'Sheets in new workbook' to 3.

Is there any way of controlling this when opening the file from Sap, i.e. I may need to create 7 or 8 sheets. Does this mean I have to go and change the setting in excel each time?

former_member201275
Active Contributor
0 Kudos

solved:

SET PROPERTY OF application 'SheetsInNewWorkbook' = 7.