LabWindows/CVI

cancel
Showing results for 
Search instead for 
Did you mean: 

Excel: Get number of worksheets, and each worksheet name

Solved!
Go to solution

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

 

0 Kudos
Message 1 of 5
(3,999 Views)

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);
}

...

 

S. Eren BALCI
IMESTEK
Message 2 of 5
(3,984 Views)
Solution
Accepted by topic author Greg_NTI

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.



Proud to use LW/CVI from 3.1 on.

My contributions to the Developer Community
________________________________________
If I have helped you, why not giving me a kudos?
Message 3 of 5
(3,983 Views)

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

 

0 Kudos
Message 4 of 5
(3,963 Views)

I'm glad I could help. Smiley Happy

 

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.



Proud to use LW/CVI from 3.1 on.

My contributions to the Developer Community
________________________________________
If I have helped you, why not giving me a kudos?
0 Kudos
Message 5 of 5
(3,941 Views)