Results 1 to 2 of 2
Thread: Querying a spreadsheet
2010-12-09, 11:31 #1
- Join Date
- Mar 2002
- Western NY, USA
- Thanked 0 Times in 0 Posts
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.
2010-12-09, 11:55 #2
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- Thanked 194 Times in 180 Posts
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:\Misc\ADO_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
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.Regards,
Microsoft MVP - Excel