Copying data from Microsoft Excel to ABAP using OLE

Purpose –

To copy data from Microsoft Excel to ABAP internal table using OLE Automation techniques.

Requirement –

In our day to day life we come across many situations where we would prefer to store the data in an excel file and the same data may be required to be uploaded to an ABAP program for further processing.

So there comes a need to upload data from excel file to internal table with a faster and efficient way.

Possible Solutions –

To achieve our requirement, there are some options to upload data in Excel file to internal table such as –

Function Modules –

1. TEXT_CONVERT_XLS_TO_SAP – This is a very simple function module but the constraint is that user should know exactly how many columns are going to be copied from the excel file.

2. KCD_EXCEL_OLE_TO_INT_CONVERT – In this function module also the beginning and ending row and column numbers are required to copy the data.

3. ALSM_EXCEL_TO_INTERNAL_TABLE – In this function module also the beginning and ending row number and column numbers are required to copy the data.

As analyzed and stated above, all these methods have a constraint that the user should know exact number of rows and columns that should be uploaded from excel file to internal table. But in reality, user may enter as much data in the excel file as he wants and it not necessarily be the same always. So, to cater such a situation, we can go for OLE automation techniques and copy the entire data present in the workbook into an internal table with ease.

Technical Specification –

For better understanding, users are advised to be aware of OLE automation technique in ABAP and VBA Macros in Microsoft Excel.

Here we will create an executable program, with input as an excel file location and it will copy the contents of excel file to clipboard. And the copied contents will be imported into an internal table. Each column of the excel sheet will become a column of internal table.

Excel Macro is recorded in Microsoft Excel 2007

ABAP programming is done in SAP ECC 6.0 EHP 4.0 Support pack 5

Logic and Pseudo Algorithm –

By the use of OLE automation in ABAP, we can call the functions available in MS Excel to simplify our processing. To find these methods, properties and constants which are to be used from our ABAP program to make the processing easy, it is better to record a macro in excel and understand all methods and properties that are used and how they are used, and then use them in a ABAP program.

Pseudo Algorithm –

1. Open the excel file present at the entered location. 

2. Mark first cell and get the first cell’s reference into a variable say cell 1

3. Mark last cell and get the last cell’s reference into a variable say cell 2 by executing CTRL + END in MS excel

4. Crate range starting at cell 1 and ending at cell 2

5. select the range

6. Copy the range

7. Import the copied content from clipboard to Internal table.

Process Steps –

Here are the steps to be followed to record an Excel macro to understand the functions and properties that are to be used in our ABAP program.

For step 2 of pseudo algorithm described, we will always consider the first cell, that is the cell at row 1 and column 1 as the starting cell ( Cell 1 ).

For step 3 of pseudo algorithm described, we will use excel methods and properties to find out the last cell that is filled on the active work sheet. The following are the steps to be followed to record an excel macros to find out the last cell that is filled with data on the active sheet :

1. Create a test excel file and input some data.

2. Record Macros by following the path : View -> Macros – > Record Macro

3. Give a Name to the Macro ( Macro1 ) and then click OK.

4. Hit the combination CTRL + END on key board.

5. Stop the Macro recording by following the path : View – > Macros – > Stop Recording

6. Display the recorded Macro using the following path : View – > Macros – > View Macros – > Select your Macros Name – > Edit

7. The following is the Macros that we shall see :

Sub Macro1()

‘

‘ Macro1 Macro

‘

‘

ActiveCell.SpecialCells(xlLastCell).Select

End Sub

8. Now in order to replicate the VBA Macro in abap, we have to know the methods, properties and constants of the above recorded macro. For that we make use of the Object Browser that is available at the location – View – > Object Browser

9. Now we will give each and every command of the Macro and examine whether it is a Method, Property or a constant.

10. First we will check for XlLastCell

11. Give the term in space provided for search term and then execute search

12. Here we will see that, it is a VBA constant whose value is 11. And we have to use 11 in our ABAP program.

13. Similarly we will examine the term SpecialCells

14. Here we find out that SpecialCells is a method

15. Here, for our example we have considered a simple macro to determine the last cell that is filled on the active sheet. For complex macros also the same procedure can be followed to find out the attribute of each and every command of VBA macro.

16. After finding out the OLE methods, properties and constants that are to be used, we are ready to write our ABAP program.

Code –

The following is the code which can be used to implement the described pseudo algorithm :

************************************************************************

*** Program       : YKK_EXCEL_SELECT_ALL

*** Author        : Kartik P

*** Creation Date : 11/05/2012

*** Description   : This program is used to select all the contents

***                 of an excel file, copy them and import the

***                 copied content from clip board to an internal

***                 table

************************************************************************

REPORT ykk_excel_select_all.

*&———————————————————————-*

*& INCLUDES USED

*&———————————————————————-*

INCLUDE : ole2incl.

*&———————————————————————-*

*& TYPES DECLARATION

*&———————————————————————-*

TYPES :

BEGIN OF ty_excelfile ,

line(50000) TYPE c ,

END OF ty_excelfile .

*&———————————————————————-*

*& DATA DECLARATION

*&———————————————————————-*

DATA :

* Objects to save excel

gv_activesheet      TYPE ole2_object,

gv_application      TYPE ole2_object,

gv_workbook         TYPE ole2_object,

gv_start_cell       TYPE ole2_object,

gv_end_cell         TYPE ole2_object,

gv_end              TYPE ole2_object,

gv_range            TYPE ole2_object,

gt_excel_string TYPE STANDARD TABLE OF ty_excelfile.

*&———————————————————————-*

*& PARAMETERS

*&———————————————————————-*

PARAMETERS : p_file TYPE string .

*&———————————————————————-*

*& AT SELECTION SCREEN

*&———————————————————————-*

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

* Presentation Sever F4 Input file path

PERFORM open_local_file CHANGING p_file.

*&———————————————————————-*

*& START OF SELECTION

*&———————————————————————-*

START-OF-SELECTION.

* Creating Object reference for Excel application

CREATE OBJECT gv_application ‘EXCEL.APPLICATION’.

* Getting the property of workbooks

GET PROPERTY OF gv_application ‘WORKBOOKS’ = gv_workbook.

* Opening the workbook

CALL METHOD OF gv_workbook ‘Open’

  EXPORTING

    #1 = p_file. ” File location entered

IF sy-subrc NE 0.

  MESSAGE ‘Entered File location cannot be opened’ TYPE ‘E’.

ENDIF.

* Refering the active worksheet of the application

GET PROPERTY OF gv_application ‘ActiveSheet’ = gv_activesheet.

* First cell is made as the starting cell

CALL METHOD OF gv_application ‘Cells’ = gv_start_cell

  EXPORTING

    #1 = 1

    #2 = 1.

GET PROPERTY OF gv_application ‘ActiveCell’ = gv_end_cell.

* Getting the last cell that is filled with data ( CTRL + END )

CALL METHOD OF gv_end_cell ‘SpecialCells’ = gv_end

  EXPORTING

    #1 = ’11’. ” Value for constant ‘xlLastCell’

* Value for constant xlLastCell is founc out using MS Excel object

*… navigator in VBA editor -> Step 12 of process mentioned above

* Creating range with starting cell and ending cell

CALL METHOD OF gv_application ‘Range’ = gv_range

  EXPORTING

    #1 = gv_start_cell

    #2 = gv_end.

* Selecting the Range

CALL METHOD OF gv_range ‘Select’.

* Copying the range

CALL METHOD OF gv_range ‘Copy’.

* Read clipboard intoABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

  IMPORTING

    data = gt_excel_string

  EXCEPTIONS

    cntl_error = 1

    error_no_gui = 2

    not_supported_by_gui = 3

    OTHERS = 4.

IF sy-subrc NE 0.

  MESSAGE ‘Error while uploading data’ TYPE ‘E’.

ENDIF.

* Freeing the used variables

FREE OBJECT gv_activesheet .

FREE OBJECT gv_workbook .

FREE OBJECT gv_application .

*&———————————————————————*

*& Form open_local_file

*&———————————————————————*

*&Purpose:The Subroutine gives the f4 help from presenatation server

*&———————————————————————*

*& Inputs: LV_FILE – INPUT OR OUTPUT FILE

*&———————————————————————*

FORM open_local_file CHANGING pv_file TYPE any.

* Local Declarations

DATA: lv_pfile TYPE localfile.

*F4 help for Presentation server

CALL FUNCTION ‘F4_FILENAME’

  EXPORTING

    program_name = syst-cprog

    dynpro_number = syst-dynnr

  IMPORTING

    file_name = lv_pfile.

pv_file = lv_pfile.

ENDFORM. “open_local_file

Execution –

After the execution of the program, output can be checked in two ways :

1.   Open a new notepad file and do paste ( CTRL + V )there.

Here since we are selecting all the data in excel file and then copying it. When we paste in notepad, we should be able to see the contents of excel file.

2.   In debugging mode – Set one break point after the call to method CLIPBOARD_IMPORT of class CL_GUI_FRONTEND_SERVICES.

We can double click on table gt_excel_string and see the contents, which should be same as entered in excel file.

For our example, following is the output in debugging mode –

Hope this document helps in using the concepts of OLE automation in ABAP.