OLE - multiple sheets not working?

former_member201275 — Mon, 01 Dec 2008 08:52:19 GMT

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

Re: OLE - multiple sheets not working?

Former Member — Mon, 01 Dec 2008 09:15:30 GMT

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

Re: OLE - multiple sheets not working?

former_member201275 — Mon, 01 Dec 2008 09:43:42 GMT

Ok.... weird.

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

Re: OLE - multiple sheets not working?

former_member201275 — Mon, 01 Dec 2008 09:59:35 GMT

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?

Re: OLE - multiple sheets not working?

former_member201275 — Mon, 01 Dec 2008 10:02:38 GMT

solved:

SET PROPERTY OF application 'SheetsInNewWorkbook' = 7.

topic Re: OLE - multiple sheets not working? in Application Development and Automation Discussions

OLE - multiple sheets not working?

Re: OLE - multiple sheets not working?

Re: OLE - multiple sheets not working?

Re: OLE - multiple sheets not working?

Re: OLE - multiple sheets not working?