In my previous post, I described how to programmatically add an embedded chart to an Excel worksheet, and what issues I encountered in the process. I was not very happy with the solution, and figured out a much better way to do this, inspired largely by this. Rather than add a chart to the workbook, and then set its location, it is possible to directly add the chart to a worksheet, by using the following syntax:

public static Excel.Chart AddEmbeddedChart(Excel.Worksheet worksheet,
Excel.XlChartType chartType, Excel.Range dataRange, Excel.XlRowCol byRowOrCol,
string title, double left, double top, double width, double height)
{
Excel.ChartObjects chartObjects = (Excel.ChartObjects)(worksheet.ChartObjects(Missing.Value));
Excel.ChartObject chartObject = chartObjects.Add(left, top, width, height);
Excel.Chart embeddedChart = chartObject.Chart;
embeddedChart.ChartType = chartType;
embeddedChart.SetSourceData(dataRange, byRowOrCol);

embeddedChart.HasTitle = true;
embeddedChart.ChartTitle.Text = title;

return embeddedChart;
}


Instead of creating a chart sheet first, and then re-locating (a copy of) the chart into the target worksheet, this version directly creates the chart in the right place. The cherry on the cake: you can set the location and size of the chart immediately, instead of having to perform acrobatics to retrieve the chart in the sheet…

So why did I go the wrong path first? Because of my best but not always reliable friend, the Macro Recorder.

When writing some C# automation for Excel, rather than trying to figure out beforehand the right properties and method names to use in the Excel object model, I usually first launch the macro recorder and do manually something similar to what I want the automation to perform, and scan the VBA code produced by the recorder. This is a great way to get a quick sense for one way to get the desired results, and what objects are involved.

There are two issues with that approach. First, figuring out which classes should be used in the C# equivalent code can be tricky, because the VBA code is generally not very explicit, and because most things are untyped in the Interop, intellisense isn’t of much help either. And then, of course, the VBA code itself isn’t always top quality, either - which is exactly what happened to me. This is the code generated by the Macro Recorder on Excel 2003:

Range("B2:D3").Select
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B2:D3"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"


Note that it does exactly what I described yesterday: add the chart, then change its location. Which is a pretty bizarre approach, if you ask me: why have a default setting which adds charts as embedded to a worksheet, and then record a macro as if the default was to add a chart in its own sheet? I was also pretty surprised to see that the code generated was different when I used Excel 2007

Range("B3:D4").Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$B$3:$D$4")