READING DATA FORM MULTIPLE WORKSHEET OF A EXCEL FILE.

[Former Member]

HI ALL,

i have a excel file . i have data into worksheets of that excel file .

how i read the data from 2nd worksheet third worksheet and so on.

please help me.

thank you.

[Former Member]

hI ALL PLZ HELP ME IN THIS QUERY .

I WANT TO PUT THE DATA INTO DIFF INTERNAL TABLES .

ALSM_EXCEL_TO_INTERNAL_TABLE. WILL NOT WORK BCOZ IT ONLY READ ONLY ONE WORKSHEET

[former_member189059]

Hi

use this code

you will have to enter the range to be copied in each sheet

&----

---

*& Report ZKRIS_EXCELUPLOAD_2SHEETS

*&

&----

---

*&

*&

&----

---

REPORT zkris_excelupload_2sheets.

• value of excel-cell

TYPES: ty_d_itabvalue TYPE alsmex_tabline-value,

• internal table containing the excel data

ty_t_itab TYPE alsmex_tabline OCCURS 0,

• line type of sender table

BEGIN OF ty_s_senderline,

line(4096) TYPE c,

END OF ty_s_senderline,

• sender table

ty_t_sender TYPE ty_s_senderline OCCURS 0.

*

CONSTANTS: gc_esc VALUE '"'.

INCLUDE lalsmexf01.

TYPE-POOLS ole2.

START-OF-SELECTION.

PARAMETERS: filename LIKE rlgrap-filename.

PARAMETERS: st_rw_s1 TYPE i.

PARAMETERS: st_cl_s1 TYPE i.

PARAMETERS: st_rw_s2 TYPE i.

PARAMETERS: st_cl_s2 TYPE i.

PARAMETERS: ed_rw_s1 TYPE i.

PARAMETERS: ed_cl_s1 TYPE i.

PARAMETERS: ed_rw_s2 TYPE i.

PARAMETERS: ed_cl_s2 TYPE i.

DATA: it_data1 TYPE ty_t_itab.

DATA: it_data2 TYPE ty_t_itab.

DATA: it_data_wa LIKE LINE OF it_data1.

• DATA DECLARATION

DATA: excel_tab TYPE ty_t_sender,

excel_tab1 TYPE ty_t_sender.

DATA: ld_separator TYPE c.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

sheet TYPE ole2_object,

range TYPE ole2_object,

worksheet TYPE ole2_object.

DATA: h_cell TYPE ole2_object,

h_cell1 TYPE ole2_object.

DATA: ld_rc TYPE i.

• MESSAGE DEFINATION

DEFINE m_message.

case sy-subrc.

when 0.

when 1.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

when others. raise upload_ole.

endcase.

END-OF-DEFINITION.

• PARAMETER CHECK

IF st_rw_s1 > ed_rw_s1.

RAISE inconsistent_parameters.

ENDIF.

IF st_cl_s1 > ed_cl_s1.

RAISE inconsistent_parameters.

ENDIF.

IF st_rw_s2 > ed_rw_s2.

RAISE inconsistent_parameters.

ENDIF.

IF st_cl_s2 > ed_cl_s2.

RAISE inconsistent_parameters.

ENDIF.

CLASS cl_abap_char_utilities DEFINITION LOAD.

ld_separator = cl_abap_char_utilities=>horizontal_tab.

• OPENING EXCEL FILE

IF application-header = space OR application-handle = -1.

CREATE OBJECT application 'Excel.Application'.

m_message.

ENDIF.

CALL METHOD OF application 'Workbooks' = workbook.

m_message.

CALL METHOD OF application 'Workbooks' = workbook.

m_message.

CALL METHOD OF workbook 'Open'

EXPORTING

#1 = filename.

m_message.

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 1.

m_message.

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 1.

m_message.

CALL METHOD OF sheet 'Activate'.

m_message.

GET PROPERTY OF application 'ACTIVESHEET' = sheet.

m_message.

• MARKING OF WHOLE SPREADSHEET

CALL METHOD OF sheet 'Cells' = h_cell

EXPORTING

#1 = st_rw_s1

#2 = st_cl_s1.

m_message.

CALL METHOD OF sheet 'Cells' = h_cell1

EXPORTING

#1 = ed_rw_s1

#2 = ed_cl_s1.

m_message.

CALL METHOD OF sheet 'RANGE' = range

EXPORTING

#1 = h_cell

#2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

• Copy marked area (SHEET1) into Clippboard

CALL METHOD OF range 'COPY'.

m_message.

• Read clipboard into ABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

IMPORTING

data = excel_tab

EXCEPTIONS

cntl_error = 1

• ERROR_NO_GUI = 2

• NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

IF sy-subrc <> 0.

MESSAGE a037(alsmex).

ENDIF.

PERFORM separated_to_intern_convert TABLES excel_tab it_data1

USING ld_separator.

• Clear the clipboard

REFRESH excel_tab.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = excel_tab

CHANGING

rc = ld_rc

EXCEPTIONS

cntl_error = 1

• ERROR_NO_GUI = 2

• NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

• Working in Second Excel Work Sheet

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 2.

m_message.

CALL METHOD OF sheet 'Activate'.

m_message.

GET PROPERTY OF application 'ACTIVESHEET' = sheet.

m_message.

• Mark Sheet2

CALL METHOD OF sheet 'Cells' = h_cell

EXPORTING

#1 = st_rw_s2

#2 = st_cl_s2.

m_message.

CALL METHOD OF sheet 'Cells' = h_cell1

EXPORTING

#1 = ed_rw_s2

#2 = ed_cl_s2.

m_message.

CALL METHOD OF sheet 'RANGE' = range

EXPORTING

#1 = h_cell

#2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

• Copy Marked Area (Sheet2) into Clippboard

CALL METHOD OF range 'COPY'.

m_message.

• Read Clipboard into ABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

IMPORTING

data = excel_tab1

EXCEPTIONS

cntl_error = 1

• ERROR_NO_GUI = 2

• NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

IF sy-subrc <> 0.

MESSAGE a037(alsmex).

ENDIF.

PERFORM separated_to_intern_convert TABLES excel_tab1 it_data2

USING ld_separator.

• Clear Clipboard

REFRESH excel_tab.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = excel_tab1

CHANGING

rc = ld_rc

EXCEPTIONS

cntl_error = 1

• ERROR_NO_GUI = 2

• NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

• Leaving Application

.

CALL METHOD OF application 'QUIT'.

m_message.

FREE OBJECT application.

m_message.

LOOP AT it_data1 INTO it_data_wa.

WRITE:/ it_data_wa.

ENDLOOP.

SKIP 3.

LOOP AT it_data2 INTO it_data_wa.

WRITE:/ it_data_wa.

ENDLOOP.