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

[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

[VBAhack]

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/