Excle sheet Upload based on tab

[Former Member]

Hello all,

I am having an excel sheet with four tabs, I need to upload only one tabe details of excle sheet.

Please let m eknow any function modules or classes avalible.

[Former Member]

Hello all,

I am having an excel sheet with four tabs, I need to upload only one tabe details of excle sheet.

Please let m eknow any function modules or classes avalible.

[former_member156446]

[uploading excel into intrenal table|http://www.sapfans.com/forums/viewtopic.php?p=119274&sid=29c97b82454126e05ea06b1ef646bfa6]

[Former Member]

Hi,

The code below works for sure.....

data itab type table of ALSMEX_TABLINE with header line.
data your_tab type table of <your_definition_of_table>.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                      = <filename>.xls
    i_begin_col                   = 1
    i_begin_row                   = 1
    i_end_col                     = 1
    i_end_row                     = 1000
  tables
    intern                        = itab
 EXCEPTIONS
   INCONSISTENT_PARAMETERS       = 1
   UPLOAD_OLE                    = 2
   OTHERS                        = 3
          .
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

loop at itab.
  work_area-filed = itab-value.
  append work_area to your_tab.
endloop.

regards,

Siddarth

[Former Member]

Hello All,

I need to read the excel sheet based on tab not the excle sheet with active tabe of excsheet.

[Former Member]

Hi,

use describe statement to get the number of fields in the internal table....

data w_type type c,
w_components type i.

describe field your_table type w_type components w_components.
"  this statement will give you number of fields in your internal table.

data itab type table of ALSMEX_TABLINE with header line.
data your_tab type table of <your_definition_of_table>.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                      = <filename>
    i_begin_col                   = 1
    i_begin_row                   = 1
    i_end_col                     = w_components
    i_end_row                     = 1000
  tables
    intern                        = itab
 EXCEPTIONS
   INCONSISTENT_PARAMETERS       = 1
   UPLOAD_OLE                    = 2
   OTHERS                        = 3
          .
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

loop at itab.
case itab-col.
when 1.
  work_area-field1 = itab-value.
when 2.
  work_area-field1 = itab-value.
...
...
when n.
  work_area-fieldn = itab-value.

endcase. 
at end of row.
  append work_area to your_tab.
endat.
endloop.

Is this what you meant by based on internal table....

if not please be more specific with your requirement.

Regards,

Siddarth

[Former Member]

Suppos i have one excel sheet. In one excel sheet i have three different tabe. That is three different sheets in one excel sheet.

Now i need to read only the second tab excle sheet. Then how is it possible any function module is there to read one tabe in excelsheet which contains three diffrent tabs.

[Former Member]

Ah!!! OK....

Take this code....

has been tested...

This is sample code.....

you can use this to check it first and then implement in your object.

data : $i_intern type  kcde_cells occurs 0 with header line.

data : $v_index type i.
data : $v_start_col type i value '1',
       $v_start_row type i value '1',
       $v_end_col   type i value '256',
       $v_end_row   type i value '7500'.

  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.

  enddo.

  call method of application 'QUIT'.
  m_message.

  free object : application,
                workbook,
                worksheet,
                sheets,
                range.
  m_message.

Regards,

Siddarth

[Former Member]

Thanks Siddharth Chordia,

The required functionality is working with the below code.

REPORT Z_DEMO_EXCEL_TAB.

TYPE-POOLs : KCDE, OLE2.

data : $i_intern type kcde_cells occurs 0 with header line.

data : $v_index type i.

data : $v_start_col type i value '1',

$v_start_row type i value '1',

$v_end_col type i value '256',

$v_end_row type i value '7500'.

data: excel_tab type kcde_sender.

data: wa_excel_tab type KCDE_SENDER_STRUC.

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.

data : sheetno TYPE i.

PARAMETER : pa_file TYPe string OBLIGATORY,

name(31) TYPE c OBLIGATORY.

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.

clear: $i_intern[], excel_tab[].

• Activate sheet number L_ACTIVE_SHEET with tab name

call method of application 'Worksheets' = worksheets EXPORTING #1 = name.

m_message.

call method of worksheets 'Activate'.

m_message.

• GET PROPERTY OF worksheets 'Name' = name.

• 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 NE 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.

call method of application 'QUIT'.

m_message.

free object : application,

workbook,

worksheet,

sheets,

range.

m_message.

LOOP At excel_tab into wa_excel_tab.

WRITE : wa_excel_tab-LINE.

ENDLOOp.