How to query total number of columns and rows populated with data?

[NiCoder]

How to get the number of rows and columns in Exel data file using Excel report?

[KyleP]

Hello NiCoder,

I found this LabVIEW example as a good starting point. Here's a VI snippet that shows the appropriate code:

After you've created an ActiveX Controller for the Microsoft Excel Object Library, you'll want to start with the function WorksheetGetUsedRange. From here you can query the number of rows and columns in that range.

Cheers,

[dcl9000 · Accepted solution]

Since you posted this question in the LabWindows/CVI forum, I assume you want to know how to do it using CVI.

You should know how to open and activate the Excel data file.

Following function will return the total number of columns and rows to col_count and row_count, respectively.

-----------------------------------------------------------------------------------------------------------------------------------------

int CountColumnsAndRows(void)
{
   HRESULT  error=0 ;
   CAObjHandle  rangeCurrentRegionHandle = 0 ;
   CAObjHandle  rangeColumnsHandle = 0 ;
   CAObjHandle  rangeRowsHandle = 0 ;
  
   unsigned long col_count=0, row_count=0;
  
   // Must use "A1" and CruuentRegion property to count the total columns and rows, including blanks!
  
   error = CA_VariantSetCString (&MyCellRangeV, "A1");
  
   error = Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyCellRangeV, CA_DEFAULT_VAL, &ExcelRangeHandle);
   if (error<0) goto Error;
  
   error = Excel_GetProperty (ExcelRangeHandle, &ErrorInfo, Excel_RangeCurrentRegion, CAVT_OBJHANDLE, &rangeCurrentRegionHandle);
   if (error<0)  goto Error ;
  
   error = Excel_GetProperty (rangeCurrentRegionHandle, &ErrorInfo, Excel_RangeColumns, CAVT_OBJHANDLE, &rangeColumnsHandle);
   if (error<0)  goto Error ;
  
   error = Excel_GetProperty (rangeColumnsHandle, &ErrorInfo, Excel_RangeCount, CAVT_LONG, &col_count);
   if (error<0) goto Error;
  
   error = Excel_GetProperty (rangeCurrentRegionHandle, &ErrorInfo, Excel_RangeRows, CAVT_OBJHANDLE, &rangeRowsHandle);
   if (error<0)  goto Error ;
  
   error = Excel_GetProperty (rangeRowsHandle, &ErrorInfo, Excel_RangeCount, CAVT_LONG, &row_count);
   if (error<0) goto Error;

  
  
   Error:

   CA_VariantClear(&MyCellRangeV);
   CA_VariantClear(&MyVariant);
   ClearObjHandle (&ExcelRangeHandle);
   ClearObjHandle (&rangeCurrentRegionHandle);
   ClearObjHandle (&rangeColumnsHandle);
   ClearObjHandle (&rangeRowsHandle);
  
 
   if (error < 0)
      ReportAppAutomationError (error);
  
   return error;
}