In my [previous post], I talked about why I’m moving away from the VBA macros that served me so well since 1999. Today, I want to dive into the first real “head-scratcher” I encountered while designing this new system.
When you move from VBA to the Power Platform and Office.js, you aren’t just changing the programming language; you are changing the entire architecture of how a user interacts with a document.
The Old Way: The “Inside-Out” Approach
In the VBA era (1999-2004), the logic lived inside the document. You opened a .doc or .dot, a UserForm popped up, you filled it in, and the VBA code “pushed” the data into the bookmarks or fields. The document was the master.
The New Way: Two Paths to the Same Goal
As I’ve been mapping out this project, I’ve realized there are two primary ways to architect this. This is where I am currently experimenting, and both have significant pros and cons.
Option A: The “Embedded” Approach (Word Add-in + Power App)
In this scenario, we build a Word Web Add-in using Office.js. This creates a Task Pane (a sidebar) inside Word. Inside that sidebar, we embed a Power App.
- The Pro: It feels most like the “old” way. The user stays inside Word.
- The Hurdle: Getting the Power App to “talk” to the Word document through the Add-in layer. You need a “bridge” of JavaScript to pass data from the App to the Office.js API. It’s powerful, but it’s technically complex.
Option B: The “External” Approach (Power App First)
In this scenario, the user starts in a Standalone Power App (or via Teams). They enter all the data, and when they hit “Generate,” a Power Automate flow takes that data and populates a Word template stored in SharePoint.
- The Pro: Much easier to build. No need for complex JavaScript Add-ins. It works beautifully on mobile.
- The Hurdle: The “Word Online” connector in Power Automate is great, but it has limits. If you need complex “huisstijl” features—like dynamically adding rows to tables with specific formatting or nesting complex building blocks—it can struggle where VBA used to fly.
My Current Struggle: The “Huisstijl” Precision
This is where my background in corporate identity (huisstijl) kicks in. Back in 2001, we demanded pixel-perfect layouts.
If I go with Option B (Power Automate), I lose some of that “surgical” control over the document. If I go with Option A (Office.js Add-in), I get the control back, but the development time and complexity of the “bridge” between the Power App and the Word Canvas is much higher.
What I’m Testing Right Now
Right now, I am testing the Office.js Task Pane approach. Why? Because I want to see if we can truly replicate the “instant” feel of VBA. I want the user to see the document change in real-time as they toggle options in the Power App sidebar.
However, I’ve already run into my first major roadblock: Authentication (SSO). Making sure the Power App knows who the user is without asking them to sign in three different times is proving to be a challenge that we never had to worry about in 1999!
Next Steps
I’m currently digging into the “Office.js Document.Context” to see how far I can push the data from the Power App.
Are you facing the same choice? Do you prefer the user to stay in Word, or do you move the process to a standalone app? I’d love to hear your thoughts on where the balance lies between user convenience and technical complexity.
In Part 3, I’ll share the results of my “Bridge” test: Did I get the Power App to talk to the Word Canvas, or did I hit a wall?