create new worksheet in excel

[Former Member]

Hi experts,

I am assigning the records in excel through program if records exceeds more than 65000 i am moving remaining records to nect worksheet in same excel.By default i am getting 3 worksheets if we want to add new(4th) worksheet in same excel i am unable to create that.Can anybody suggest me to creat it.

Thanks in advance

Murali

[Former Member]

Hi Murali,

1. As u said u are working with excel,

u must be comfortable/acquainted

working with OLE thru abap code.

2. Try this code (just copy paste)

This code ADDs a sheet.

There are other things also like count

number of sheets etc

which u will find useful.

CALL METHOD OF sheets 'Add'.

The above line adds the actual sheet.

3.

REPORT abc .

*----

---

Data

TYPE-POOLS ole2.

DATA excel TYPE ole2_object.

DATA workbook TYPE ole2_object.

DATA aw TYPE ole2_object. "-- active workbook

DATA sheets TYPE ole2_object.

DATA sheet TYPE ole2_object.

DATA cnt TYPE i.

DATA nm(250) TYPE c.

DATA : filename(100) TYPE c.

DATA : sheetname(100) TYPE c.

START-OF-SELECTION.

*----

---

Variables

filename = 'C:\MAT.XLS'.

sheetname = 'Sheet5'.

*----

---

Open OLE

CREATE OBJECT excel 'Excel.Application' .

SET PROPERTY OF excel 'Visible' = 1.

CALL METHOD OF excel 'Workbooks' = workbook.

CALL METHOD OF workbook 'Open'

EXPORTING

#1 = filename.

CALL METHOD OF excel 'ActiveWorkbook' = aw.

CALL METHOD OF aw 'Sheets' = sheet

EXPORTING

#1 = sheetname.

CALL METHOD OF aw 'Sheets' = sheets.

GET PROPERTY OF sheets 'Count' = cnt.

*----

---

CALL METHOD OF sheets 'Add'.

CALL METHOD OF sheets 'Add'.

CALL METHOD OF sheets 'Add'.

GET PROPERTY OF sheet 'Name' = nm.

CALL METHOD OF sheet 'Activate'.

*----

---

Display for checking purpose

WRITE 😕 cnt , nm.

*----

---

Done

I hope it helps.

Regards,

Amit M.

[Former Member]

Hi Murali,

You can use OLE to achieve this:

CALL METHOD spreadsheet->select_sheet

EXPORTING name = name

no_flush = no_flush

IMPORTING error = error

retcode = retcode.

Or check this URL

Hope it helps...

Lokesh

Pls. reward appropriate points

[Former Member]

Amit mittal,

In my code by default excel is having 3 worksheets,

after data getting updated in these excels new worksheets are adding but every new excels are adding in third position and old one(which is third position)is moving to next position.Every time the same(last) sheet only over writing with new data.

EX: A,B,C, are the 3 excels.After getting updated all 3 sheets,new excel D is adding in 3rd("C" place) and "C" is moving to 4th place and "C" data is over written with new data.

So how to move new excel in last position every time.

Thanks in Advance.

[Former Member]

hi

try this

CREATE OBJECT h_excel 'EXCEL.APPLICATION'.

CALL METHOD OF h_excel 'Workbooks' = h_mapl.

• Add a new worksheet

CALL METHOD OF h_mapl 'Add' = h_map.

GET PROPERTY OF h_excel 'ACTIVESHEET' = h_sheet.

SET PROPERTY OF h_sheet 'NAME' = 'Color Test'.

• Position to active cell (A1)

CALL METHOD OF h_excel 'Cells' = h_zl EXPORTING #1 = 1 #2 = 1.

• Fill the cell with something

SET PROPERTY OF h_zl 'Value' = 'Content A1'.

regs

Arun