The writer of this question has already done the hardest part: they understand their constraints. A nationwide rollout with zero local support means the tool must survive contact with users who will do things you never imagined. The instinct to move logic out of VBA and into Power Pivot or DAX is the right one. Power Query handles ETL beautifully, but VBA introduces a failure point, a broken reference, a security setting, a user clicking "enable macros" at the wrong moment, that you cannot debug remotely. Power Pivot and DAX live inside the workbook's data model, not in code that can be tampered with or disabled. For a tool that must be bulletproof, that distinction matters.
The 30-page reporting nightmare is a separate problem with a clear answer. Excel objects driving Word via VBA will crash. You have already seen the instability. The document becomes heavy, the chains break, and you resort to static pasting. That is not a workflow; it is a trap. Power BI is the logical successor here, but not because it is trendy. It is because Power BI separates the data model from the presentation layer. Your 32-page report becomes a paginated report in Power BI, pulling from the same query you already built. The data refreshes, the formatting holds, and you never touch a Word document again. The learning curve to paginated reports is real, but it is shorter than the time you have already spent fighting Word.
Future-proofing for a national standard should prioritize Power BI over deeper VBA. The reason is not technical superiority alone; it is supportability. A Power BI solution can be centrally managed, version-controlled, and deployed without touching a single local machine. VBA expertise, no matter how advanced, still requires someone to maintain code on every workstation. When that someone is you and you cannot access those workstations, the model breaks. Master Power BI. Let Power Query handle the data. Let DAX handle the logic. Let VBA handle only what must happen locally, and keep it minimal. That stack scales. The current approach does not.