On Aug 13, 10:13*am, arjen van... wrote:
It can be done using SQL and embedding the SQL query in a VBA procedure
(using ADO). It requires adding a reference to the ADO library in your
project. Tools References Microsoft ActiveX Data Objects Library.

This was done in Excel 2003, using the Acess 2003 database engine (JET). I
hope this formats okay.

Option Explicit

Sub QueryExcel()

* * 'create the connection string
* * Dim ConnectionString As String

* * ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
* * * * * * * * * * * * * * * * "Data Source=K:\Excel\jun09\Aug13.xls;" & _
* * * * * * * * * * * * * * * * "Extended Properties=Excel 8.0;"

* * 'create the sql query
* * Dim MyQuery As String

* * MyQuery = "SELECT Account, SUM(Amount) " & _
* * * * * * * * * * *"FROM [DataSheet$] " & _
* * * * * * * * * * *"GROUP BY Account " & _
* * * * * * * * * * *"ORDER BY Account"

* * 'create the recordset
* * Dim MyRS As ADODB.Recordset
* * * * Set MyRS = New ADODB.Recordset

* * 'open the recordset
* * MyRS.Open MyQuery, ConnectionString, adOpenStatic, adLockReadOnly,
adCmdText

* * ThisWorkbook.Sheets("Summary").Activate
* * ActiveSheet.Range("A1").CopyFromRecordset MyRS

* * MyRS.Close
* * Set MyRS = Nothing

End Sub

Note: You'll have to change the path in the connection string. Also the way
it's setup requires the original data & the summary you're creating to be in
different workbooks.

Thanks a lot. I'll try to make it work.