We don’t always get to choose the technology we work with in the Power Platform world. Strict security, missing subscriptions, long approvals… if you’ve ever worked on the government projects, you know the drill.
However, sometimes, this leads to a very unusual combination of tools and technologies, and, in this series of posts, I’m going to talk about one such case.
I’ve been working on a project where, as usual, there is a need for document templates. However, the templates are relatively complex, so classic document templates in Dataverse are just not good enough. There are word templates in Power Automate, and that’s often a good alternative, but they are somewhat tricky to manage and support for the end users.
With all that said, we had been trying to get our heads around the problem of document templates for a little while, and, suddenly, the business team mentioned that they did have some templates… it’s just that those templates were done using VBA Macros in Word. Moreover, those macros knew how to read data from an excel spreadsheet, so the templates were not only very flexible (since you can do almost anything with a macro), but they were also connected to the data.
At first I thought, well, that’s a completely different world, how does it help… but, then, I thought a bit more, we talked to the business team, we looked at the templates they had, and we ended up creating a templating engine using the following:
- A Power FX command bar button in the MDA
- A custom page – based dialog for choosing a template
- Which, in turn, calls A Power Automate cloud flow to generate a spreadsheet with the data
- Which gets stored in Sharepoint
- There is a javascript web resource with a function that gets called when a user clicks the button above, and there are a few other functions there to download the template at the end
- And there is a set of word template files with VBA macros in them. Each teamplate can be as complex as needed, it essentially has access to the “live” data (more exactly, to the data exported seconds ago), and it can be maintained by the business team. This is assuming, of course, that the business team knows how to create such templates and how to code in VBA (and, in our case, they do have that expertise)
This solution touches on quite a few things in Power Platform, so I figured it would be worth sharing how it works. For example, have you ever tried passing parameters to a custom page (other than entitytype and recordid)? Or how about figuring out that mysterious driveid and fileid for a newly created file in Sharepoint?
Besides, you may even find the whole solution useful, so let’s dive into the details.
But, first, here is a short demo recording in which I’ll open a record in the MDA, I’ll click “New Document” button, there will be a popup dialog where I’ll choose a template, and, after a few seconds, that template will be downloaded. Once I open it, it’ll recognize the location of the data file in Sharepoint, it’ll read that file, and it’ll update a few bookmarks in the document with the data from the generated spreadsheet.
If you are looking for the source files, check out this repo on github: https://github.com/ashlega/TemplatesDemo
Finally, to make it easier to navigate through this series of posts, here are all the links: