Skip to Content

Introduction

This blog post describes how to draw a graph in an excel file from SAP for standard transaction. The transaction used here is ME1P- Purchase Order Price History. Object Linking and Embedding (OLE) is a technology developed by Microsoft that allows embedding and linking to documents and other objects. OLE allows an editing application to export part of a document to another editing application and then import it with additional content.

ABAP & OLE

Through its Open Object Interface, ABAP supports the OLE2 Automation technique. Desktop applications that provide their functionality in the form of an OLE2 Automation Server (such as Excel or WinWord) can thus be integrated into R/3. The following ABAP key words control the applications:

  • CREATE OBJECT
    This ABAP key word generates an object of the class “class”. CREATE OBJECT H_EXCEL ‘EXCEL.APPLICATION’. To address an OLE Automation Server (e.g. EXCEL) from ABAP, the server must be registered with SAP. The CREATE statement generates the initial object of this class and this can be processed further with the related key words.
  • SET PROPERTY
    This ABAP key word sets the property of the object according to the contents of the field.
    The object must be of type OLE2_OBJECT. SET PROPERTY OF H_EXCEL ‘Visible’ = 1
  • GET PROPERTY
    This ABAP key word copies the property of the object to the field
    GET PROPERTY OF CELL ‘Font’ = BOOK_FONT.
  • CALL METHOD
    This ABAP key word calls the method of the object.
    CALL METHOD OF H_EXCEL ‘Workbooks„ = WORKBOOKS.
    CALL METHOD OF H_EXCEL ‘Cells’ = CELL
    EXPORTING
    #1 = 1
    #2 = 1.
  • FREE OBJECT
    This ABAP key word releases the storage space required for the object.
    The object can then no longer be processed.
    FREE OBJECT H_EXCEL.

When called from an ABAP program, the SAPGUI acts as OLE client, and the desktop application as the OLE server.

untitled.PNG

Macros

A macro is used to write a piece of code that can be used to perform a similar function in different scenarios. In Microsoft Office applications macros are written in VBA which enables developers to build user defined functions. These macros can be called via an ABAP program using OLE functionality provided by the Windows applications.

The macro defined here is used to outline the steps for creating the required graph in excel. Steps for creating a macro through ABAP program:

1. Define an internal table for the macrofile.

DATA: BEGIN OF MACROFILE OCCURS 0,
LINE(80) ,
END OF MACROFILE. “number of lines you require in your macro

2. Create a Sub..End Sub for the macro.

MACROFILE-LINE = ‘sub draw_graph()’.
APPEND MACROFILE. MACROFILE-LINE = ‘charts.add’.
APPEND MACROFILE. MACROFILE-LINE = ‘activechart.charttype = xllinemarkersStacked ‘.
…..
……
APPEND MACROFILE.
MACROFILE-LINE = ‘end sub ‘.
APPEND MACROFILE. “the Sub defines a function for the type of graph created

Sample Chart

12.PNG
Sample Code

This is an example of a code sample to draw an excel graph via SAP transaction ME1P using enhancement.

ENHANCEMENT ZXCEL_GRAPH.
*&------------------------------------------------------------------------*
*&  Program       :  ZXCEL_GRAPH                                          *
*&  Created by    :  Sakshi Dogra                                         *
*&  Description   :  Downloading data in excel and creating a graph for it*
*                    based on different vendors                           *
*&------------------------------------------------------------------------*
“checking t-code
IF SY-TCODE = 'ME1P'.
"making use of macros to design an excel for displaying a graph
INCLUDE OLE2INCL.  “include for accessing OLE objects registered with SAP
DATA: H_EXCEL TYPE OLE2_OBJECT,
      WORKBOOKS TYPE OLE2_OBJECT,
      THIS_WORKBOOK TYPE OLE2_OBJECT,
      CELL TYPE OLE2_OBJECT,
      BOOK_FONT TYPE OLE2_OBJECT.
 "counter variable
 DATA: V_CTR TYPE I.
 "macrofile
 DATA: BEGIN OF MACROFILE OCCURS 0,
        LINE(80) ,
       END OF MACROFILE.
"inserting excel file path for download
  CALL METHOD OF THIS_WORKBOOK 'InsertFile'
    EXPORTING
      #1 = 'C:\temp\VBsource.tmp'.
"defining parameters for creating graph
MACROFILE-LINE = 'Sub draw_graph()'.
“defining variables to be used
APPEND MACROFILE. MACROFILE-LINE = 'Dim ThisSeries As String'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim lngRow As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim lngStartRow As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim objChart As Chart'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim objSeries As Series'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim MinX As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim MaxX As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim MinAxisX As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim MaxAxisX As Long'.
APPEND MACROFILE. MACROFILE-LINE = 'Dim NewDate As Date'.
"defining selection area in the generated excel
APPEND MACROFILE. MACROFILE-LINE = 'Range("A4").Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Do While Not ActiveCell.Value = ""'.
“change in formula to get date in mm/dd/yyyy format
APPEND MACROFILE. MACROFILE-LINE = 'NewDate = DateValue(Mid(ActiveCell.Value, InStr(1, ActiveCell.Value, ".", _'.
APPEND MACROFILE. MACROFILE-LINE = 'vbTextCompare) + 1, 2) & "/" & Left(ActiveCell.Value, _'.
APPEND MACROFILE. MACROFILE-LINE = 'InStr(1, ActiveCell.Value, ".", _'.
APPEND MACROFILE. MACROFILE-LINE = 'vbTextCompare) - 1) & "/" & Right(ActiveCell.Value, Len(ActiveCell.Value) - _'.
APPEND MACROFILE. MACROFILE-LINE = 'InStrRev(ActiveCell.Value, ".", , vbTextCompare)))'.
APPEND MACROFILE. MACROFILE-LINE = 'ActiveCell.Value = NewDate'.
APPEND MACROFILE. MACROFILE-LINE = 'ActiveCell.Offset(1, 0).Activate'.
APPEND MACROFILE. MACROFILE-LINE = 'Loop'.
APPEND MACROFILE. MACROFILE-LINE = 'Range("A3").Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Range(Selection, Selection.End(xlDown)).Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Selection.NumberFormat = "dd/mm/yyyy;@"'.
APPEND MACROFILE. MACROFILE-LINE = 'Range("A1").Activate'.
"defining graph properties
APPEND MACROFILE. MACROFILE-LINE = 'Set objChart = ActiveSheet.ChartObjects.Add( _'.
APPEND MACROFILE. MACROFILE-LINE = '190, 30, 700, 325).Chart'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.ChartType = xlXYScatterLines'.
APPEND MACROFILE. MACROFILE-LINE = 'With ActiveSheet'.
APPEND MACROFILE. MACROFILE-LINE = 'lngStartRow = 3'.
APPEND MACROFILE. MACROFILE-LINE = 'lngRow = 3'.
APPEND MACROFILE. MACROFILE-LINE = 'ThisSeries = .Cells(lngStartRow + 1, 3).Value'.
APPEND MACROFILE. MACROFILE-LINE = 'Do While Len(.Cells(lngRow, 1).Value) > 0'.
APPEND MACROFILE. MACROFILE-LINE = 'ThisSeries = .Cells(lngStartRow + 1, 3).Value'.
APPEND MACROFILE. MACROFILE-LINE = 'If Not .Cells(lngRow + 1, 3).Value = ThisSeries Then'.
APPEND MACROFILE. MACROFILE-LINE = 'Set objSeries = objChart.SeriesCollection.NewSeries'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.Name = .Cells(lngStartRow + 1, 3).Value'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.XValues = .Range("A" & lngStartRow + 1, "A" & lngRow)'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.Values = _'.
APPEND MACROFILE. MACROFILE-LINE = 'Range("B" & lngStartRow + 1, "B" & lngRow)'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.HasDataLabels =True'.
"adding labels to lines drawn in graph
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)'.
APPEND MACROFILE. MACROFILE-LINE = 'lngStartRow = lngRow'.
APPEND MACROFILE. MACROFILE-LINE = 'End If'.
APPEND MACROFILE. MACROFILE-LINE = 'lngRow = lngRow + 1'.
APPEND MACROFILE. MACROFILE-LINE = 'Loop'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.HasDataLabels =True'.
APPEND MACROFILE. MACROFILE-LINE = 'objSeries.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)'.
APPEND MACROFILE. MACROFILE-LINE = 'End With'.
"adding title for the chart
APPEND MACROFILE. MACROFILE-LINE = 'objChart.HasTitle = True'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.ChartTitle.Characters.Text = " Commodity Price Graph "'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlValue, xlPrimary).HasTitle = True'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price(INR)"'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlCategory, xlPrimary).HasTitle = True'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "DATE"'.
“setting min and max values for date on x-axis
APPEND MACROFILE. MACROFILE-LINE = 'MinX = Application.Min(Range("A:A"))'.
APPEND MACROFILE. MACROFILE-LINE = 'MaxX = Application.Max(Range("A:A"))'.
APPEND MACROFILE. MACROFILE-LINE = 'MinAxisX = MinX - 30'.
APPEND MACROFILE. MACROFILE-LINE = 'MaxAxisX = MaxX + 30'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlCategory).MaximumScale = MaxAxisX'.
APPEND MACROFILE. MACROFILE-LINE = 'objChart.Axes(xlCategory).MinimumScale = MinAxisX'.
“fixing excel column width
APPEND MACROFILE. MACROFILE-LINE = 'Columns("A:A").Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Selection.ColumnWidth = 11'.
APPEND MACROFILE. MACROFILE-LINE = 'Columns("B:B").Select'.
APPEND MACROFILE. MACROFILE-LINE = 'Selection.ColumnWidth = 13'.
APPEND MACROFILE. MACROFILE-LINE = 'End Sub'.
APPEND MACROFILE.
"downloading data into excel
    CALL FUNCTION 'WS_DOWNLOAD'
    EXPORTING
        FILENAME = 'c:\temp\VBsource.tmp' "directory with temporary file
        FILETYPE = 'ASC'
    TABLES
       DATA_TAB = MACROFILE
    EXCEPTIONS
        FILE_OPEN_ERROR = 1
        FILE_WRITE_ERROR = 2
        INVALID_FILESIZE = 3
        INVALID_TABLE_WIDTH = 4
        INVALID_TYPE = 5
        NO_BATCH = 6
        UNKNOWN_ERROR = 7
        OTHERS = 8.
"creating excel file
    CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.
    SET PROPERTY OF H_EXCEL 'Visible' = 1.
    CALL METHOD OF H_EXCEL 'Workbooks' = WORKBOOKS.
    CALL METHOD OF WORKBOOKS 'Add' = THIS_WORKBOOK.
 "adding data to excel file
  " adding material heading
    CALL METHOD OF H_EXCEL 'Cells' = CELL
        EXPORTING
           #1 = 1
           #2 = 1.
     SET PROPERTY OF CELL 'Value' = 'MATERIAL' .
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 1 .
   " adding material
      LOOP AT XEINA.
        V_CTR = SY-TABIX.
        CALL METHOD OF H_EXCEL 'Cells' = CELL
           EXPORTING
               #1 = V_CTR
               #2 = 2.
       IF V_CTR = 1.
       SET PROPERTY OF CELL 'Value' = XEINA-MATNR .
       GET PROPERTY OF CELL 'Font' = BOOK_FONT.
       SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
       ENDIF.
     ENDLOOP.
      "    adding date heading
    CALL METHOD OF H_EXCEL 'Cells' = CELL
        EXPORTING
           #1 = 3
           #2 = 1 .
     SET PROPERTY OF CELL 'Value' = 'DATE' .
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 1 .
      "    adding price heading
      CALL METHOD OF H_EXCEL 'Cells' = CELL
           EXPORTING
             #1 = 3
             #2 = 2.
     SET PROPERTY OF CELL 'Value' = 'PRICE(INR)' .
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 1 .
  " adding vendor heading
    CALL METHOD OF H_EXCEL 'Cells' = CELL
        EXPORTING
           #1 = 3
           #2 = 3.
     SET PROPERTY OF CELL 'Value' = 'VENDOR' .
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 1 .
     SORT XEINA BY INFNR.
     SORT XEIPA BY INFNR.
      LOOP AT XEINA.
        LOOP AT XEIPA.
          IF xeipa-infnr = xeina-infnr.
        V_CTR = SY-TABIX + 3.
 " adding vendor
        CALL METHOD OF H_EXCEL 'Cells' = CELL
           EXPORTING
               #1 = V_CTR
               #2 = 3.
       SET PROPERTY OF CELL 'Value' = XEINA-LIFNR .
       GET PROPERTY OF CELL 'Font' = BOOK_FONT.
       SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
" adding date
       CALL METHOD OF H_EXCEL 'Cells' = CELL
           EXPORTING
               #1 = V_CTR
               #2 = 1.
     SET PROPERTY OF CELL 'Value' = XEIPA-BEDAT.
     GET PROPERTY OF CELL 'Font' = BOOK_FONT.
     SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
" adding price
       IF XEIPA-LPREI = ' '.
          CALL METHOD OF H_EXCEL 'Cells' = CELL
               EXPORTING
                   #1 = V_CTR
                   #2 = 2.
           SET PROPERTY OF CELL 'Value' = XEIPA-PREIS .
           GET PROPERTY OF CELL 'Font' = BOOK_FONT.
           SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
      ELSE.
            CALL METHOD OF H_EXCEL 'Cells' = CELL
                 EXPORTING
                      #1 = V_CTR
                      #2 = 2.
           SET PROPERTY OF CELL 'Value' = XEIPA-LPREI .
           GET PROPERTY OF CELL 'Font' = BOOK_FONT.
           SET PROPERTY OF BOOK_FONT 'Bold' = 0 .
      ENDIF.
       endif.
       endloop.
    ENDLOOP.
"adding data sheet to created excel file
      CALL METHOD OF H_EXCEL 'Modules' = MODULE.
      CALL METHOD OF MODULE 'Add' = NEWMODULE.
      CALL METHOD OF NEWMODULE 'Activate'.
      CALL METHOD OF NEWMODULE 'InsertFile'
         EXPORTING #1 = 'C:\temp\VBsource.tmp'.
"creating graph
      CALL METHOD OF H_EXCEL 'Run'
         EXPORTING #1 = 'draw_graph'.
"deallocating memory
       FREE OBJECT H_EXCEL.
ENDIF.
ENDENHANCEMENT.
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Sowmya Sreeram

    Thanks Sakshi for this Blog. 

    Can you please help me in converting the Excel Macro code to ABAP?

    I tried this way, but it is not working.

    For X-Axis Title:

    Here’s the VBScript:

    With ActiveChart 

            .Axes(xlCategory, xlPrimary).HasTitle = True

            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = “Date”

            .Axes(xlValue, xlPrimary).HasTitle = True

            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “MB”

        End With

        ActiveChart.ChartType = xlLineMarkers

    End Sub

    And my ABAP Code which is not working:

    GET PROPERTY OF h_chart3 ‘AxisTitle’ = h_xaxis.
      GET PROPERTY OF h_xaxis ‘Characters’  = h_xaxis.
      SET PROPERTY OF h_xaxis ‘text’  = l_xaxis.
    *
    *  GET PROPERTY OF h_chart3 ‘AxisTitle’ = h_yaxis.
    *  GET PROPERTY OF h_yaxis ‘Characters’  = h_yaxis.
    *  SET PROPERTY OF h_yaxis ‘text’  = l_yaxis.

    ActiveChart.HasLegend = True

        ActiveChart.Legend.Select

        Selection.Position = xlBottom

    Const xlBottom = -4107 (&HFFFFEFF5)


    * Making the Legend to display at the Bottom of the Chart
      SET PROPERTY OF h_chart3 ‘HasLegend’ = 1.
      GET PROPERTY OF h_chart3 ‘Legend’  = h_legend.
    *  GET PROPERTY OF h_legend ‘Select’  = h_legend.
      CALL METHOD OF
          h_legend
          ‘Select’.
      SET PROPERTY OF h_legend ‘Position’  =  ‘-4107’. ” ‘&HFFFFEFF5’. ” ‘-4107’.

    ActiveChart.SeriesCollection(2).Select

        With Selection.Border

            .ColorIndex = 3

            .Weight = xlThin

            .LineStyle = xlDot

        End With

        With Selection

            .MarkerBackgroundColorIndex = xlNone

            .MarkerForegroundColorIndex = xlNone

            .MarkerStyle = xlNone

            .Smooth = False

            .MarkerSize = 3

            .Shadow = False

        End With

    Please do help. Thanks!!

    Rgards,

    Sowmya

    (0) 

Leave a Reply