CA V MANMOHAN

Crafting an Excel VBA Prompt Builder
for Complex Automation Workflows

A privacy-first, browser-based architectural engine designed to bridge the gap between non-technical finance teams and world-class Excel automation.

Project Overview

The Excel VBA Builder eliminates the need to rely on fragile "record macro" features or expose sensitive corporate data to public AI platforms. It provides a structured visual interface guiding users through designing a complete Extract, Transform, Load (ETL) pipeline.

The engine generates a precisely structured, optimized prompt that a Large Language Model (such as ChatGPT or Claude) can use to produce bulletproof, enterprise-grade VBA code.

Key Outcomes Delivered

Native Windows PathingFolder pickers via Application.FileDialog
Dynamic Data StackingAutomated YTD master table consolidation
Automated DashboardsGeneration with KPI cards, pivot charts, and slicers
Paginated PDF ReportingStyled PDFs with cover pages & A4-fitted export ledgers

The 5 Phases of the Tool

Operating the Builder: A step-by-step pipeline to transform multi-hour manual monthly processes into a single-click operation.

1. Sheet Settings & Multi-File Ingestion

Environment & Ingestion

Upload your Excel files here. The tool reads column headers automatically. You choose between Standard mode (specific mapped files) or Consolidation / YTD loop mode (folder of monthly files).

Under the hood: Drag and drop a single sample source file into the dropzone. The engine instantly maps all column headers and intelligently infers data types (Currency, Date, Text) to generate type-safe transformation code.

2. Data Metric Tools

Auditing

Define data quality rules — flag duplicate invoice numbers, negative amounts, blank customer names, etc. These get routed to an Audit Log sheet automatically in the generated macro, ensuring pure data enters the final model.

3. Core Structuring

Transformation

Apply row-level cleansing. Transform rules: remove blank rows, deduplicate, unpivot columns, merge or split fields. For YTD analysis, you'll stack chronological rows (e.g., April + May) into one comprehensive master table here.

4. Output Objects, Dashboards & Reports

Architecture

Define your YTD pivot tables, charts, dashboard layout, and KPI cards. Add calculated fields, specify publication sheets, and set the visual theme.

Top BannerKPI Cards & aggregated metrics.

Main CanvasPivot Charts (Bar / Donut visualizations).

Left PanelSlicers & Timelines (interactive controls).

5. PDF Binder

Print Rendering

Optional: configure cover page, footer, page numbers, and generate a professional PDF export of the dashboard. Skip this for now and focus on the Excel macro first.

Advanced Rendering: Supports multi-page output with a styled cover page (title, subtitle, logo) that disables footers/gridlines exclusively on that page. Tabular export ledgers are automatically zebra-banded, fitted to A4 width, and configured to repeat headers.

Phase 6: The Output

The final step generates and copies the AI prompt. Simply paste the resulting VBA code into Excel (Alt+F11) and run.

Architecture & Guardrails

Strict development standards operate invisibly to ensure corporate stability, data privacy, and macro reliability.

Privacy-First Pathing

Never hardcodes local directories; uses runtime file dialogs for full corporate compliance.

Dual-Mode ETL Ingestion

Standard (mapped files) and Folder Consolidation (auto-loop & header-strip) modes.

The Holy Trinity of Speed

Auto-suspends ScreenUpdating, Calculation & EnableEvents for high-volume processing.

Safe-State Rollback

Catches runtime errors, logs to Error_Logs, and restores the workbook to its original state.

Idempotency & GC

Re-runnable without data duplication; explicit memory cleanup (Garbage Collection) prevents Excel leaks.

Auto-Backup

Saves a timestamped workbook snapshot before any macro execution begins.

Technical Summary

Application Type	Single-File Offline Web Application (HTML/CSS/JavaScript)
Output	Structured AI Prompt for VBA Code Generation
Target Environment	Microsoft Excel (configurable: 365, 2019, 2016)
ETL Modes	Standard File Mapping / Folder Consolidation Loop
Dashboard Outputs	KPI Cards, Pivot Charts, Slicers, Timelines
Report Outputs	Advanced PDF Binder with Cover Page and Export Ledger
Privacy Model	100% Local — no data transmitted to any server
Error Handling	Safe-State Rollback with Error_Logs sheet logging
Performance Engine	Holy Trinity: ScreenUpdating + Calculation + Events suspended