• Login
• Become a member
• RSS
• Part of the TechTarget network

This tip is a developable example of how to open an Excel file, find and activate a sheet, read in a rectangular area from it and write out a diagnostics. To run the program, prepare Excel test file 'c:temptest_ole.xls ' with the second sheet named 'Test' and add some data in its first two columns up to 11th row. (Written in version 4.6C, NT.)

REPORT ZVVTST_OLE .
INCLUDE OLE2INCL.
type-pools kcde.
Parameters: w_book  like RLGRAP-FILENAME obligatory lower case
                                   default 'c:temptest_ole.xls',
            w_sheet(50)                  obligatory lower case
                                   default 'Test'.
Select-options:
  rows for sy-index default 2 to 11 obligatory NO-EXTENSION,
  cols for sy-index default 1 to  2 obligatory NO-EXTENSION.
Parameters: visible  as checkbox default 'X',
            out_diag as checkbox default 'X'.

DATA:  EXCEL     TYPE OLE2_OBJECT,
       WORKBOOK  TYPE OLE2_OBJECT,
       SHEET     TYPE OLE2_OBJECT,
       RANGE     TYPE OLE2_OBJECT,
       CELL1     TYPE OLE2_OBJECT,
       CELL2     TYPE OLE2_OBJECT,
     ret_code,
     EXCEL_TAB TYPE KCDE_SENDER.

at selection-screen on value-request for w_book.
  perform f4_filename changing w_book.

at selection-screen output.
  import w_sheet from memory id 'SH'.  free memory id 'SH'.
  import w_book  from memory id 'WB'.  free memory id 'WB'.

start-of-selection.
  perform workbook.
  perform open_workbook using w_book changing  ret_code.
    if   not ret_code

 is initial.
      concatenate w_book '- NOT FOUND' into w_book separated by space.
      export w_book to memory id 'WB'.
      perform quit_excel.
      exit.
    endif.
  perform open_sheet    using w_sheet changing ret_code.
    if   not ret_code is initial.
      concatenate w_sheet '- NOT FOUND' into w_sheet separated by space.
      export w_sheet to memory id 'SH'.
    endif.
  perform swallow_range  changing ret_code.
  perform spit_out_diag  changing ret_code.
  perform quit_excel.
    if   not ret_code is initial.
      concatenate w_sheet '- SOME ERR' into w_sheet separated by space.
      export w_sheet to memory id 'SH'.
    endif.
*----------------------------------------------------------------------*
FORM open_sheet USING  value(P_SH)
             changing  p_ret_code.
data sheet_name(51).
  concatenate '#' P_SH into sheet_name.
  while sheet_name <> p_sh.
    CALL METHOD OF EXCEL 'Worksheets' = SHEET  EXPORTING #1 = sy-index.
    if   sy-subrc <> 0.
      move 'X' to p_ret_code.
      exit.
    endif.
    get PROPERTY OF SHEET 'Name' = sheet_name.
  endwhile.
  if    p_ret_code is initial.
    CALL METHOD OF SHEET  'Activate'.
  endif.
ENDFORM.                    " open_sheet
*----------------------------------------------------------------------*
FORM open_workbook USING value(P_WB) changing  p_ret_code.
  CALL METHOD  OF workbook 'Open' EXPORTING #1 = p_wb.
  if   sy-subrc <> 0.
    move 'X' to p_ret_code.
  endif.
ENDFORM.                    " open_workbook
*----------------------------------------------------------------------*
FORM workbook.
  CREATE OBJECT EXCEL 'excel.application'.
  if   visible = 'X'.
    SET PROPERTY OF EXCEL 'visible' = 1.
  endif.
  CALL METHOD OF EXCEL 'Workbooks' = workbook.
*  CALL METHOD OF workbook 'Add'.
ENDFORM.                    " workbook
*----------------------------------------------------------------------*
FORM swallow_range CHANGING P_RET_CODE.
constants:  delimiter    type x value 9.
data:       cells type string occurs 0 with header line,
            n_of_rows type i.
field-symbols: <ex_fs> like line of excel_tab.

 CALL METHOD OF sheet 'Cells' = CELL1
                            EXPORTING #1 = rows-low  #2 = cols-low.
 CALL METHOD OF sheet 'Cells' = CELL2
                            EXPORTING #1 = rows-high #2 = cols-high.
 CALL METHOD OF sheet 'RANGE' = RANGE EXPORTING #1 = CELL1 #2 = CELL2.
 CALL METHOD OF RANGE 'SELECT'.
 CALL METHOD OF RANGE 'COPY'.
 CALL FUNCTION 'CONTROL_FLUSH'  EXCEPTIONS    OTHERS = 3.
 CALL FUNCTION 'CLPB_IMPORT'    TABLES      DATA_TAB   = EXCEL_TAB
                                EXCEPTIONS  CLPB_ERROR = 1
                                OTHERS     = 2.
  n_of_rows = rows-high - rows-low + 1.
  loop at EXCEL_TAB assigning <ex_fs>.
    if    sy-tabix > n_of_rows. " CLPB_IMPORT returns one extra line
      delete excel_tab.
      continue.
    endif.
    split <ex_fs> at delimiter into table cells.
    new-line.
    loop at cells.
      write: cells.
    endloop.
    Move 'Data OK.' to <ex_fs>. " or some other diagnostics
  endloop.
ENDFORM.                    " swallow_range
*----------------------------------------------------------------------*
FORM quit_excel.
  if     not ret_code is initial.
    SET PROPERTY OF EXCEL 'DisplayAlerts' = 0. " to be asked about save
  endif.
  CALL METHOD  OF workbook 'Close'.
  CALL METHOD  OF EXCEL 'Quit'.
  FREE OBJECT EXCEL.
ENDFORM.                    " quit_excel
*----------------------------------------------------------------------*
FORM spit_out_diag CHANGING P_RET_CODE.
 check     P_RET_CODE  is initial.
 check not out_diag    is initial.
 add 1 to cols-high. " place diagnostics into column next to inp. range
 CALL METHOD OF SHEET 'Cells' = CELL1
                            EXPORTING #1 = rows-low  #2 = cols-high.
 CALL METHOD OF SHEET 'Cells' = CELL2
                            EXPORTING #1 = rows-high #2 = cols-high.
  CALL METHOD OF SHEET 'RANGE' = RANGE EXPORTING #1 = CELL1 #2 = CELL2.
  CALL METHOD OF RANGE 'SELECT'.
  CALL FUNCTION 'CLPB_EXPORT'   TABLES      DATA_TAB   = EXCEL_TAB
                                EXCEPTIONS  CLPB_ERROR = 1
                                            OTHERS     = 2.
  CALL FUNCTION 'CONTROL_FLUSH' EXCEPTIONS  OTHERS = 3.
  CALL METHOD OF SHEET 'PASTE'.
ENDFORM.                    " spit_out_diag
*----------------------------------------------------------------------*
FORM f4_filename CHANGING P_FILENAME.
data repid type sy-repid.
  move sy-repid to repid.
  call function 'F4_FILENAME'
    exporting  program_name  = repid
               dynpro_number = sy-dynnr
               feld_name     = 'PATH'
    importing  file_name     = P_FILENAME.
ENDFORM.                    " f4_filename

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

All Rights Reserved,Copyright 2000 - 2013, TechTarget