Access 2003 sp2 (count non blank rows in a spreashseet)

Topic

New Reply

I need to open a workbook from Access, count the number of non blank rows, and return the result to a table. I would call the table tblRowCount, and the field, Count.

I found the CountA function in Excel, but am honestly clueless how to impliment it in Access. Any help would be appreciated.

Reply | Quote

Replies

To count non-blank rows is an Excel question, not an Access question, but OK.
Do you want to look at a specific column to determine whether a row is non-blank, or at all columns?

Do you also need help with storing the value in the table?

Reply | Quote

Edited by HansV to provide link to post – see Help 19

Thanks for the suggestion Steve…I definitely should have known that.

Hans, I refer you back to a previous post you helped me with, post 574114 my function that doesn’t do anything. Well this is another step I need to add to give me something to reconcile too.

I import the data from a large number of spreadsheets and process it through a database. The last time I ran my process there were a few sheets that were not picked up. I ran them by themselves and they processed perfectly. I thought if I could count all the rows in each spreadsheet and put them into a table, I would then have a total record count to reconcile to against my Access table where all the records are imported. Then I could know if something failed.

The first column of each spreadsheet is what I will use. I created a macro in Excel to do the count, but I do not know how to put it into VBA in Access, nor do I know how to store the data from each count into a table. So all I have is the macro below.

Sub RowCount()
ActiveCell.FormulaR1C1 = “=COUNTA(R[-3]C[-9]:R[996]C[-9])”
Range(“J5”).Select
End Sub

Reply | Quote

I’ll post a reply a bit later today.

Reply | Quote

I would add a number (long integer) field RowCount to tblFileNames and fill this field while looping through the records in the table:

Public Function ConvertFiles()
Dim RS As DAO.Recordset, DB As DAO.Database
Dim strFileName As String
Dim xlObj As Object
Dim xlWbk As Object
Dim lngRowCount As Long

On Error GoTo ErrHandler

Set DB = CurrentDb
Set RS = DB.OpenRecordset(“tblFileNames”, dbOpenDynaset)
RS.MoveFirst
Set xlObj = CreateObject(“Excel.Application”)
xlObj.DisplayAlerts = False
Do Until RS.EOF
strFileName = RS!Folder & “” & RS!FileName
Set xlWbk = xlObj.Workbooks.Open(strFileName)
‘ -4162 is xlUp
lngRowCount = xlWbk.Worksheets(1).Range(“A65536”).End(-4162).Row
RS.Edit
RS!RowCount = lngRowCount
RS.Update
xlWbk.Close SaveChanges:=False
RS.MoveNext
Loop

ExitHandler:
On Error Resume Next
RS.Close
Set RS = Nothing
Set DB = Nothing
Set xlWbk = Nothing
xlObj.Quit
Set xlObj = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function

Reply | Quote

This works perfectly. Obviously I could not have even begun to do this without you. Many thanks.

Reply | Quote

A quick suggestion – do not call your field ‘Count’ – it is almost certainly a reserved word in Access/VBA and will come back to haunt you later.

Reply | Quote