Architecture for two separate projects: A Nationwide Template and an Automated 30+ Page Report

I am designing a data solution that needs to scale from a local proof-of-concept to a nationwide implementation. I need advice on the most stable "tool stack" to ensure reliability for non-technical end-users.

Technical Proficiency / Skill Set:

• Power Query: Self-taught, comfortable with ETL and merging multiple data sources.
• VBA: Basic/Intermediate (primarily used for automation, often AI-assisted).
• Web/Coding: Very basic HTML/CSS knowledge, but no JavaScript/Python experience.
• Context: I have a logical workflow mindset, but I am seeking more of the "industry standard" to move away from manual, repetitive processes.

Project A: The Nationwide Data Template (Standalone Tool)

• The Goal: Build a data setup to be implemented nationwide for other court locations.
• Input: Must process CSV files from a central authority (no direct database access due to high security).
• Users: Staff at all technical levels. They should only interact with 1-3 specific "result" sheets. The plan is that they will be told only to use one specific folder and of course the sheets will be protected.
• Constraints: I will have no access to their local folders or machines for troubleshooting. The tool must be "bulletproof" and handle data refreshes without user-side errors.
• Question: Is Power Query + VBA robust enough for a rollout with zero local support, or should I move the logic to Power Pivot/DAX to reduce the risk of users "breaking" the tool?

Project B: The 30+ Page Reporting Nightmare (Internal Workflow)

• The Goal: Automate a 32-page yearly report that I personally produce.
• The Problem: Currently, I manually copy/paste tables and charts from Excel into Word. I started with keeping the chains but the document became so heavy it was near-crashing, and because it's so unstable, I've had to break the chains and make the data static.
• The Requirement: I need a smarter "bridge" between Excel and a formatted report (Word or PDF) to replace the manual "Ctrl+C / Ctrl+V" marathon.
• Question: What is the industry standard for this? Is this where I move to Power BI, or is there a reliable way to automate Word via Excel objects that won't crash the document?

Technical Questions:

1. Stability: For a rollout with zero local support, is a Power Query + VBA combination robust enough? Would moving the logic to Power Pivot/DAX improve stability and reduce "breakability" for end-users?
2. Reporting Pipeline: What is the industry standard for pushing large amounts of dynamic Excel data into a formatted 30-page report? Is Power BI the logical successor here, or is there a reliable way to automate Word via Excel (e.g., Content Controls or specific VBA objects) that won't crash?
3. Future-proofing: Given the need for a national standard, should I prioritize mastering Power BI or deepening Advanced VBA/Data Modeling within Excel to solve these specific deployment issues?
   

Excel Version: Microsoft 365 (Build 17928.20440), Semi-Annual Enterprise Channel.