Databar formatting in Excel using Actxserver

Im using actxserver to write data to an excel sheet and to format it. I managed to color cells and to add frames to the cells, but I am stuck at adding databars.

The corresponding VBA code would be:

   Range("D7:D217").Select
      Selection.FormatConditions.AddDatabar
      Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1)
          .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
          .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
      End With
      With Selection.FormatConditions(1).BarColor
          .Color = 13012579
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
      Selection.FormatConditions(1).Direction = xlContext
      Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
      Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
      Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
      With Selection.FormatConditions(1).AxisColor
          .Color = 0
          .TintAndShade = 0
      End With
      With Selection.FormatConditions(1).NegativeBarFormat.Color
          .Color = 255
          .TintAndShade = 0
      End With

How can I change the code to make it work in Matlab? Any advice, also a link to general advice on how to use actxserver with Excel (i.e. what changes to the VBA code need to be done) is appreciated. Thanks!

Answer 1

[Sarabjit Kheberi]

Since you have a working VBA script, you can create a macro with your code. You can then run an excel macro from MATLAB using the instructions in the below MATLAB answers page:

https://www.mathworks.com/matlabcentral/answers/100938-how-can-i-run-an-excel-macro-from-matlab

[Johannes Baumgarten]

Thank you for your answer! I found my way around it by formatting an empty excel sheet and copying it into the used one every time the program is used. But your way would work as well especially with more complicated topics.

Answer 2

[Johannes Baumgarten]

If someone ever gets the same problem, I found the solution myself, using the invoke command. Excel is openend in Actxserver.

     dummy_range = 'A1:A100'; 
     Excel.Range(dummy_range).Select;
     Databar = invoke(Excel.Selection.FormatCondition, 'AddDatabar');
     Databar.BarFillType = 'xlDataBarFillSolid';
     Databar.AxisPosition = 'xlDataBarAxisAutomatic';
     Databar.BarColor.ThemeColor =  5;
     Databar.NegativeBarFormat.ColorType = 'xlDataBarSameAsPositive';

Once you know how to do it, it gets quite self explanatory.