[RESOLVED] ADODB/Excel VBA to aggregate data and create report

[VBAhack]

Trying to use ADODB with Excel VBA (Office 2021) and connection strings to aggregate expense data (date, category, amount) from ~6 closed Excel workbooks into a summary showing amount by category for each calendar year (to get a year-by-year comparison). Each workbook has roughly 1k lines of data, contains data for a single calendar year and will have common and potentially unique categories (~50 total).

My current line of thinking is to successively aggregate the data of each workbook (already working),

"SELECT CATEGORY, SUM(AMOUNT) FROM " & ExcelExportNamedRange & " GROUP BY CATEGORY"

then combine them in a separate recordset that has three columns (year, category, amount). This means creating a new recordset and copying existing records in a loop for each workbook (I can determine the year from the first record or from the workbook file name). Given the small number of categories, this isn't a lot of data. Trouble is, I don't know what to do at this point to create the summary report. I think I could dump everything into an array and massage the data with VBA, but I'm hoping there is a more elegant way to do it.

Suggestions??

P.S.

The combined recordset will look like:

Year   Category  Amount
2022       Food     500
2020     Travel    4300
2021       Food     352
2020       Food     756
2020       Misc     345
etc.

The summary report will look like the below and reside on an Excel sheet. Note: some of the categories for some years will be zero.

        2022  2021  2020
Food     500   352   756
Travel  1500  8000  4300
Misc      34   856   345
etc.