10-12-2007 9:41 AM
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.
01-07-2008 5:37 AM
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
01-07-2008 5:46 AM
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.