Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Thanks
    chuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  3. #3
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    I'll post a reply a bit later today.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    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>post 574114</post#> 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
    Thanks
    chuck

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  7. #7
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •