Skip to Content
Author's profile photo Kartik P

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.

  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.

Assigned tags

      24 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks for this Document Kartik. This is useful. 🙂

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kartik P
      Kartik P
      Blog 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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kartik P
      Kartik P
      Blog 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 ??

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kartik P
      Kartik P
      Blog 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

      Author's profile photo Former Member
      Former Member

      It's now working fine after I removed "set source data" code.

      Thanks once again 🙂

      Author's profile photo Former Member
      Former Member

      Thanks Kartik 🙂

      Author's profile photo Kartik P
      Kartik P
      Blog 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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kartik P
      Kartik P
      Blog 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

      Author's profile photo Former Member
      Former Member

      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!!!!!!!!!! 🙂

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kartik P
      Kartik P
      Blog 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

      Author's profile photo Clemens Li
      Clemens Li

      HI Kartik,

      Thank you for the nice document. It explains

      I think, for modern modularized programming, you may consider the use of Desktop Office Integration (BC-CI).

      Regards,

      Clemens

      Author's profile photo Peter C. Sandgaard
      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.

      Author's profile photo Kartik P
      Kartik P
      Blog 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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Kartik P
      Kartik P
      Blog 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

      Author's profile photo Shailendra Jain
      Shailendra Jain

      Thanks a lot...

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Adrian Zetak
      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