topic Re: Excel with ABAP in Application Development and Automation Discussions

Excel with ABAP

Former Member — Tue, 05 Dec 2006 08:55:14 GMT

My requirement is that i need to delete the first worksheet in an excel file using ABAP....

Please lend help?

Re: Excel with ABAP

Former Member — Tue, 05 Dec 2006 09:41:52 GMT

This will solve your problem,

REPORT zkb_delete_excel_sheet.

TYPE-POOLS ole2.

DATA: o_application TYPE ole2_object,

o_workbook TYPE ole2_object,

o_worksheet TYPE ole2_object,

o_sheet TYPE ole2_object.

PARAMETERS: p_excel TYPE rlgrap-filename DEFAULT 'C:\Soft\Book1.xls',

p_sheet(30) TYPE c DEFAULT 'Sheet2'.

START-OF-SELECTION.

CREATE OBJECT o_application 'Excel.Application'.

CALL METHOD OF o_application 'Workbooks' = o_workbook.

CALL METHOD OF o_workbook 'Open' EXPORTING #1 = p_excel.

CALL METHOD OF o_application 'WORKSHEETS' = o_sheet EXPORTING #1 = p_sheet.

CALL METHOD OF o_sheet 'DELETE'.

CALL METHOD OF o_workbook 'Save'.

CALL METHOD OF o_workbook 'Close'.

FREE: o_application, o_workbook, o_worksheet, o_sheet.

Regards

Kathirvel

Re: Excel with ABAP

Former Member — Tue, 05 Dec 2006 10:20:06 GMT

hi Kathirvel ,

firstly thanks for the reply

but call method of o_workbook 'close'.

gives a sy-subrc value of 2

do u know why this happens?

Re: Excel with ABAP

Former Member — Tue, 05 Dec 2006 10:29:05 GMT

Hi Raksha

Please comment that line, The method close might not be available I believe.

I will check the same and get back to you I find any thing.

You can find more information from here,

http://help.sap.com/saphelp_47x200/helpdata/en/db/9987b3c3cf11d194ad00a0c94260a5/frameset.htm

Please reward point if that helped you.

Regards

Kathirvel

Re: Excel with ABAP

Former Member — Tue, 05 Dec 2006 10:37:04 GMT

yep...ill surely reward points

but even after commenting the call method...

i get sy-subrc = 2 for the prev call method

so i am also not able to make out what the problem is

Re: Excel with ABAP

Former Member — Tue, 05 Dec 2006 10:39:41 GMT

Try this code,

The objects were not properly cleared :-(.

Now just after save just we free the objects. It is working for me jsut check and revert.

REPORT zkb_delete_excel_sheet.

TYPE-POOLS ole2.

DATA: o_application TYPE ole2_object,

o_workbook TYPE ole2_object,

o_worksheet TYPE ole2_object,

o_sheet TYPE ole2_object.

PARAMETERS: p_excel TYPE rlgrap-filename DEFAULT 'C:\Soft\Book1.xls',

p_sheet(30) TYPE c DEFAULT 'Sheet2'.

START-OF-SELECTION.

CREATE OBJECT o_application 'Excel.Application'.

CALL METHOD OF o_application 'Workbooks' = o_workbook.

CALL METHOD OF o_workbook 'Open' EXPORTING #1 = p_excel.

CALL METHOD OF o_application 'WORKSHEETS' = o_sheet EXPORTING #1 = p_sheet.

CALL METHOD OF o_sheet 'DELETE'.

CALL METHOD OF o_workbook 'Save'.

CALL METHOD OF o_application 'Save'.

FREE OBJECT: o_application, o_workbook, o_worksheet, o_sheet.

Regards

Kathirvel

Re: Excel with ABAP

Former Member — Tue, 05 Dec 2006 10:56:59 GMT

nope...its not wrking here

it still gives sy-subrc 2 for the free statement

Re: Excel with ABAP

Former Member — Tue, 05 Dec 2006 11:20:02 GMT

Yes u r right I still get the sy-subrc as 2.

Replace the last line with these as this is the way the excel ole app needs to be closed,

CALL METHOD OF o_application 'QUIT'.

FREE OBJECT o_sheet.

FREE OBJECT o_worksheet.

FREE OBJECT o_workbook.

FREE OBJECT o_application.

But still you will get sy-subrc as 2. I am not able to figure out the reason.

But it is deleting the worksheet correctly which is your main problem I believe.

If you could get it just update this thread...

Regards

Kathirvel

Re: Excel with ABAP

Former Member — Tue, 05 Dec 2006 13:12:33 GMT

hi...

Actually what is happening is tht when i create an object o_application

there are five fields in it.... the header is getting populated with -1 and the clsid, CB_INDEX are not getting populated.

can u tell me what these fields are getting populated with when u run ur code.

and the sheet is not getting deleted here

Re: Excel with ABAP

Former Member — Wed, 06 Dec 2006 09:12:02 GMT

Here are the values of my o_application object,

HEADER - OBJH

TYPE - OLE2

HANDLE - 1-

CB_INDEX -

CLSID -

One reason is that the sheet might be open already.

Regards

Kathirvel

Re: Excel with ABAP

Former Member — Wed, 06 Dec 2006 09:21:14 GMT

hi Kathirvel,

even me getting same values....

its wrking on another server....

but the problem is that ,its not automated... it throws a message on the excel sheet saying do u want to delete sheet.... and it doesnt get saved automatically

what is req is tht it does the deletion of the sheet automatically... and saves it

i dunno hw to do this...

THANKYOU SO MUCH FOR UR HELP