Excel VBA “Group by”-like selection

Question

I want to get data from an Excel worksheet in an SQL-like mode. Meaning, I have following columns: code, type, name, product_code, price, quantity.

What I want to do is get the data from this sheet like it was a db table and I execute the following:

select code, type, name, product_code, sum(price), sum(quantity) from table
group by code, type, name, product_code

How can I do this in VBA?

Answer 1

You can query the worksheet using Access SQL e.g. using ADO classic. However, because the data types are determined at run-time using the current state of the data and registry keys (which may result in values coerced to the wrong type and/or nulls where values cannot be coerced), it is perhaps better to import the data into a table in a SQL database so that data types can be specified at design-time, for which SQL Server is well suited.

Answer 2

SQLite for excel will let you do it in-memory. I'm not exactly sure what-all LINQ provides to the .NET crowd, but it seems you can emulate some of it using SQLite. I used it just enough to verify it works, but haven't gotten around to building a utility class around it.

Answer 3

Order your data by code, type, name, product_code, then write a loop that iterates through every row on the sheet, aggregating as it goes. When any of those four fields changes value, you're in a new group, so write out your current aggregate and start again on the current row.

There is not, to my knowledge, any way to write SQL against an Excel sheet in VBA; you just write the loop yourself.

Personally, I'd avoid VBA and use a pivot table.