Excel File Upload

[Former Member]

Hi all,

I have an Excel file to upload. In that Excel 3 sheets are there, i want to Upload data only from the <b>2nd sheet</b>.

How to do this?

[Former Member]

Hi

use the fun module

ALSM_EXCEL_TO_INTERNAL_TABLE

and give that Excel file name

Reward if useful

regards

Anji

[Former Member]

I want to upload the Data only from the <b>SECOND sheet</b>

[former_member189059]

Hi Gopi,

This is the code to upload data from more than one sheet

I guess you could just comment the part for the first sheet to meet your requirement

*&---------------------------------------------------------------------*
*& 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.

[Former Member]

hi,

Function module: ALSM_EXCEL_TO_INTERNAL_TABLE

This FM only catch data which sheet is active. That means only one sheet will be read by this function module.

For your requirement, I suggest that you need write coding manually to upload your excel file.

key statement: CREATE OBJECT

SET PROPERTY OF obj p = f.

GET PROPERTY OF obj p = f.

CALL METHOD OF obj m.

FREE OBJECT

Actually, This is same concept as function moduleALSM_EXCEL_TO_INTERNAL_TABLE.(They are all belong to OLE programming)

But you can customize your OLE coding in your program.

you can use the functionality of Macro Recording in EXCEL to generate OLE coding(This seems like BDC recording in SAP).

Hope this will help you.