Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record count of Excel spreadsheet (A2K)

    Is there a way to get the recordcount of unique records in a column of an Excel spreadsheet without importing it into Access?
    I need to know a count before I do some processing in my Access code.
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record count of Excel spreadsheet (A2K)

    There are several ways. You can link to the Excel spreadsheet, either using an ODBC link or using ADO code and then do a query on that column to return a a count of the unique values in it, or you can use ADO to open that recordset on the Excel range without linking it. Here's an example of SQL that should work in 2000 to give you a count:

    SELECT Count([customerID])
    FROM (Select distinct [CustomerID] FROM Orders);
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record count of Excel spreadsheet (A2K)

    Thanks, that gives me a starting point

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Record count of Excel spreadsheet (A2K)

    If you look in the excel board, you will find that someone wrote a neat procedure that does this in excel as well as other ways to do this in excel if you don't want to import the data to access.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record count of Excel spreadsheet (A2K)

    It would be extra nice of you to post a link to that thread in this one. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Record count of Excel spreadsheet (A2K)

    Since you asked....

    An easier way is described in <post#=207331>post 207331</post#>. Many thanks to Hans. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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