Excel remains the most powerful tool of the Microsoft office package, the ease of use and function available make it favourite among the users. I have seen people using the SAP report just as a tool to export the data in excel and then carry out the entire remaining task in Excel. Believe me they have compelling reason for that. There are function module and class available in SAP which will directly export (or import) the data from the internal table to excel document, however, in some complex situation, you end up with no other solution but OLE Automation.
OLE Automation method allows ABAP program to communicate with any Application which supports OLE Automation. Excel is one of them. In simple terms you can consider whole Excel as an object and as long as you know the structure of the class to which this object belongs you can control it as you may like.
Working with excel has its own advantage, you don’t need to know the whole object model of excel to carry out your stuff. You can simply record a macro for the desired task you want to control from ABAP and then use that macro code with little modification in ABAP to carry out same thing from ABAP. Let’s work through it with an example.
In below example, we will download the data from table T005 and T005T to excel sheet with some formatting and then save it at the specified location. To know what will be excel equivalent code to perform these activities we need to first record a macro in excel which will create an excel sheet, put some data on it with some formatting and save it.
To records a macro select Tool->Macro->Record New Macro
Specify the name of macro and hit OK on next screen
Once a recording has started do the stuff you would like to do from ABAP, in this case, I have created two columns with some formatting to it as seen in below screenshot. Once you are done stop the recording and to see the code generated choose menu Tool->Macro->Macro.
On next screen select the macro name and press Edit button. You can now see the code which will very much look like below.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 14/07/2009 by kesarip
'
'
Wookbooks.Add
ActiveCell.FormulaR1C1 = "Country "
Range("B1").Select
ActiveCell.FormulaR1C1 = "Country Name"
Range("A2").Select
ActiveCell.FormulaR1C1 = "Uk"
Range("B2").Select
ActiveCell.FormulaR1C1 = "United Kingdom"
ActiveWorkbook.SaveAs Filename:="H:Book2.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub
Let's analyse some code which we will use in ABAP.
Workbooks.Add
will create new excel sheet.
Range("B1").Select
will select a particular cell
ActiveCell.FormulaR1C1 = "Coutry Name"
will put value in the selected cell.
ActiveWorkbook.SaveAs
is to save the sheet.
REPORT zsaexcel.
*&---------------------------------------------------------------------*
*& Types and Data
*&---------------------------------------------------------------------*
TYPE-POOLS ole2.
TYPES: BEGIN OF ty_t005,
land1 TYPE t005-land1,
landx TYPE t005t-landx,
END OF ty_t005.
DATA : i_t005 TYPE TABLE OF ty_t005 ,
ls_t005 TYPE ty_t005 .
DATA: h_excel_application TYPE ole2_object, “Excel object
h_activewindow TYPE ole2_object, " Active Window
h_work_books TYPE ole2_object, " List of workbooks
h_work_book TYPE ole2_object, " Workbook
h_active_cell TYPE ole2_object. “Cell
DATA : lv_row TYPE i ,
lv_col TYPE i ,
i_val TYPE string ,
l_prop TYPE i .
*&---------------------------------------------------------------------*
*& Start of Selection
*&---------------------------------------------------------------------*
START-OF-SELECTION.
SELECT t005~land1
t005t~landx
INTO TABLE i_t005
FROM t005 INNER JOIN t005t ON t005~land1 = t005t~land1
WHERE t005t~spras = sy-langu.
*&---------------------------------------------------------------------*
*& End of Selection
*&---------------------------------------------------------------------*
END-OF-SELECTION.
CREATE OBJECT h_excel_application 'EXCEL.APPLICATION'.
* Workbooks.AddGET PROPERTY OF h_excel_application 'Workbooks' = h_work_books.
CALL METHOD OF h_work_books 'Add' = h_work_book.
* Set header values
lv_row = 1.
lv_col = 1.
CALL METHOD OF h_excel_application 'Cells' = h_active_cell
EXPORTING #1 = lv_row #2 = lv_col.
i_val = 'Country code’.
SET PROPERTY OF h_active_cell 'FormulaR1C1' = i_val .
lv_row = 1.
lv_col = 2.
CALL METHOD OF h_excel_application 'Cells' = h_active_cell
EXPORTING #1 = lv_row #2 = lv_col .
i_val = 'Country Name’.
SET PROPERTY OF h_active_cell 'FormulaR1C1' = i_val.
LOOP AT i_t005 INTO ls_t005.
lv_row = sy-tabix + 1.
lv_col = 1.
CALL METHOD OF h_excel_application 'Cells' = h_active_cell
EXPORTING #1 = lv_row #2 = lv_col .
i_val = ls_t005-land1 .
SET PROPERTY OF h_active_cell 'FormulaR1C1' = i_val .
lv_col = 2 .
CALL METHOD OF h_excel_application 'Cells' = h_active_cell
EXPORTING #1 = lv_row #2 = lv_col .
i_val = ls_t005-landx.
SET PROPERTY OF h_active_cell 'FormulaR1C1' = i_val.
ENDLOOP.
* Save
CALL METHOD OF h_work_book 'SaveAs'
EXPORTING
#1 = 'H:1.xls'.
* Close the current window
GET PROPERTY OF h_excel_application 'ActiveWindow' = h_activewindow .
CALL METHOD OF h_activewindow ‘Close’.
Output File:
