Bluesky
- SAP Managed Tags
hi
OLE is object link enabling. it is ued to handle objects like excel, word etc through abap.
check this sample program
&----
*& Report Z_PR_EXCEL_WITH_OLE
*&
&----
*&
*&
&----
REPORT z_pr_excel_with_ole.
To handle OLE objects
INCLUDE ole2incl.
TYPE-POOLS kcde.
TYPES: BEGIN OF ty_table,
LINE LIKE SY-TABIX,
COLN TYPE I,
name(1024) TYPE c,
END OF ty_table.
DATA: itab TYPE STANDARD TABLE OF ty_table,
wa_itab TYPE ty_table.
Data Declaration
DATA: excel TYPE ole2_object, " Excel object
workbook TYPE ole2_object,
sheet TYPE ole2_object,
cell TYPE ole2_object,
directory TYPE string,
filetable TYPE STANDARD TABLE OF file_info,
count TYPE i,
wa_filetable LIKE file_info,
v_len TYPE i,
v_extn TYPE string,
v_filename TYPE rlgrap-filename,
intern TYPE kcde_sender,
wa_intern LIKE LINE OF intern,
v_date TYPE dats,
week LIKE scal-week,
monday LIKE sy-datum,
sunday LIKE sy-datum,
cell_value(132) TYPE c.
DATA : excel_line LIKE sy-index,
excel_coln LIKE sy-index.
Selection Screen
PARAMETERS : p_dir TYPE string OBLIGATORY.
To read no. and type of files in a directory
CALL METHOD cl_gui_frontend_services=>directory_list_files
EXPORTING
directory = p_dir
filter = '.'
FILES_ONLY = FILES_ONLY
DIRECTORIES_ONLY = DIRECTORIES_ONLY
CHANGING
file_table = filetable
count = count
EXCEPTIONS
cntl_error = 1
directory_list_files_failed = 2
wrong_parameter = 3
error_no_gui = 4
not_supported_by_gui = 5
OTHERS = 6 .
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
Check whether Excel sheet is present or not
If Present, Process it
LOOP AT filetable INTO wa_filetable.
v_len = STRLEN( wa_filetable-filename ) - 3.
v_extn = wa_filetable-filename+v_len(3).
IF v_extn EQ 'XLS' OR v_extn EQ 'xls'.
CONCATENATE p_dir wa_filetable-filename INTO v_filename SEPARATED BY '\'.
CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
EXPORTING
filename = v_filename
i_begin_col = 1
i_begin_row = 1
i_end_col = 11
i_end_row = 58
TABLES
intern = intern
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.
start Excel
CREATE OBJECT excel 'EXCEL.APPLICATION'.
SET PROPERTY OF excel 'VISIBLE' = 1 .
CALL METHOD OF excel 'WORKBOOKS' = workbook.
CALL METHOD OF workbook 'OPEN' EXPORTING #1 = v_filename.
CALL METHOD OF excel 'read' EXPORTING #1 = v_filename.
DO 53 TIMES.
excel_line = sy-index.
DO 10 TIMES.
excel_coln = sy-index.
CALL METHOD OF excel 'CELLS' = cell
EXPORTING
#1 = 16
#2 = 4.
GET PROPERTY OF cell 'VALUE' = cell_value.
CLEAR wa_itab.
wa_itab-LINE = excel_line.
wa_itab-COLN = excel_coln.
wa_itab-name = cell_value.
APPEND wa_itab TO itab.
ENDDO.
ENDDO.
FREE OBJECT excel.
FREE OBJECT workbook.
FREE OBJECT sheet.
FREE OBJECT cell.
*call method of cell 'SELECT'.
*call method of cell 'COPY'.
*CALL FUNCTION 'CONTROL_FLUSH'
EXCEPTIONS
CNTL_SYSTEM_ERROR = 1
CNTL_ERROR = 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.
*CALL FUNCTION 'CLPB_IMPORT'
TABLES
data_tab = intern
EXCEPTIONS
CLPB_ERROR = 1
OTHERS = 2
.
*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 intern into wa_intern WHERE row eq 16 and col eq 4.
v_date = wa_intern-value.
CALL FUNCTION 'GET_WEEK_INFO_BASED_ON_DATE'
EXPORTING
DATE = intern
IMPORTING
WEEK = WEEK
MONDAY = MONDAY
SUNDAY = SUNDAY .
*
ENDLOOP.
ENDIF.
ENDLOOP.
regards
vijay
reward points if helpful
| User | Count |
|---|---|
| 3 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
