How to count number of rows in Excel?

[Paul]

I have a procedure in Access which can automate the batch importing of the
Excel files to an Access table. I have 159 Excel files and the batch
importing of the Excel files is working. What I need to do is to QA the
Excel files before and after the importing process? The things I need to QA
is the total number of the row in all Excel files and the sum of a column
(currency data type) on the excel file. The Excel files are alll on the same
format like the number of columns. My questions are how to count the number
of the rows and the summation of a column on each of the Excel file. Thanks.

 

[JohnFol]

You have several options, for example after import you can do either a
DSum/DCount combination or a Group By query to give the number of rows and
summation.

To QA before the import, your best bet is to open up the Excel file using
OLEAutomation. I'd be surprised if there was no method in Excel to count /
sum rows!

 

[Paul]

I am more interest to know how to QA BEFORE the import. As I mentioned there
are 159 Excel files it would be nice to automate this (counting the number
of rows and sumation of a column) instead of opening 159 Excel files one at
a time. Thanks

 

[JohnFol]

But you ARE opening one at a time in you automation otherwise, otherwise how
do you read the contents?

I am thinking the solution is somewhete along these lines

Dim myExcel As New Excel.Application
Dim fso As New FileSystemObject

Dim ExcelFile As File

For Each ExcelFile In fso.GetFolder("c:\").Files
If Right(ExcelFile.Name, 4) = ".xls" Then
myExcel.Workbooks.Open "c:\" & ExcelFile.Name
'Do the check to see how many rows there are .. ..
myExcel.ActiveWorkbook.Close


End If

Next ExcelFile

 

[JohnFol]

PS you could always attach/link the Excel files and use the same DCount /
DSum methods. .. . .