Identifying the number of rows in Excel file

[Guest]

I have a module in Access that opens an Excel file and does some calculations
on the data but I need to know the number of rows to loop through for my
ForNext loop. Can you tell me how to figure out the number of rows? Thanks.

 

[OffInc]

I've used the following to return the first blank cell in a column:

Pass the filename, the worksheet number and the column letter. It should
count down the cells until it hits the first blank cell. The number it
returns is the number used in the cell address ie. (X, #)

Function ReturnTheFirstBlankCellInTheColumn(strFilename As String,
intWorksheetNumber As Integer, _
strColumnLetter As String) As Long

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim xlCell As Object
Dim lngNonBlanks As Long

ReturnTheFirstBlankCellInTheColumn = 0

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(strFilename)

With xlApp

Set xlSheet = xlBook.Worksheets(intWorksheetNumber)
Set xlCell = xlSheet.Cells

With xlSheet

.Activate

lngNonBlanks = 0

For Each xlCell In .Range(strColumnLetter & ":" &
strColumnLetter)

'***This is where it looks for the blank cell
If xlCell.Value <> "" Then

'***This cell is not empty so move to the next cell
lngNonBlanks = lngNonBlanks + 1

Else

'***This cell is empty so return the cell number (X,
#)
lngNonBlanks = lngNonBlanks + 1
ReturnTheFirstBlankCellInTheColumn = lngNonBlanks

Exit For

End If

Next xlCell

End With

Set xlCell = Nothing
Set xlSheet = Nothing

End With

xlBook.Save

'***Release the objects
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

End Function

 

[Guest]

I have a module in Access that opens an Excel file and does some calculations
on the data but I need to know the number of rows to loop through for my
ForNext loop. Can you tell me how to figure out the number of rows? Thanks.

xlsheet.UsedRange.rows.count