topic excel multiple sheet in Application Development and Automation Discussions

excel multiple sheet

Former Member — Wed, 05 Jun 2013 10:02:45 GMT

Hi,

I am having a problem with creating new sheet in excel via abap. My values in the 1st sheet is getting overwritten. I have used the following code..

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'. "type of application

PERFORM CHECK_ERRORS.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

PERFORM CHECK_ERRORS.

CALL METHOD OF H_EXCEL 'Workbooks' = WORKBOOKS.

PERFORM CHECK_ERRORS.

CALL METHOD OF WORKBOOKS 'Add'." = THIS_WORKBOOK.

set PROPERTY OF h_excel 'SheetsInNewWorkbook' = 3.

CALL METHOD OF h_excel 'worksheets' = osheet EXPORTING #1 = 1.

set PROPERTY OF osheet 'Name' = 'BUF1'.

call method of osheet 'Activate'.

GET PROPERTY OF h_excel 'Activesheet' = osheet.

"and for creating the new sheet..

CALL METHOD OF h_excel 'worksheets' = osheet EXPORTING #1 = 2.

CALL METHOD OF osheet 'Add'." = osheet1.

set PROPERTY OF osheet 'Name' = 'BUF2'.

call method of osheet 'Activate'.

GET PROPERTY OF h_excel 'Activesheet' = osheet.

Can anyone please help me.. Its urgent.

Re: excel multiple sheet

Former Member — Wed, 05 Jun 2013 11:22:36 GMT

Try below code..

* start Excel
       IF h_excel-header = space OR h_excel-handle = -1.
         CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
       ENDIF.
*--- get list of workbooks, initially empty
       CALL METHOD OF
           h_excel
           'Workbooks' = h_mapl.
       SET PROPERTY OF h_excel 'Visible'       = 1.
       CALL METHOD OF
           h_mapl
           'Add' = h_map.

       PERFORM create_sheet TABLES gt_0105
                            USING 'Communication Details' h_sheet9.

       PERFORM create_sheet TABLES gt_23
                            USING 'Previous Employer Details' h_sheet8.

       PERFORM create_sheet TABLES gt_22
                            USING 'Education Details' h_sheet7.
**********************************************************************************

FORM create_sheet TABLES   it_sheet TYPE ty_data
                    USING    iv_name
                             iv_sheet TYPE ole2_object.

   DATA l_rc TYPE i.

   gv_sheet_name = iv_name.
   GET PROPERTY OF h_excel 'Sheets' = iv_sheet .
   CALL METHOD OF
       iv_sheet
       'Add'    = h_map.
   SET PROPERTY OF h_map 'Name' = gv_sheet_name .
   GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.

   CALL METHOD cl_gui_frontend_services=>clipboard_export
     IMPORTING
       data                 = it_sheet[]
     CHANGING
       rc                   = l_rc
     EXCEPTIONS
       cntl_error           = 1
       error_no_gui         = 2
       not_supported_by_gui = 3
       OTHERS               = 4.

   CALL METHOD OF
       h_excel
       'Cells' = w_cell1
     EXPORTING
       #1      = 1
       #2      = 1.
   CALL METHOD OF
       h_excel
       'Cells' = w_cell2
     EXPORTING
       #1      = 1
       #2      = 1.
   CALL METHOD OF
       h_excel
       'Range' = range
     EXPORTING
       #1      = w_cell1
       #2      = w_cell2.
   CALL METHOD OF
       range
       'Select'.
   CALL METHOD OF
       worksheet
       'Paste'.

ENDFORM.                    " CREATE_SHEET

*******************************************************************

Re: excel multiple sheet

Former Member — Wed, 05 Jun 2013 11:42:29 GMT

Hi Sharbani,

Replace the below code with above code .

For Sheet # 1

CALL METHOD OF h_excel 'worksheets' = osheet

EXPORTING #1 = 1.

call method of osheet 'Activate'.

set PROPERTY OF osheet 'Name' = 'BUF1'.

GET PROPERTY OF h_excel 'Activesheet' = osheet.

For Sheet # 2

CALL METHOD OF h_excel 'worksheets' = osheet

EXPORTING #1 = 2.

call method of osheet 'Activate'.

set PROPERTY OF osheet 'Name' = 'BUF2'.

GET PROPERTY OF h_excel 'Activesheet' = osheet.

Regard's

Smruti