Excel: Get number of worksheets, and each worksheet name

[Greg_NTI]

Hello,

I need to read the total number of worksheets within a workbook, and the name of each worksheet.
Can anyone please give me a pointer to the right functions?

Thanks,
Greg

[ebalci]

Hi Greg,

Below is a some code for your needs.

Make sure you discard every handle (application handle, workbook handle, worksheet handle, etc) using the CA_DiscardObjHandle() function. Otherwise you will end up with lots of EXCEL.exe ghost processes filling up your active process list which you can see from the Windows Task Manager.

After you close the workbook and the quit the excel application, call CA_DiscardObjHandle() for both appHndl and wbHndl.

You can see that I discarded them before getting a new handle in the for loop and so quit the loop with all worksheet allocated resources freed.

Hope this helps,

...

ExcelRpt_ApplicationNew (FALSE, &appHndl);
ExcelRpt_WorkbookOpen (appHndl, filePath, &wbHndl);
ExcelRpt_GetWorkbookAttribute (wbHndl, ER_WB_ATTR_NUM_WORKSHEETS, &wsCount);

for (int i = 1; i <= wsCount; i++) {
  ExcelRpt_GetWorksheetFromIndex (wbHndl, i, &wsHndl);
  ExcelRpt_GetWorksheetAttribute (wbHndl, ER_WS_ATTR_NAME, wsName);
  CA_DiscardObjHandle (wsHndl);
  MessagePopup ("WS Name", wsName);
}

...

[RobertoBozzolo · Accepted solution]

Hello,

the following code permits to retrieve the name of each sheet in a given workbook in 'name' string. It assumes you are using the Excel ActiveX library (excel2000.fp) and shows only the relevant code for this task: code for launching Excel and connecting to the worksheet is not shown, you can take it from the Excel example installed with CVI.

long	nSheets;
int		i;
char	*name = NULL;

errChk (Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets, CAVT_OBJHANDLE, &ExcelSheetsHandle));
errChk (Excel_GetProperty (ExcelSheetsHandle, NULL, Excel_SheetsCount, CAVT_LONG, &nSheets));
for (i = 1; i <= nSheets; i++) {
	errChk (Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(i), &ExcelWorksheetHandle));
	errChk (Excel_GetProperty (ExcelWorksheetHandle, NULL, Excel_WorksheetName, CAVT_CSTRING, &name));

	// Add code to handle 'name' content here

	CA_FreeMemory (name);
	errChk (ClearObjHandle (&ExcelWorksheetHandle));
}

When dealing with ActiveX, remember to dispose of all allocated handles before terminating the application, otherwise some Excel task may remain pending in the system and prevent further connection to it.

errChk macro is taken from the Programmer's toolbox: you can either strip it away or add toolbox.fp to yout project.

I cannot help you in case you intend to use ExcvelReport.fp since I'm not using it in my activity.

[Greg_NTI]

Sorry for beeing not precise in my description.

I am using "excel2000.fp"

Your code does exactly what I need. Thanks Roberto!

Best Regards,

Greg

[RobertoBozzolo]

I'm glad I could help. 

It's not that you've been unclear: I observed that almost all users posting questions related to Excel is using Excel report instrument and since I never used it I always point out that I cannot help with it.