[RESOLVED] ADODB/Excel VBA to aggregate data and create report
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),
Code:
"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:
Code:
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.
Code:
2022 2021 2020
Food 500 352 756
Travel 1500 8000 4300
Misc 34 856 345
etc.
Re: ADODB/Excel VBA to aggregate data
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.
Re: ADODB/Excel VBA to aggregate data
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.
Re: ADODB/Excel VBA to aggregate data
Re: ADODB/Excel VBA to aggregate data
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
Re: ADODB/Excel VBA to aggregate data and create report
Third time must be a charm in terms of code streamlining. Discovered how to use TRANSFORM and PIVOT to aggregate the results to the final form I wanted and copy directly to the worksheet using Range.CopyFromRecordset(). The key was figuring out how to use TRANSFORM and PIVOT together with UNION, which I found in the below link. No sorting (other than ORDER BY in the SQL query), no writing to arrays or to an external database, and no looping (except to create the query string with UNION's and writing the recordset field names to the result area). SQL is powerful stuff.
https://www.mrexcel.com/board/thread...-pivot.237331/
