[RESOLVED] ADODB/Excel VBA to aggregate data

[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.

[jdelano]

You might consider importing them into a table in an Access database, then you can run all your queries against the entire dataset without the need for arrays and massaging, you'll be able to create any manor of summation. Excel has built-in tools to pull data from Access.

[VBAhack]

In the end I figured out a way to implement my original idea. Key steps were:

(1) defining the data range in each workbook by updating a named range with the BeforeSave() event

(2) aggregating data ("SELECT CATEGORY, SUM(AMOUNT) FROM " & ExcelExportNamedRange & " GROUP BY CATEGORY") for each data workbook before adding to the disconnected in-memory recordset

(3) sorting the in-memory recordset by YEAR, then looping through the records and adding a column index parameter value for each row based on the value of YEAR

(4) sorting the recordset (rs.SORT = "CATEGORY") before using rs.getrows() to dump the records into an array

(5) looping through the array and populating a result array based on the CATEGORY value and value of the column index previously created

(6) writing the result array to an output area on a worksheet: Range(rOut).Resize(1 + k, 1 + nYears) = aResult

It turned out to be straightforward, largely due to being able to sort the in-memory recordset and using a column index field (for YEAR). Good project.

[jdelano]

Well done!!

[VBAhack]

After stumbling on an example using UNION to create a single query to multiple data sources (see below link), I accepted the challenge to streamline my project to eliminate separate queries to each source, then looping through each recordset to copy the records to a master disconnected recordset. Had to modify the query a bit to allow sorting of the resulting single recordset, but got it to work. Trouble is I couldn't modify the recordset to add a column index based on year, but eventually came up with a better alternative. Thanks to VBA's flexibility on array indexing, I created a small column index array using the values of year as the index and populated the array with column index values. For example aIdxCol(2022)=1, aIdxCol(2021)=2, etc. In the end, I still sorted the recordset twice, once to extract the year values in descending order, and the second time to sort the data by category to efficiently populate the result array. In both cases I used rs.getRows() after the sort and never did anything at the recordset level except sort and copy to arrays.

Worked well in the end, though I don't at all understand the use of the workbook (executing he VBA code) in the connection string. Maybe someone can explain that?

https://stackoverflow.com/questions/...e-in-excel-vba