c# automate excel chart with secondary axis generates HRESULT: 0x800A03EC exception

[EDN Admin]

Using C# I am trying to automate the import of data into an excel file, then plot the information on a multi-axis plot. Desired plot would have one data series on the primary (left) y-axis, and the remainder of the data on the secondary (right) y-axis. I
can generate this plot in excel, but any attempt to use the secondary axis in C# results in an error. "HRESULT: 0x800A03EC exception when generating an excel chart"
I have included additional information and semi-pseudocode below. The code is tagged in a comment where it dies (toward the bottom). I would appreciate help as all searches (including this forum) have resulted in code solutions which either generate the
same error or Visual Studios refuses to compile.
<span style="text-decoration:underline Excel Data Setup Description:

data is in columns B-Gcolumn B is the reference index (x-axis)columns C-G are data (y-axis)want columns C-F on secondary y-axis (right); data series 1-4G on primary y-axis (left); data series 5
<span style="text-decoration:underline <span style="text-decoration:underline Useful Information? <span style="text-decoration:underline

Visual Studios 2010 Version 10.0.30319.1.NET 4.0.30319<span style="text-decoration:underline Excel 2003 (11.8346.8341) SP3<br/>

<span style="text-decoration:underline CODE
<pre class="prettyprint using System;
using Excel = Microsoft.Office.Interop.Excel; // required to automate excel
using System.Reflection; // required to automate excel


public void SOMECODE
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;


//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;

//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;

/* READ OF FILE INTO EXCEL REDACTED FOR SIMPLICITY */
// data is put in columns B-G; column B is the reference index (x-axis)
// columns C-G are data (y-axis); want columns C-F on secondary y-axis (right); G on primary (left)


/* FORMAT DATA IN EXCEL REDACTED FOR SIMPLICITY */

// GENERATE A CHART (using a function)
generateDataChart(oWB, oSheet, nDataEntries, headerLinesExcel, dataName);

//Make sure Excel is visible and give the user control
//of Microsoft Excels lifetime.
oXL.Visible = true;
oXL.UserControl = true;

}


private void generateDataChart(Excel._Workbook oWB, Excel._Worksheet oWS, int dataEntries, int headerLines, string dataName )
{
Excel.Series oSeries;
Excel.Range oResizeRange;
Excel._Chart oChart;

// Add a Chart for the selected data.
oChart = oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

// Direct Plot Generation...note: ChartWizard failed to work at all

// define the source data range
oResizeRange = oWS.get_Range( dataName + "!$B$" + (headerLines).ToString() +":$G$"+(dataEntries+headerLines).ToString() );
label3.Text = dataName + "!$B$" + (headerLines).ToString() +":$G$"+(dataEntries+headerLines).ToString();

// define the chart and set the data
oChart.ChartType = Excel.XlChartType.xlXYScatterSmooth;
oChart.SetSourceData(oResizeRange, Excel.XlRowCol.xlColumns);
oChart.Activate(); // This is not required; makes the chart the active chart (hoping to use below for actively references commands)

// apparently a chart must have at least 2 series before a 2nd axis can be added
// set non-set5 series items to secondary axis
label3.Text = "pre-Secondary";
for (int SeriesN = 1; SeriesN == 4; SeriesN++)
{
((Excel.Series)oChart.SeriesCollection(SeriesN)).AxisGroup = Excel.XlAxisGroup.xlSecondary;
}


// make the second axis visible
label3.Text = "set visible?";
oChart.set_HasAxis(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary, true); // create secondary y axis (so I think)
oChart.set_HasAxis(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlSecondary, true); // create secondary x axis

label3.Text = "post-Secondary";
// -- set the title and axis labels
oChart.HasTitle = true;
oChart.ChartTitle.Text = "Some Data " + dataName;
// Format the chart title
oChart.ChartTitle.Font.Name = "Arial";
oChart.ChartTitle.Font.Size = 20;
oChart.ChartTitle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
// primary x axis, specified by xlCategory
oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = true;
oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.text = oWS.Cells[headerLines, 2]; // should be column B header, x axis
// primary y axis specified by xlValue
oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = true;
oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.text = oWS.Cells[headerLines, 7]; // should be data set 5, left y axis
oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary).CrossesAt =



label3.Text = "pre-Secondary label";
// secondary x axis
//oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlSecondary).HasTitle = true;
//oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlSecondary).AxisTitle.text = oWS.Cells[headerLines, 2]; // should be column B header, x axis
// secondary y axis
/*
*
*
* CODE DIES ON THE NEXT LINE. if commented will die on the next oChart line after that, etc
*
*
*
*/
oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary).HasTitle = true;
label3.Text = "pre-Secondary label2";
// mark the second axis label
oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary).AxisTitle.Characters.text = "Sets 1-4"; // right y axis
label3.Text = "post label second";
oChart.Axes(Visible, Excel.XlAxisGroup.xlSecondary); // this line causes a memory error
label3.Text = "post-Secondary label";

// worksheet label
oWS.Name = "data_plot";

label3.Text = "Here success";

} // generateDataChart

[/code]




View the full article