How to use Excel_ChartsAdd function?

[btuman]

I am trying to add a new chart to the Excel workbook using Excel_ChartsAdd(), but it always produces an error and since I cannot see the help information for the function, I am not sure that I am using the function correctly. I am calling it as follows:

// Create a new sheet for the chart
status = Excel_ChartsAdd(ExcelChartsHandle, &errorInfo, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, &ExcelChartHandle);

if (status<0) goto Error;

The status code returned is -2147467262, "No Such Interface".

How do I use this function correctly??

Thanks for your help.

[Chris Matthews]

There's nothing wrong with your function call. You must not be getting the handle to the Charts collection (ExcelChartsHandle) correctly. Excel has a hierarchy of objects that are arranged through collections. You can't just call a function on Charts in Excel, you have to start with the application object, then the workbook object, then you get to the charts collection of the workbook. For example, to open Excel, create a new workbook and add a new chart, you would call these functions:

//Create New Excel Application
Excel_NewApp (NULL, 1, LOCALE_NEUTRAL, 0, &hExcelApp);

//Get Collection of Workbooks from Application
Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppWorkbooks, CAVT_OBJHANDLE, &ExcelWorkbooksHandle);

//Add a new workbook to the c
ollection of workbooks
Excel_WorkbooksAdd (ExcelWorkbooksHandle, NULL, CA_DEFAULT_VAL, &ExcelNewWorkbookHandle);

//Get the collection of charts from the new workbook
Excel_GetProperty (ExcelNewWorkbookHandle, NULL, Excel_WorkbookCharts, CAVT_OBJHANDLE, &ExcelChartsHandle);

//Add a new chart to the collection of charts
Excel_ChartsAdd(ExcelChartsHandle, &errorInfo, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, &ExcelChartHandle);

Welcome to ActiveX programming. If you don't want to learn the very complicated Excel ActiveX API, you could use the Excel Reporting tool that ships with CVI. It is a vastly easier API into Excel. You can find and example under cvi\samples\activex\excel\excelreportdemo.prj.

Best Regards,

Chris Matthews
National Instruments

[btuman]

Hi Chris,

Thanks for the information. That is exactly what I am doing (in fact I tried copying and pasting your exact code), but I still get the -2147467262 error when calling Excel_ChartsAdd(). Any other ideas?

In the meantime, maybe I will take a look into the excel reporting tool.

Thanks,

Brian

[bilalD]

So this was a little more funcky than i thought it would be. I based the example from example in VB, but the following should add an empty chart on a worksheet

error = Excel_SheetsAdd (ExcelSheetsHandle, NULL, CA_DEFAULT_VAL,
CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
&newSheetHandle);

error = Excel_WorksheetChartObjects (newSheetHandle, NULL,
CA_DEFAULT_VAL,
&chartObjectHandle);

error = Exce
l_ChartObjectsAdd (chartObjectHandle, NULL, 50, 40, 300,
200, &excelSingleChartObject);

error = Excel_GetProperty (excelSingleChartObject, NULL,
Excel_ChartObjectChart, CAVT_OBJHANDLE,
&excelChartHandle);

Then its just a matter of giving the chart the data source and you should be set.

I hope this helps, i tested this with Excel 2002

Bilal

[Chris Matthews]

OK, I had a chance to look at this more. This appears to be an error with the Excel Automation interface. What you are doing should work, but it doesn't. I verified that it doesn't work in 3 different compilers. I did find a workaround that I will attempt to explain.

So, in Excel, Charts can be in two main places, embedded in a worksheet (which works fine, you can see an example of how to do this in our excel2000dem.prj example) or as separate worksheets (which is what your code is trying to do). Apparently, the ActiveX interface to Excel is not allowing you to create a Worksheet of type Chart, which is should allow you to do. To verify this, I tried to also use the Excel_SheetsAdd function with a type of xlChart, and it gave me the
same error as Excel_ChartsAdd.

However, I did find a workaround that adds a chart as a separate worksheet. If you change the code I submitted above by replacing the Excel_ChartsAdd function with an Excel_SheetsAdd function and leave all the parameters the same (as CA_DEFAULT_VAL), it will add a Chart sheet to your workbook. They you should be able to create a chart with data on that sheet. Your other option would be to add the charts as embedded objects instead of separate sheets as seen in the example program cvi\samples\activex\excel\excel2000dem.prj.

Hope that helps,

Chris Matthews
National Instruments

[btuman]

Hello Chris. I actually discovered this yesterday as well and was able to get it working using the Excel_SheetsAdd function. However, I used the function with a type of ExcelConst_xlChart and it worked for me. Anyway, thanks for the follow-up.

Brian