09-29-2008 12:14 PM
hello,
Is it possible to upload multiple worksheets present in a single excel sheet into internal table.
Regards,
09-29-2008 12:19 PM
09-29-2008 12:17 PM
09-29-2008 12:19 PM
09-29-2008 12:52 PM
09-29-2008 1:18 PM
Function kcd_excel_ole_to_int_convert lets you read 1 (active) worksheet from an excel sheet.
I copied the source from this function into a FORM routine and made some adjustments to read all available worksheets.
1. first you need to determine the number of sheets
2. activate a sheet
3. import the contents from the activated sheet.
form kcd_excel_ole_to_int_convert .
data: excel_tab type kcde_sender.
data: separator type c.
field-symbols: <field>.
data: application type ole2_object,
workbook type ole2_object,
range type ole2_object,
worksheet type ole2_object,
worksheets type ole2_object,
sheets type ole2_object.
data: h_cell type ole2_object.
data: h_cell1 type ole2_object.
data: l_sheet type c length 40.
data: l_active_sheet type i.
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.
* Create Excel OLE2 object and open XLS 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 workbook 'Open' exporting #1 = pa_file.
m_message.
* Show/don't show XLS
* set property of application 'Visible' = 1.
* m_message.
* Determine number of sheets
call method of application 'Sheets' = sheets.
m_message.
call method of sheets 'Count' = sheetno.
m_message.
l_active_sheet = 0.
do sheetno times.
clear: $i_intern[], excel_tab[].
clear: it_vals[], it_chars[].
l_active_sheet = l_active_sheet + 1.
* Activate sheet number L_ACTIVE_SHEET
call method of application 'Worksheets' = worksheets exporting #1 = l_active_sheet.
m_message.
call method of worksheets 'Activate'.
m_message.
* Get active sheet
get property of application 'ACTIVESHEET' = worksheet.
m_message.
* Find start
call method of worksheet 'Cells' = h_cell
exporting #1 = $v_start_row #2 = $v_start_col.
m_message.
* Find end
call method of worksheet 'Cells' = h_cell1
exporting #1 = $v_end_row #2 = $v_end_col.
m_message.
* Create range
call method of worksheet 'RANGE' = range
exporting #1 = h_cell #2 = h_cell1.
m_message.
* Select range
call method of range 'SELECT'.
m_message.
* copy to Clipboard
call method of range 'COPY'.
m_message.
call function 'CONTROL_FLUSH'
exceptions
others = 3.
* Import clipboard
call function 'CLPB_IMPORT'
tables
data_tab = excel_tab
exceptions
clpb_error = 1
others = 2.
if sy-subrc <> 0. message x001(kx). endif.
separator = cl_abap_char_utilities=>horizontal_tab.
perform separated_to_intern_convert(saplkcde) tables excel_tab $i_intern
using separator.
set property of application 'CutCopyMode' = 0.
m_message.
* DO YOUR STUFF ON THE IMPORTED SHEET DATA HERE!
enddo.
call method of application 'QUIT'.
m_message.
free object : application,
workbook,
worksheet,
sheets,
range.
m_message.
endform. " KCD_EXCEL_OLE_TO_INT_CONVERT