Inserting data into an excel spreadsheet with Microsoft Excel 8.0 Object Library

[HIlg]

Hy there,
After giving up on the database stuff (see previous postings) I turn my
attention to export data to Excel.

Again, my basic problem appears to be kind of easy:
I need only one function, that writes data (a two dimensional "double" array
with a known amount of rows and columns) in an excel file. Furthermore, this
function should detect if a consigned file already exists, if so, the data
should be written into a new created worksheet.
Unfortunately, the task turned out not to be that easy, not at least due to
the "Could not launch the help file" message on about 80% of all the
functions in the library. Did I forget to install this file? Which file is
looked for? Can I add this file subsequently?

So far, I have the following questions:
- After creating a new workbook (Excel_WorkbooksAdd()) there are by default
3 worksheets in it. I only want to have one though. How do I delete the
additional two? Excel_WorksheetDelete() causes a dialog box from Excel. Or
how do I create a workbook with only 1 worksheet?
- After inserting some data, I want to format the appropriate range with the
Excel function Format->Column->AutoFitSelection. How do I do that?
- I want to create a graph based on this data. The first column is a date, I
want to create an XY(Scatter), smooth line, rest default graph. But when I
call the function Excel_ChartChartWizard the error with code 0x800a03ec,
ChartWizardMethod or Chart class failed occurs. I took the function call
directly from the excel97demo.c, only I changed ExcelConst_xl3DSurface in
the gallery parameter to ExcelConst_xlXYScatterSmooth.
- After calling Excel_AppQuit, the visible application closes, but there is
still a process running which can't be closed but with the task manager.
Furthermore, if I try to open the created Excel file, the frame of the Excel
application appears, but no data is displayed. After shutting down, an WinNT
exception error occurs.

In regard to the amount of questions to be expected: Does NI provide any
documentation about using the Excel97 Library?
Or do I have to go through the whole ActiveX crap and learn, what GUID clsid
= {0x20818, 0x0, 0x0, 0xC0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x46} and stuff
like that means??

Thanks in advance for any response!

Harald

mailto:HIlg@gmx.net

[Chris Matthews]

I won't address everything you discussed, but a few things that will help:

1) The help for Microsoft Excel Automation is installed with Microsoft Office (or Excel) as Visual Basic Help. Go back through Office installation to add these help files.

2) The reason you are not getting Excel to shutdown is that you are not properly releasing all of the object handles. Anytime you are using a object handle to the app, a workbook, worksheet, range, etc. You have to release it with CA_DiscardObjHandle. See the examples for a demonstration of this. You must have handles you aren't freeing.

3) NI doesn't provide help for specific 3rd-party application automation outside of the examples we ship. Microsoft is responsible for this documentation since they develop th
is API. However, we are going to be providing an Excel reporting instrument driver in the next major release of CVI to help with Excel reporting tasks. We currently have such a tool for Word that is under \cvi\samples\activex\word\wordreport.fp.

Best Regards,

Chris Matthews
Measurement Studio Support Manager

[Alister]

With regard to point 2: The example supplied with Measurement Studio does not shut Excel down correctly. (I'm still trying to work out how to make it work)

[Alister]

It turns out to be a simple fix. In the ReadDataFromExcel function (in excel2000dem.c) the call to ClearObjHandle needs to be moved into the inner loop eg just after the call to CA_VariantClear:

// Free Variant element
CA_VariantClear(&MyVariant);
ClearObjHandle (&ExcelSingleCellRangeHandle);

Thanks, Chris, for pointing me in the right direction!