Querying a spreadsheet

Topic

New Reply

I would like to extract and count distinct values from a range and sort by the Pareto. If I were using a DB this would be easy.

Select column2, column3, count(column3) from table where some criteria order by count(column3)

Is there a way to do this in Excel without macro’s? If not, I can write the macro.

Thanks.

Reply | Quote

Replies

If the source workbook is closed, then you can use ADO to query it:

   ‘ Sample demonstrating how to return a recordset from a workbook
   Dim strQuery As String, rst As Object, strConn As String
   Dim varData As Variant
   strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MiscADO_test2.xls;” & _
         “Extended Properties=””Excel 8.0;HDR=Yes;”””
   strQuery = “SELECT Test, COUNT(Test2) FROM [Sheet1$] GROUP BY Test ORDER BY COUNT(Test2);”
   Set rst = CreateObject(“adodb.Recordset”)
   rst.Open strQuery, strConn, 3, 1, 1 ‘adOpenStatic, adLockReadOnly, adCmdText
    ActiveSheet.Range(“A2”).CopyFromRecordset rst
   rst.Close
   Set rst = Nothing

for example. You can do the same with an open workbook but there are memory leaks so if you do more than a couple of queries you can run into problems.

Reply | Quote