Project Overview
The client's financial operations team spent hours every morning manually downloading messy CSV reports from various payment vendors. They relied on a series of brittle, locally stored VBA macros to format headers, highlight discrepancies, and generate a daily pivot table dashboard. This manual process was error-prone, tied to specific Windows machines, and frequently crashed when handling large datasets.
MetaDesign Solutions architected a modern, zero-touch cloud automation pipeline. We migrated the legacy VBA logic into secure, modern Office Scripts (TypeScript) and orchestrated the entire workflow using Microsoft Power Automate, eliminating human intervention entirely.
Power Automate Orchestration
We built a completely automated cloud workflow using Microsoft Power Automate. The flow automatically triggers when vendor emails arrive, securely extracts the CSV attachments, and saves them directly to a designated SharePoint document library, entirely bypassing the need for manual downloads.
VBA to Office Scripts (TypeScript) Migration
Our engineers translated the complex, legacy VBA macro logic into modern Office Scripts written in TypeScript. This secure, cloud-native code executes directly against the Excel workbook stored in SharePoint, normalizing messy headers, cleansing invalid data, and applying strict conditional formatting.
Have a similar challenge?
Our experts can help you build custom integrations and plugins tailored to your business workflows.
Zero-Touch Dashboard Generation
The final step in the Office Script automatically refreshes the connected pivot tables and charts. Once the script execution completes, Power Automate sends a notification to the finance team's Microsoft Teams channel with a direct link to the finalized, web-ready dashboard.
Key Challenges
Challenge 1
Local VBA macros frequently crashed Excel when processing files larger than 50MB, delaying daily financial reporting.
Challenge 2
The process required a human analyst to manually download email attachments and trigger the macros every morning.
Challenge 3
Legacy VBA code was undocumented and presented a massive security vulnerability within the corporate network.
Challenge 4
The existing workflow tied analysts to specific Windows desktop machines, preventing a transition to Mac or web-based work.
Results & Outcomes
The automated pipeline completely eliminated the 2-hour daily manual reporting bottleneck. The finance team now arrives at work with a fully processed, web-ready financial dashboard waiting for them. By deprecating the legacy VBA macros, the company significantly improved its security posture and empowered its workforce to operate seamlessly on any device via Excel on the Web.