Record count of Excel spreadsheet (A2K)

Topic

New Reply

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

Reply | Quote

Replies

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);

Reply | Quote

Thanks, that gives me a starting point

Reply | Quote

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.

Reply | Quote

It would be extra nice of you to post a link to that thread in this one.

Reply | Quote

Since you asked….

An easier way is described in post 207331. Many thanks to Hans.

Reply | Quote