topic Re: How to translate excel macro code for chart object to AbAp code in Application Development and Automation Discussions

How to translate excel macro code for chart object to AbAp code

xiswanto — Fri, 21 Jul 2023 08:26:01 GMT

Hello all,

Somehow I can't really find any documentation on how to translate excel macro code for chart object to ABAP code.

I appreciate if anyone can share any link to the translation guide or help me in translating the code below into ABAP code.

Here is the excel macro I wanted to translate ( which is to apply data label to stacked bar char, and to apply color to bar of each series; in my case, there are 3 series and I wanted to change each series color )

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetElement (msoElementDataLabelNone)
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.SetElement (msoElementDataLabelNone)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleNone)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
    ActiveChart.SetElement (msoElementDataTableNone)
    ActiveChart.ApplyDataLabels
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 240)
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent6
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.25
        .Transparency = 0
        .Solid
    End With

I did some attempt to apply the data label; although it show the label, but it disrupt the proses of the chart activation causing the chart object failed to be moved to source data sheet. Meanwhile, my attempt to change the color of fullseriescollection(index) failed.

  GET PROPERTY OF gs_application 'Charts' = gs_charts .
  CALL METHOD OF gs_charts 'Add' = gs_chart .
  CALL METHOD OF gs_chart 'Activate'.
  CALL METHOD OF gs_chart 'SetSourceData'
    EXPORTING
      #1 = gs_cells
      #2 = 1.
  SET PROPERTY OF gs_chart 'ChartType' = p_ch_type .
  SET PROPERTY OF gs_chart 'HasTitle' = p_title.
  SET PROPERTY OF gs_chart 'HasLegend' = p_legend.

  SET PROPERTY OF gs_chart 'ApplyDataLabels' = 'X'.
  
  CALL METHOD OF gs_excel 'WorkSheets' = gs_activesheet
    EXPORTING #1 = gv_sheet_name.
  CALL METHOD OF gs_activesheet 'Activate' .
  CALL METHOD OF gs_chart 'Location'
    EXPORTING
      #1 = 2
      #2 = gv_sheet_name.

Image 1: excel file run smoothly without the syntax set property of 'ApplyDataLabels'

Image 2 & 3: excel file stuck on the sheet of chart object with the syntax set property of 'ApplyDataLabels', failed to be moved to source data sheet ( although the data label is active )

Re: How to translate excel macro code for chart object to AbAp code

xiswanto — Tue, 25 Jul 2023 07:14:12 GMT

*Update 25.07.2023

I found the way to apply data label in chart.

However, I've still looking for way to update the color of each series in the chart. I do find out how to get the property of series until the part 'Selection.Format.Fill' and I am sure of it until this part, reason is because I could update the 'Visible' property which is below 'Fill' property. But weirdly enough, I tried to get the 'ForeColor' property, then I tried to set the 'RGB' property under 'ForeColor' (a.k.a. ForeColor.RGB) but it failed to do so using both 'set property of' and 'call method of', such as below passed value

SET PROPERTY OF gs_fullseries_c 'RGB' = 'RGB(255, 0, 0)'.<br>

I also tried to set another property 'ObjectThemeColor' under 'ForeColor' (a.k.a. ForeColor.ObjectThemeColor) but this one run without any problem using such code as below

SET PROPERTY OF gs_fullseries_c 'ObjectThemeColor' = 5.<br>

Please help suggest how do I fix the code.

Re: How to translate excel macro code for chart object to AbAp code

Sandra_Rossi — Tue, 25 Jul 2023 08:39:40 GMT

Could you please record the VBA code corresponding to what you want to do, make sure the VBA code works, and post it here?

It's easy to translate then... (without need to understand what does the VBA code)

Re: How to translate excel macro code for chart object to AbAp code

xiswanto — Wed, 26 Jul 2023 02:55:25 GMT

sandra.rossi sorry, I never heard the VBA code term before, did a quick search, perhaps this below is the VBA code?

I obtained it from the macro recording I created. (tested it on Microsoft Visual Basic for Application and it works as intended).

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Col 1"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Col 2"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Col 3"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Col 4"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Row 1"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "9"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "11"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Row 2"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "9"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "15"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Row 3"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "15"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "19"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "20"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("A1:E4").Select
    ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$E$4")
    ActiveChart.SetElement (msoElementDataLabelCenter)
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.SetElement (msoElementDataLabelNone)
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.FullSeriesCollection(1).Select
    Selection.Format.Fill.Visible = msoFalse
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(0, 176, 240)
        .Transparency = 0
        .Solid
    End With
    ActiveChart.FullSeriesCollection(3).Select
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
    End With
    Range("H5").Select
End Sub

Re: How to translate excel macro code for chart object to AbAp code

Sandra_Rossi — Wed, 26 Jul 2023 12:56:39 GMT

From the VBA code, you can translate into ABAP, you just need to understand a very simple logic to go from VBA to ABAP.

You have several posts which explain the logic or propose examples:

Using ole2 objects to create an excel file | SAP Blogs

OLE Uploading Excel file containing ole objects in worksheet | SAP Community

Merge table cells in MS Word - OLE objects | SAP Community

MS Outlook & OLE2 | SAP Community

How to eliminate empty rows in ole abap? | SAP Community

Copying data from Microsoft Excel to ABAP using OLE | SAP Blogs

Re: How to translate excel macro code for chart object to AbAp code

Sandra_Rossi — Wed, 26 Jul 2023 12:57:07 GMT

That's right, you have posted VBA code. The language of "Excel macro" is VBA (Visual Basic for Applications).