I completely understand you: it feels like the ground is shifting because the only thing you’ve mastered is a language that is no longer anyone’s priority. It’s frustrating to work with a solid foundation but feel like you’re building on sand, especially when company restrictions prevent you from trying the new tools everyone praises.
Power Query and Power Automate Desktop (RPA)
For your particular situation, where IT security is strict and you can’t install Python or use the entire cloud ecosystem, the answer isn’t “learn something new,” but rather “enhance what you already have.”
Power Query is a data transformation engine that already lives inside Excel; you don’t need to install anything. Its function is to replace all that complex VBA logic you used to clean, filter, and consolidate tables. Instead of writing code to find rows or remove duplicates, you configure visual steps that repeat themselves automatically every time you refresh the file.
Power Automate Desktop is a Robotic Process Automation (RPA) tool that comes integrated with Windows 10 and 11. Unlike Office Scripts (which requires Excel Online), this software runs directly on your computer, interacting with the apps you already use: Excel, Outlook, and File Explorer. It’s the perfect solution to automate what VBA did well (generating PDFs and sending emails) but without relying on fragile macros.
2. The Problem
- Replace consolidation with Power Query: Forget about VBA
For Eachloops to merge files. Use the “Get Data from Folder” function. This allows you to simply drop new files into a folder, and Excel will automatically process, clean, and consolidate them. If an input format changes tomorrow, you only need to correct one step in the query—no need to rewrite code. - Standardize your snapshots with Excel Tables: To prevent automation from breaking, avoid fixed ranges (like
A1:Z100). Always convert your reports into “Tables” (Ctrl+T). This makes your structure dynamic; if the report grows, Power Query and Power Automate will detect it without you having to adjust anything manually. - Automate the output with Power Automate Desktop: Instead of programming a complex macro to export to PDF and send via Outlook, create a flow in Power Automate Desktop. You can “record” the action: open Excel, go to File > Export as PDF, and then use the “Send email through Outlook” action. It is visual, robust, and doesn’t require admin permissions to function within your corporate environment.
- Centralize the workflow: Set up a Windows scheduled task to run this Power Automate flow at your convenience (for example, every Monday at 8:00 AM). This way, you achieve the same result as your current stack but with a modern architecture that is easy to maintain and does not depend on a language in “maintenance mode.”
3. Suggested Tool
Power Query and Power Automate Desktop.
4. Step-by-Step Solution
(Refer to the detailed steps in the section above).
5. Prompt to copy:
Act as an expert PMO process automation consultant. I have a VBA-based workflow that performs the following tasks: [briefly describe your macro, e.g., consolidates 5 Excel files, generates a PDF dashboard and sends it via Outlook].
Due to security restrictions at my company, I cannot use Python or have full access to Excel Online. I need you to design a roadmap to migrate this process using exclusively Power Query (for data cleaning and consolidation) and Power Automate Desktop (for PDF generation and sending emails via Outlook).
Please detail for me:
1. The logical steps I should configure in Power Query to replace my current macro's logic.
2. A list of the specific actions (steps) I should program in Power Automate Desktop to automate report output and email delivery.
3. How to ensure the process is resilient to changes in the source data.