'Group by' facility?

[Steve W]

Hi,

I've a lot of csv files to process. In each one I need to summ some columns
where one or two other columns are the same.
In a database table this would be easy using a SQL 'group by' statement.
Is there a similar method I could use in VBA?

Thanks in advance
SW

 

[Rob van Gelder]

Pivot tables are about as close as you'll get. Unless you want to treat the
same workbook as a Jet SQL source and query it.

 

[SW]

Treating it as a Jet SQL source sounds useful. How do I do that?

Thanks
SW

 

[Jamie Collins]

Treating it as a Jet SQL source sounds useful. How do I do that?

Sub just_four_lines()
Dim rs As Object
Set rs = CreateObject("ADOR.Recordset")
rs.Open _
"SELECT COUNT(*) as customer_count," & _
" Country FROM MyFile#csv" & _
" GROUP BY Country" & _
" ORDER BY COUNT(*), Country", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyFolder\;" & _
"Extended Properties='Text;HDR=Yes';"
Sheet1.Range("A1").CopyFromRecordset rs
End Sub

For more details <g>, see:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/scripting03092004.asp

Jamie.

--