Case Study 03 • Reusable Reporting Framework

ADODB ReportingFramework

A query-driven Excel/VBA tool that treated recurring CSV and XLSX files as data sources, turning many formula-heavy templates into selectable, formatted reporting workflows.

  • Sources30+ NYC files/month
  • Reports~20 definitions
  • Contexts3 account groups

Overview

Bring query logic to the files the team already received

Recurring NYC reporting depended on source files delivered as CSVs and Excel workbooks. Traditional templates copied those files into raw tabs and used formulas to shape the result, making changes difficult when headers moved or business rules evolved.

I built an Excel/VBA framework that used ADODB to execute Access SQL-style logic directly against folder-based files, then returned results to a formatted workbook for review and delivery.

Problem

One-off templates were expensive to change and easy to break

Formula-heavy workbooks coupled data retrieval, transformation, and presentation. Each variation could become another template, and column changes could break references throughout the file. The team also needed a safe way to use SQL-style thinking without relying on production database access for every transformation.

Changing layouts

Headers and column positions could move between source deliveries.

File-based inputs

Recurring work arrived primarily as CSV, with some XLSX-tab variations.

Safe environment

Transformations needed to avoid unnecessary use of production database systems.

Supportability

Generic ADODB errors and VBA complexity could make broader adoption difficult.

Approach

A small reporting application inside Excel

The framework used a raw-data folder for each run and a shared library of text query files. VBA dynamically listed available queries, let the operator choose a source folder, executed the selected ADODB logic, and wrote the recordset into a standardized workbook format.

  1. 01

    Select

    Choose the source folder and required report through the Excel interface.

  2. 02

    Load

    FileSystemObject reads the matching text-based query definition from the shared library.

  3. 03

    Execute

    ADODB applies SELECT, JOIN, GROUP BY, filters, UNION ALL, and aggregation logic to CSV or XLSX sources.

  4. 04

    Format

    CopyRecordSet returns the result to Excel and the template applies the standard customer-ready presentation.

Sanitized Evidence

A small architecture with replaceable query logic

Illustrative framework architecture

An operator selects a report and source folder. The framework loads a text query, executes it against approved files through ADODB, and writes the result to a formatted Excel output.

Conceptual architecture only. File names, query names, and report layouts are fabricated or generalized.

Key Decisions

Flexibility without pretending complexity disappeared

Store queries as shared text files

Reasoning

New or revised queries could be added to the library and discovered dynamically without changing the selector code.

Tradeoff

The query files became a governed dependency and required clear naming and change discipline.

Use ADODB query logic against flat files

Reasoning

The approach enabled SQL-style transformations where the actual inputs lived, without first copying every source into workbook tabs.

Tradeoff

Access SQL syntax and provider behavior differed from enterprise database platforms and produced generic errors.

Keep Excel as the operator and presentation layer

Reasoning

Excel was familiar to the reporting environment and supported final review, formatting, and customer-ready delivery.

Tradeoff

The framework remained a specialized tool, primarily used by its builder and selected testers rather than broadly adopted by the full team.

Tools & Validation

Query and file-processing stack

  • Excel VBA
  • ADODB
  • Access SQL
  • FileSystemObject
  • FileDialog
  • CopyRecordSet
  • CSV/XLSX

Validation approach

  • Expected inputs and schemas were checked before delivery
  • Query output was reviewed against known template results
  • Aggregations and joins were reconciled to source totals
  • Formatted outputs received an analyst review before sending

Result & Impact

From many template routines to a reusable report engine

30+NYC source files/month
~20selectable report definitions
3account contexts supported
1shared query library

The framework reduced dependence on workbook formulas and produced formatted outputs from reusable query definitions. Separating query logic from presentation made the owner’s changes more direct, while wider team adoption remained limited by workload and troubleshooting complexity.

Learnings

Reusable logic still needs an approachable support model

  • Separating transformation logic from workbook presentation made changes easier and reduced formula fragility.
  • SQL-style logic can improve file-based reporting without implying ownership of an enterprise database platform.
  • Dynamic query discovery made the framework flexible, but generic provider errors raised the knowledge required to support it.
  • A technically capable tool is not automatically a broadly adoptable one; documentation, diagnostics, and team capacity matter.

Additional context

This project is best understood as an advanced Excel/VBA reporting framework, not as database administration or enterprise software deployment. It processed approved file-based inputs and supported reporting workflows within the team's operating environment.