CVI 2009 vs. 2010: compatibility to Excel

[RobertoBozzolo]

Hello all,

I have created an application in CVI2009SP1 that exports its data to an Excel file using Excel2000 instrument driver shipped with CVI. All works well if the application is compiled and run on a WinXP machine but when run on a Win7 machine the customer complains to receive a strange error while opening the generated files:

The message basically means "The format of the file you want to open does not correspond to the type declared in its extension"

Pressing Ok will open the file regularly but I wonder what this error means and how to put it away.

I tested this situation in Excel 2007 and 2010, both starter and full edition: despite the error the files open regularly. On the other hand, the generated file cannot be opened in Word2000 due to a more severe "format not recognized" error.

I tried compiling the executable in CVI 2010 and I receive no error even if the Excel instrument is still declared as "Microsoft Excel 9.0 object library" the same as it is in 2009; nevertheless the object file associated to the instrument is newer than the 2009 version (June 30, 2010 for the library shipped with CVI2009; June 22, 2011 for the 2010 one).

I would prefer not to distribute a release compiled in 2010 as the application drives a high power equipment for research on prototypes and I cannot test it in real environment, so I tried simply to substitute CVI2009 version of the Excel interface library with the CVI2010 one and the program compiles and executes without errors.

So questions are:

Q1. Any clue as to what that error means?

Q2. Is it safe to upgrade the Excel library in CVI2009?

Q3. Any possibility to maintain compatibility to older versions of Excel?

[D_Biel]

Hi Roberto,

1. I don't have any particular insight on question 1. Since the extension is correct, there must be a internal formatting issue in the file. What function and parameters are you using to save the file?
2. Yes, you can upgrade the library. In fact, they should already be the same, because we did not make any changes to the library between 2009 SP1 and 2010. We ship the source code for this library, so try diffing the two versions: excel2000.c. Maybe you made a change without realizing it? If they are the same, try rebuilding the 2009 SP1 version of the library.
3. If you are using the Excel2000 library, it should be compatible with Excel 2000 and later. Which version of Excel are you trying to use?

[RobertoBozzolo]

Hi,

I diffed the source codes and they are effectively identical, while the libraries are slightly different in size (some 10 bytes) and creation date. I don't know if checking the object for differences makes sense. I haven't modified that instrument in any way.

I'll try to send to the customer a program release compiled in CVI2009 with 2010 library and see what happens: unfortunately I don't have in my office the exact copy of the target machine (Win7Pro + Office2010) to check it on.

To write the file I use the following:

errChk (CA_VariantSetCString(&MyVariant, <file pathname>));
errChk (Excel_WorkbookSaveAs (ExcelWorkbookHandle, NULL, MyVariant,
      CA_DEFAULT_VAL, CA_DEFAULT_VAL,
      CA_DEFAULT_VAL, CA_DEFAULT_VAL,
      CA_DEFAULT_VAL, ExcelConst_xlNoChange,
      CA_DEFAULT_VAL, CA_DEFAULT_VAL,
      CA_DEFAULT_VAL, CA_DEFAULT_VAL));

While examining my code now I noted something I didn't remember: when moving data to Excel I try to get the decimal point formatting code used in the system and apply it to the data in Excel worksheet, using this code:

// Read decimal separator from the registry
strcpy (msg, "Control Panel");
MakePathname (msg, "International", msg);
RegReadString (REGKEY_HKCU, msg, "sDecimal", a, 512, &i);

// Select some columns to format
sprintf (msg, "D2:H%d", cnt + 1);
errChk (CA_VariantSetCString (&MyVariant, msg));
if (ExcelRangeHandle) errChk (ClearObjHandle (&ExcelRangeHandle));
errChk (Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyVariant, CA_DEFAULT_VAL, &ExcelRangeHandle));
errChk (Excel_RangeActivate (ExcelRangeHandle, &ErrorInfo, NULL));

// Apply the proper format
sprintf (msg, "#0%s0", a);
errChk (CA_VariantSetCString (&MyVariant, msg));
errChk (Excel_SetProperty (ExcelRangeHandle, &ErrorInfo, Excel_RangeNumberFormat, CAVT_VARIANT, MyVariant));

I wonder if this code can cause any problem when executed on a Win7 machine?

Coming to your last question, I am using excel2000 instrument driver with the code above. Situation is as follows:

- When execute on a WinXPPro machine with Excel2000 I can create files without problems. Files created can be viewed in Excel2007 full and Excel 2010 starter without errors nor warnings

- Files created by the application running in a Win7 box can be opened in Excel 2010 with the warning I posted. They can't be opened in Excel2000: I receive the error "This file is not in a recognizable format" and if I follow on I see binary code in the worksheet

[RobertoBozzolo]

Additional update on this subject!

I simply renamed one of the files that was causing the error using ".xlsx" extension, without making any modification to the program: it can be opened without errors both in Excel 2010 and in Excel 2000 (after the proper conversion done by compatibility pack supplied by Microsoft) 

Another example of nondeterministic approach to computing!