Skip to Content

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.

  1. 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.

  1. 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

/wp-content/uploads/2012/05/11_101679.jpg

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

/wp-content/uploads/2012/05/2_101622.jpg

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

/wp-content/uploads/2012/05/3_101623.jpg

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

/wp-content/uploads/2012/05/4_101624.jpg

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.

  1.   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 –

/wp-content/uploads/2012/05/12_101680.jpg

/wp-content/uploads/2012/05/13_101682.jpg

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

To report this post you need to login first.

24 Comments

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

  1. Sowmya Sreeram

    Hi Kartik,

    Could you please let me know the ABAP code for this Macro?

    . I’m facing little difficulty in converting to ABAP. Could you please help me on this? 

    Now, I got the Macro:

    Sub Graph_Macro1()

    ‘ Graph_Macro1 Macro

    ‘ Macro recorded 5/16/2012 by ! on KNTTS3C66

        With ActiveChart

            .HasTitle = True

            .ChartTitle.Characters.Text = “Inventory”

            .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

    End Sub

    How can I get the proper ABAP code for this?

    Thanks in advance.

    Best Regards,

    Sowmya

    (0) 
    1. Kartik P Post author

      Dear Soumya Sreeram,

      I hope you have used the object VBA object browser and found out the methods and properties that are to be used in your abap program. Here i will guide you in setting the title for chart from your abap program, you can follow the similar instructions and set your axes titles accordingly –

      consider the vba statements –

      .HasTitle = True
      .ChartTitle.Characters.Text = “Inventory”

      Here, for explanation purpose i will use some variables which start with the letters ‘gv_’ all these variables are of type ole2_object.

      Get the reference of your chart into a variable named gv_chart ( This is to be done based on how you are creating you chart )

      SET PROPERTY OF gv_chart ‘HasTitle’  = 1.

      GET PROPERTY OF gv_chart ‘ChartTitle’  = gv_title.

      GET PROPERTY OF gv_title ‘Characters’  = gv_title.

      SET PROPERTY OF gv_title ‘text’  = ‘inventory’.

      Hope this helps. Thank you.

      Regards,
      kartik

      (0) 
      1. Sowmya Sreeram

        Hi Kartik,

        If I provide the Chart Title in the code, then the Graph is jumbled. If I’m not providing, then it is fine. I’m not able to understand the issue.

        I’m not able to insert image here.

        Here’s my code for setting the Chart Title:

        * Setting the Chart Type as Line Graph
          SET PROPERTY OF h_chart3 ‘charttype’ = ’65’.

        * Title for Chart

          SET PROPERTY OF h_chart3 ‘HasTitle’ = 1.
          GET PROPERTY OF h_chart3 ‘ChartTitle’  = h_title.
          GET PROPERTY OF h_title ‘Characters’  = h_title.
          SET PROPERTY OF h_title ‘text’  = l_header.

        -Sowmya

        (0) 
        1. Kartik P Post author

          Could you please tell me how you are getting the reference to your chart. Here everything else looks fine and works for me. Is h_chart3 refering to your chart object ??

          (0) 
          1. Sowmya Sreeram

            Yes.

            Here’s the full code:

            **************** creates an excel application
              CREATE OBJECT h_excel ‘EXCEL.APPLICATION’.
            *Sheet1
              CALL METHOD OF
                  h_excel
                  ‘Workbooks’ = h_mapl.
              SET PROPERTY OF h_excel ‘Visible’ = 1.

            *************** opens a work book
              CALL METHOD OF
                  h_mapl
                  ‘Add’  = h_map.

            *************** gets the current worksheet
              GET PROPERTY OF h_excel ‘activesheet’ = h_worksheet.
            *************** sets the name of the worksheet
              SET PROPERTY OF h_worksheet ‘Name’ = ‘SPW Table’.

            *********** pass data to sheet
              CALL METHOD OF
                  h_worksheet
                  ‘Cells’     = h_cell1
                EXPORTING
                  #1          = 1  ” starting row of selection
                  #2          = 1. ” starting column of selection

              CALL METHOD OF
                  h_worksheet
                  ‘Cells’     = h_cell2
                EXPORTING
                  #1          = 1  ” ending row of selection
                  #2          = 1. ” ending column of selection

            ———————- 

              CALL METHOD cl_gui_frontend_services=>clipboard_export
            ————-

                  h_range
                  ‘Select’.
            ————-

                  ‘Paste’.

            *create a chart sheet
              CALL METHOD OF
                  h_excel
                  ‘charts’ = h_chart.

            * opens the initially created sheet
              CALL METHOD OF
                  h_worksheet
                  ‘Select’.
              CALL METHOD OF
                  h_excel
            ————————————-code———–

            * combine cells to form the range
              CALL METHOD OF
                  h_excel
            ……..

            * sets the chart type
              CALL METHOD OF
                  h_excel
                  ‘charts’ = h_chart2.

              CALL METHOD OF
                  h_chart2
                  ‘add’    = h_chart3.

            * Selects the Worksheet of Chart
              CALL METHOD OF
                  h_chart3
                  ‘Select’.

            * Sets the name of the Chart WorkSheet
              SET PROPERTY OF h_chart3 ‘Name’ = ‘SPW Graph’.

            * Setting the Chart Type as Line Graph
              SET PROPERTY OF h_chart3 ‘charttype’ = ’65’.

            * Title for Chart
              SET PROPERTY OF h_chart3 ‘HasTitle’ = 1.
              GET PROPERTY OF h_chart3 ‘ChartTitle’  = h_title.
              GET PROPERTY OF h_title ‘Characters’  = h_title.
              SET PROPERTY OF h_title ‘text’  = l_header.

            *  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.

              CALL METHOD OF
                  h_chart3
                  ‘SetSourceData’

                EXPORTING
                  #1              = h_range ” range to be passed to the chart
                  #2              = 2.      ” selcts row/column

              CALL METHOD OF
                  h_chart3
                  ‘ApplyDataLabels’

                EXPORTING
                  #1                = ‘5’.

            ****** selects worksheet
              CALL METHOD OF
                  h_worksheet
                  ‘Select’.

              CALL METHOD OF
                  h_excel
                  ‘Cells’ = h_cell1
                EXPORTING
                  #1      = 1  ” starting row of selection
                  #2      = 1. ” starting column of selection

              CALL METHOD OF
                  h_excel
                  ‘Cells’ = h_cell2
                EXPORTING
                  #1      = 1        ” ending row of selection
                  #2      = l_count. ” ending column of selection

              CALL METHOD OF
                  h_excel
                  ‘Range’ = h_range
                EXPORTING
                  #1      = h_cell1
                  #2      = h_cell2.

            *********** sets the bold for the header
              GET PROPERTY OF h_range ‘Font’ = h_gs_font .
              SET PROPERTY OF h_gs_font ‘Bold’ = 1 .

              FREE OBJECT h_excel.
              FREE OBJECT h_worksheet.

            If I use the above “Title” code, then the Graph is jumbled.

            -Sowmya

            (0) 
            1. Kartik P Post author

              Dear Sowmya,

              Refering to your code, i have tried the following and it works fine for me.

              * Create a chart sheet
                CALL METHOD OF h_excel ‘charts’ = h_chart.

              * Opens the initially created sheet
                CALL METHOD OF worksheet ‘Select’.

              * Sets the chart type
                CALL METHOD OF h_excel ‘charts’ = h_chart2.

                CALL METHOD OF h_chart2 ‘add’ = h_chart3.

                SET PROPERTY OF h_chart3 ‘Name’ = ‘SPW Graph’.

              * Setting the chart type to ‘xlLineMarkers’
                SET PROPERTY OF h_chart3 ‘charttype’ = ’65’.

              * Title for chart
                SET PROPERTY OF h_chart3 ‘HasTitle’ = 1.

                GET PROPERTY OF h_chart3 ‘ChartTitle’ = ch_title.

                GET PROPERTY OF ch_title ‘Characters’ = ch_title.

                lv_title = ‘Capacity Overview’.

                SET PROPERTY OF ch_title ‘text’ = lv_title.

              In your coding, you need not do, set source data, because when you paste data in excel cells, then by default the data will be selected. So you can remove that part.

              Thanks and regards,

              kartik

              (0) 
    1. Kartik P Post author

      You are welcome, please do let me know if it helped you in understanding OLE concepts better and in interpreting the VBA commands in abap.

      (0) 
      1. Sowmya Sreeram

        Hi Kartik,

        I’ve got one more query. Got struck in this code converison.

        Can you please help me in converting the below 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) 
        1. Kartik P Post author

          Hi,

          Sorry, for a delayed reply, these days i am mostly away from computer. Anyways, I have got only a few minutes to study and respond to your query, Here is where you are commiting a mistake.

          consider the vba code :

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

          Now, if you can study the command properly and find out the methods and properties that are to be used, then you will find out that Axes is a class, which has a member AxisTitle

          Similarly, characters is a member of class AxisTitle and

          Text is a member of Class Characters.

          Now you have to use these methods and properties appropriately.

          In your program i think h_chart3 is a reference to the chart, but the property AxisTitle is present in the class Axes, so first of all you should get reference of axes of your chart into a variable say gv_axes then you have to go on refering the related methods and members.

          The following is the code to interpret the above mentioned line of vba code.

          CALL METHOD OF gv_chart ‘Axes’ = gv_axes
            EXPORTING
              #2 = 1. ” xlPrimary

          GET PROPERTY OF gv_axes ‘AxisTitle’ = gv_axis_title.
          GET PROPERTY OF gv_axis_title ‘Characters’ = gv_axis_title.
          SET PROPERTY OF gv_axis_title ‘Text’ = ‘X axis Title’.

          Please do try the rest in the similar way. Also do let me know after trying it out. Thank you.

          Regards,

          kartik

          (0) 
          1. Sowmya Sreeram

            Thank you so much Kartik. You’re Great!!!!!

            I learnt something new from you.

            It worked.

            Just one thing you forgot to add – ‘HasTitle’. Without this, it wasn’t working.

            Here’s my code:

            CALL METHOD OF h_chart3 ‘Axes’ = h_xaxis

              EXPORTING

                #2 = 2. ” For Y-Axis

            SET PROPERTY OF h_yaxis ‘HasTitle’ = 1.

            GET PROPERTY OF h_yaxis ‘AxisTitle’ = h_yaxistitle.

            GET PROPERTY OF h_yaxistitle ‘Characters’ = h_yaxistitle.

            SET PROPERTY OF h_yaxistitle ‘Text’ = l_yaxis.

            Million Thanks again!!!!!!!!!! 🙂

            (0) 
      2. Sowmya Sreeram

        Hi again! 🙂

        I was trying to push the Legend down.

        VBA:

        ActiveChart.ChartArea.Select

            ActiveChart.HasLegend = True

            ActiveChart.Legend.Select

            Selection.Position = xlBottom

        The constant value for XlBottom – -4107 (&HFFFFEFF5)

        My ABAP Code:

        * 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.

          CALL METHOD OF

              h_legend

              ‘Select’.

          SET PROPERTY OF h_legend ‘Position’  =  ‘-4107’.

        This is working if it is opened in Excel 2007, but not in 2003. Is there any difference if the Chart is opened in Excel 2007 / 2003?

        And for the below VBA:

        ActiveChart.SeriesCollection(6).Select
            With Selection.Border
                .Weight = xlThin
                .LineStyle = xlAutomatic
            End With
            With Selection
                .MarkerBackgroundColorIndex = xlAutomatic
                .MarkerForegroundColorIndex = xlAutomatic
                .MarkerStyle = xlNone
                .Smooth = False
                .MarkerSize = 5
                .Shadow = False
            End With
        End Sub
        __________________________________________________

        Red colored Max Inv and no marker

          ActiveChart.SeriesCollection(2).Select
            With Selection.Border
                .ColorIndex = 3
                .Weight = xlThin
                .LineStyle = xlContinuous
            End With
            With Selection
                .MarkerBackgroundColorIndex = xlAutomatic
                .MarkerForegroundColorIndex = xlAutomatic
                .MarkerStyle = xlNone
                .Smooth = False
                .MarkerSize = 5
                .Shadow = False
            End With
        End Sub

        How do we know which Series it is? I mean, if I want to change the property of a Particular Series based on Legend Description (for example Max Inventory Line on Chart), how do we select that Series?

        In VBA, it is SeriesCollection(2), etc.

        Thanks in advance,
        Sowmya

        (0) 
        1. Kartik P Post author

          Hi,

          I am glad that you have learned so much about OLE now.

          For selecting any particular series, you can pass either the number of the series or the name of the series to the method  ‘SeriesCollection’. Here by saying the name of the series i mean the name of the column which contains the data for the series. The following code works fine for me.

             GET PROPERTY OF gv_excel ‘ActiveChart’ = gv_chart.

             CALL METHOD OF gv_chart ‘SeriesCollection’ = gv_series
                   EXPORTING
                        #1 = ‘required’.

          Here required is the column name, and the same appears on the legend.

          Regarding the legend position in MS Excel 2007 and MS Excel 2003, i will try out and let you know. So far i have not found any major difference between the both as far as macros are concerned.

          Regards,

          kartik

          (0) 
  2. Peter C. Sandgaard

    It works perfect. Thanks a lot. But I want to use it for moving data to and from DB tables.

    Can I do that; how do I get the data from the internal table to DB and will it look OK ?

    Thanks.

    (0) 
    1. Kartik P Post author

      Dear Mr. Peter C. Sandgaard,

      you can fetch data from DB table and display it or save it in excel using ole by –

      1. Fetch data from DB tables to internal table

      2. export the contents of internal table to clipboard using cl_gui_frontend_services=>clipboard_export.

      3. Open a new workbook using OLE at a desired file location.

      4. Do a paste (ctrl + v) to paste the data which was copied to clipboard in step 2.

      5. Now the data is present in excel, here if required, the data can be formatted.

      To write data from excel to DB table –

      1. Copy the data from excel to an internal table using OLE.

      2. Once the data is present in internal table/tables you can do an insert data base table from internal table.

      Hope it helps. Thank you.

      Regards,
      Kartik

      (0) 
  3. Hari M

    Hi Karthik,

    i have requirement in excel, i have merged 4 rows and 3 columns. i need to pass the data into these merged cells with 4 lines. each line should show full text how much length that the text exist.

    i need to show the address in merged cell like below

    SAP – Business Management Software Solutions,

    Sarjapur Outer Ring Road,

    Bangalore,

    India,

    Can you please provide code for this.

    Thanks inadvace.

    Regards,

    Hari

    (0) 
  4. Otto Rodriguez

    Hello Kartik, thanks for all the information.  i need to assign labels to x data, you mentioned that the method to use is following, but i don’t know how to assign the values gv_series and ‘required’, please help.

    GET PROPERTY OF gv_excel ‘ActiveChart’ = gv_chart.

       CALL METHOD OF gv_chart ‘SeriesCollection’ = gv_series
             EXPORTING
                  #1 = ‘required’.

    thank in advance

    (0) 
    1. Kartik P Post author

      Dear Otto Rodriguez,

      In the above mentioned example of chart, the chart series name is nothing but the column name of data, which was passed as ‘required’ from internal table to clipboard and then from clip board to excel worksheet.

      Once the internal table data was pasted into excel cells, the data was selected and on the selection chart was generated so that the column name becomes name of a series in chart.

      For further details, i need to know how you are generating chart, if you are also generating it as i mentioned here then just pass the column name as desired and it will appear in your chart.

      Hope it helps. Feel free to revert for further clarification. Thank you.

      Regards,

      Kartik

      (0) 
  5. Liza Brckm

    Hi Kartik,

    now I got an insight into OLE, thank you.

    I would like though to update an infotype from the multi-worksheets uploaded xls though, not for the simple output.

    Could you help me?

    KR,

    Fata

    (0) 
  6. Adrian Zetak

    One option 😉


    DATA gs_chartAdoZet TYPE OLE2_OBJECT.


    GET PROPERTY OF gs_activesheet ‘ChartObjects’ = gs_chartobjects.

           CALL METHOD OF gs_chartobjects ‘Item’ = gs_chartAdoZet   ” That method return object to chart

                 EXPORTING

                       #1 = ‘Chart 1’.   ” Index = Number (1)/String (‘Chart 1’) <- find out in excel Name Box Number 1, … n

           CALL METHOD OF gs_chartAdoZet ‘Activate’ .

           CALL METHOD OF gs_chartAdoZet ‘Select’ .

           CALL METHOD OF gs_chartAdoZet ‘Cut’               ” ‘Copy’/‘Cut’.

    How does it to use ??? I was attaching according those link

    Generaly

    Object model (Excel VBA reference)

    Chart

    Chart Members (Excel)

    Typs of charts ( bottom at the end of www page) http://wiki.scn.sap.com/wiki/display/ABAP/ABAP-OLE+Code+Display+More+then+One+Chart+in+Excel

    (0) 

Leave a Reply