Count number of Excel rows to be imported 1

[RDS2]

Is there a way to count the number of rows that will be imported to an Access table using the Transfer Spreadsheet method?

I want to count the rows (in a selection) and display this in a message box so that I can cancel out of the routine if the row count is wrong.

 

[Remou]

You will get a trappable error if the sheet or range does not exist.

strSQL = "SELECT Count(*) As RecCount FROM [Sheet5$] " _
    & "IN '' [Excel 8.0;database=C:\Docs\LTD.xls;HDR=Yes];"
Set rs = CurrentDb.OpenRecordset(strSQL)

If Not rs.EOF Then
    MsgBox rs!RecCount & " found."
End If

Using a named range:

strSQL = "SELECT Count(*) As RecCount FROM Data_Range " _
    & "IN '' [Excel 8.0;database=C:\Docs\LTD.xls;HDR=Yes];"
Set rs = CurrentDb.OpenRecordset(strSQL)

http://lessthandot.com