Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    617

    Resolved [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.
    Last edited by VBAhack; Dec 4th, 2023 at 04:58 PM.

  2. #2
    Addicted Member
    Join Date
    Jul 2022
    Posts
    167

    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.

  3. #3

    Thread Starter
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    617

    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.
    Last edited by VBAhack; Nov 27th, 2023 at 12:05 PM.

  4. #4
    Addicted Member
    Join Date
    Jul 2022
    Posts
    167

    Re: ADODB/Excel VBA to aggregate data

    Well done!!

  5. #5

    Thread Starter
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    617

    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
    Last edited by VBAhack; Nov 30th, 2023 at 03:28 PM.

  6. #6

    Thread Starter
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    617

    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/
    Last edited by VBAhack; Dec 4th, 2023 at 04:57 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width