EXCEL2000.FP: removing existing sheets from a workbook

[RobertoBozzolo]

Hi all,

I am facing a strange problem when trying to delete some empty worksheets from an Excel workbook. My application uses Excel2000.fp to create and empy workbook and write some data in two of its sheets; after this is done, I would like to delete the other sheets that Excel creates as default (if any) using the following code:

 errChk (Excel_GetProperty (ExcelSheetsHandle, NULL, Excel_SheetsCount, CAVT_LONG, &cnt));
 if (cnt > 2) {
  for (i = 3; i <= cnt; i++) {
   errChk (Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(i),
     &ExcelWorksheetHandle));
   errChk (Excel_WorksheetDelete (ExcelWorksheetHandle, NULL));
   ClearObjHandle (&ExcelWorksheetHandle);
  }
 }

After this code, I close the workbook and exit Excel, but I have found that an istance of Excel remains active in Task Manager and if I open Excel only the window frame is visible but no menu bar, no toolbar and no empty worksheet as usual. Deleting that instance from the Task Manager removes the problem. Commenting out this code completely removes the abnormal behaviour.

Anybody can give me some hint to solve this problem?

[msaxon]

Which version of Excel? I've observed that Excel_AppQuit() doesn't appear to work in Excel 2003.

[RobertoBozzolo]

I am developing in CVI 7.1 and testing in my office with Excel 2000; I cannot test personally with a different version of Excel. You think this can give a difference I can send a copy to my customer and test with Excel 97: nothing more recent than this, I'm afraid...

[ebalci]

Hi Roberto,

In the past I remember discovering that Excel remains in Task Manager after my app exits if (I cannot say "only if") there is a handle left unterminated.

I also noticed your code looks like it uses 1-based indexing for sheets. Are you sure it is not 0-bases?
If there is an error due to this logic, your code may jump to the "cleanup" without passing through ClearObjHandle function, hence leaving the handle unterminated.

I cannot "dig" down any further from the given piece of code. So I hope this much helps.
Good luck!

[RobertoBozzolo]

Thanks Eren for the suggestion: I have observed too that an active handle leaves excel in the Task Manager, but it seems that this is not the problem in this case. I have verified that Item property is a 1-based index: with 4 sheets created by default, trying to delete from index 2 to 3 deletes the second sheet (where I have written my data) and 3, leaving the last untouched (but empty ). Based on your suggestion, I tried step-running my code and I received a not-better-specified exception when selecting (Excel_SheetsItem) the last sheet: in this case no handle should be active, since I have already cleared the previous handle, but the excel instance is still present in Task Manager...

Ok, maybe the better is to leave the empty sheets there and go on with something else: the operator receives also a confirmation question for each sheet to be deleted!