Chart Automation

Automate Your Charts

You can automate your Mekko Graphics charts using VBA or C#.

VBA Automation


// Return true if shape is a Mekko Graphics chart 
bool IsMekkoChart(PowerPoint.Shape shape);

// Open Mekko Graphics chart for editing
void EditMekkoChart(PowerPoint.Shape shape);

// Return true Mekko Graphics Chart Editor is open
bool IsChartEditorOpened();

// Save and close the currently opened Mekko Graphics chart
void SaveAndCloseMekkoChart();

// Extract the chart data from the currently opened Mekko Graphics chart
string[,] GetOpenedChartData();

// Extract the chart data from the provided Mekko Graphics chart
string[,] GetChartData(PowerPoint.Shape shape);

// Inject the chart data into the currently opened Mekko Graphics chart
void SetOpenedChartData(string [,] data);

// Inject the chart data into the provided Mekko Graphics chart
PowerPoint.Shape SetChartData(PowerPoint.Shape shape, string[,] data);

// Update Mekko Graphics chart
bool RefreshChart(PowerPoint.Shape shape);

// Replaces entire ChartDataArray in the currently opened Mekko Graphics chart 
void ReplaceOpenedChartDataArray(string[,] data);

// Replaces entire ChartDataArray for shape
PowerPoint.Shape ReplaceChartDataArray(PowerPoint.Shape shape, string[,] data);

// Refresh(Update) chart
bool RefreshChart(PowerPoint.Shape shape);

// Set chart labels font size. Equivalent to fonts in Preference Manager
void SetChartFont(PowerPoint.Shape shape, int maxFontSize, int minFontSize, int smartPlaceFontSize);

// Set Y axis title position
// titlePosition values: Vertical = 1, Horizontal = 2, TopTickLabel = 3
void SetYAxisTitlePosition(PowerPoint.Shape shape, int  titlePosition);

// Set axis title text
// axisId identifies axis as following: X = 1, Y = 2, Y2 = 4
void SetAxisTitleText(PowerPoint.Shape shape, int axisId, string title);

// Show series identified by rowIndex (in spreadsheet) as specified by format parameter 
// format values: Chart = 0, Table = 1, GraphAndTable = 2, CAGR = 3,  NotShown = -1
void ShowSeriesAs(PowerPoint.Shape shape, int format, int rowIndex);

// Show bar identified by columnIndex (in spreadsheet) as specified by format parameter
// format values: Chart = 0, Table = 1, GraphAndTable = 2, CAGR = 3,  NotShown = -1
void ShowBarsAs(PowerPoint.Shape shape, BarsSeriesDisplayOption format, int columnIndex);

// Set Chart Series Sort Order and optional flag to set Order of negative segments
// sortOption values:  NoSort = 1, ReverseOrder = 4, BiggestOnRightTop = 3, BiggestOnLeftBottom = 2;
// reverseOrderForNegative values: -1 do not change, 1 – set reverse order, 0 – set normal order
void SetSeriesSortOrder(PowerPoint.Shape shape, int sortOption, int reverseOrderForNegativeValues = -1);

// Set Chart Bars Sort Order and optional flag to include start bar in sorting for waterfall charts
// sortOption values: NoSort = 1,  ReverseOrder = 4, BiggestOnRightTop = 3,  BiggestOnLeftBottom = 2, WidestOnRightTop = 5, WidestOnLeftBottom = 6
// includeStartBarInSorting : -1 do not change, 1 – include in sorting, 0 – do not include in sorting
void SetBarsSortOrder(PowerPoint.Shape shape, int sortOption, int includeStartBarInSorting = -1);

// Change the chart type
// chartType values: Cluster=1, Stacked=2, Stacked100Pcnt=3, Waterfall=4, Marimekko=5, BarMekko=6, Line=7, Bubble=8, BubbleGrowthGrowth=9, BubbleGrowthShare=10, BubbleROSvsRMS=11, Scatter=12,Area=13, Area100Pcnt=14, Pie=15,
void SetChartType(PowerPoint.Shape shape, int chartType);

// Set chart segment’s color to a color from current palette identified by colorDisplayOrder
void SetSegmentColorFromPaletteById(PowerPoint.Shape shape, int row, int column, int colorDisplayOrder);

// Set chart segment’s color to a color from current palette identified by colorName
void SetSegmentColorFromPaletteByName(PowerPoint.Shape shape, int row, int column, string colorName);

// Set chart segment’s color to a color from  specified ARGB components
void SetSegmentColorFromArgb(PowerPoint.Shape shape, int row, int column, int alpha, int red, int green, int blue);

// Set Excel Link for the chart
void SetExcelLink(PowerPoint.Shape shape, string workbookPath, string rangeName, string address);

// Removes Excel Link from the chart
void RemoveExcelLink(PowerPoint.Shape shape);

// Refreshes chart data from linked Excel workbook if it is set
bool RefreshExcelLink(PowerPoint.Shape shape);

// Update all charts in the presentation
string UpdateAllCharts(PowerPoint.Presentation presentation);

// Exports the data for all charts in the presentation to data sheets inside the workbook
string ExportToExcel(PowerPoint.Presentation presentation, string outputExcelFilePath);

VBA Sample Code


// Switch to Microsoft Visual Basic Editor (Alt+F11) to see more code samples
// VBA

Sub UpdateSelectedChart()

    Dim addIn As COMAddIn
    Dim MG_utilities As Object

    Dim sel As Selection
    Dim sh As Shape

    Set addIn = Application.COMAddIns("MekkoGraphicsAddin")
    Set MG_utilities = addIn.Object

    If (MG_utilities Is Not Null) Then
        Set sel = ActiveWindow.Selection
        If (sel.ShapeRange.Count = 1) Then
            Set sh = sel.ShapeRange(1)
            If (MG_utilities.IsMekkoChart(sh)) Then
                MG_utilities.RefreshChart (sh)
            End If
        End If
    End If
End Sub

C# Automation


    public partial interface IAddInUtilities
    {
        bool IsMekkoChart(PowerPoint.Shape shape);

        void EditMekkoChart(PowerPoint.Shape shape);

        bool IsChartEditorOpened { get; }

        void SaveAndCloseMekkoChart();

        string[,] GetOpenedChartData();

        string[,] GetChartData(PowerPoint.Shape shape);

        // Only Replaces corresponding cell values in ChartDataArray if cells and values are not empty. Does not change size of ChartDataArray
        void SetOpenedChartData(string[,] data);

        // Only Replaces corresponding cell values in ChartDataArray if cells and values are not empty. Does not change the size of ChartDataArray
        PowerPoint.Shape SetChartData(PowerPoint.Shape shape, string[,] data);

        // Replaces entire ChartDataArray
        void ReplaceOpenedChartDataArray(string[,] data);

        // Replaces entire ChartDataArray
        PowerPoint.Shape ReplaceChartDataArray(PowerPoint.Shape shape, string[,] data);

        bool RefreshChart(PowerPoint.Shape shape);

        void SetChartFont(PowerPoint.Shape shape, int maxFontSize, int minFontSize = -1, int smartPlaceFontSize = -1);

        void SetYAxisTitlePosition(PowerPoint.Shape shape, OrientationEnum titlePosition);

        void SetAxisTitleText(PowerPoint.Shape shape, DimensionEnum axisId, string title); // DimensionEnum X=1,Y=2,Y2=4

        void ShowSeriesAs(PowerPoint.Shape shape, BarsSeriesDisplayOption format, int rowIndex);

        void ShowBarsAs(PowerPoint.Shape shape, BarsSeriesDisplayOption format, int columnIndex);

        void SetSeriesSortOrder(PowerPoint.Shape shape, BarsSeriesSortOptions sortOption, int reverseOrderForNegativeValues = -1);

        void SetBarsSortOrder(PowerPoint.Shape shape, BarsSeriesSortOptions sortOption, int includeStartBarInSorting = -1);

        // Change the chart type
        void SetChartType(PowerPoint.Shape shape, GeneralizedType type);

        // Set color for a chart segment
        void SetSegmentColorFromPaletteById(PowerPoint.Shape shape, int row, int column, int colorDisplayOrder);

        // Set color for a chart segment
        void SetSegmentColorFromPaletteByName(PowerPoint.Shape shape, int row, int column, string colorName);

        // Set color for a chart segment
        void SetSegmentColorFromArgb(PowerPoint.Shape shape, int row, int column, int alpha, int red, int green, int blue);

        void SetExcelLink(PowerPoint.Shape shape, string workbookPath, string rangeName, string address);

        void RemoveExcelLink(PowerPoint.Shape shape);

        bool RefreshExcelLink(PowerPoint.Shape shape);

        // Updates all charts in the presentation.
        string UpdateAllCharts(PowerPoint.Presentation presentation);

        // Exports the data for all charts in the presentation to data sheets inside the given workbook.
        string ExportToExcel(PowerPoint.Presentation presentation, string outputExcelFilePath);
    }

C# Sample Code


// C#

// First set a reference to MekkoAddin10.dll located in the Mekko Graphics installation folder
public void UpdateSelectedChart(Office.IRibbonControl control)
{
	object addInName = "MekkoGraphicsAddin";
	Office.COMAddIn addIn = Globals.ThisAddIn.Application.COMAddIns.Item(ref addInName);
  	
	WizardAddin.IAddInUtilities MG_utilities = (WizardAddin.IAddInUtilities) addIn.Object;
	
	if (MG_utilities != null)
	{
		PowerPoint.DocumentWindow dw = Globals.ThisAddIn.Application.ActiveWindow;
		PowerPoint.Selection sel = dw.Selection;
		
		if (sel.ShapeRange.Count == 1)
		{
			PowerPoint.Shape sh = sel.ShapeRange[1];
			
			if (MG_utilities.IsMekkoChart(sh))
			{
				MG_utilities.RefreshChart(sh);
			}
		}
	} 
}

Download Automating Your Charts presentation.

Automate Your Charts

Recent Blog Posts

Case Studies

Functions

Chart Types

Privacy

Mekko Graphics