How to run Power Automate from Excel with Office Scripts or VBA

For a long time, I’ve been asking Microsoft for the ability to run a Power Automate flow from an Office Script. Office Scripts are restricted to operate only on objects within the workbook. Therefore, we can’t currently run a script that changes another workbook, or undertakes other activities outside of Excel. This significantly reduces the automation potential for Office Scripts.

However, in this post, I will share a method that we can use to run a Power Automate flow from Excel using Office Scripts or VBA.

NOTE: At the time of writing (January 2023), Office Scripts are included in Excel 365 E3 and E5 licenses and are available for Excel Online, and Excel Desktop insider channel (but I’m sure it will come to all 365 users soon).

Table of Contents

Watch the video

Trigger a Power Automate Flow from Excel with Office Scripts or VBA | Excel Off The Grid

Watch the video on YouTube

Why run Power Automate flow from Excel?

For a long time, Excel and VBA have been the development platform for many workplace warriors trying to automate their tasks.

The last major VBA update was in 2007. Since then, Microsoft has made a significant shift towards online rather than desktop solutions. While VBA continues to be supported, over time, it will become less and less useful as more companies move towards online solutions. In addition, more companies fear the security risk from VBA, so there is a gradual movement towards turning VBA off entirely.

So what alternatives do end users have? Office Script and Power Automate appear to be the most likely contenders. Office Scripts provides the detailed Document Object Model for us to work within Excel, while Power Automate provides the cross-application integration.

We can already run an Excel Office Script from a Power Automate flow, as shown in this post: Use Office Scripts with Power Automate. But currently, there is no official method to trigger a Power Automate flow from Excel. But thanks to a video by Talia Cao and a bit of research, I want to show you how to create the functionality with the existing tool set.

Setting up the Power Automate flow

Let’s start by creating the Power Automate flow.

Create an Instant Cloud Flow with when an HTTP request is received as the trigger.

When a HTTPS request is received

The fields in this trigger are:

  • HTTP POST URL: Once we click save, a unique URL is generated
  • method: A drop-down list from which we can select various Web calls (GET, PUT, POST, PATCH and DELETE). For our purposes, GET is the best option.
  • relativePath: This provides a way to pass parameters back to Power Automate from the URL string. These parameters are optional and can be excluded entirely if not required. The syntax of this section is as follows:
    {Parameter1}/{Parameter2}/{Parameter3}… etc
    In the screenshot above: Parameter1 = {User}, Parameter2 = {Application}

NOTE: When an HTTP request is received requires a Power Automate premium license.

Complete the remainder of the flow as required. For the sake of illustration, I have simply added an email send. The {User} and {Application} parameters created above are included in the email body.

Step 2 in flow - send email

After clicking save, the HTTP POST URL field changes to a unique URL. Copy this URL.

URL Path

WARNING: Do not share the URL. It is an open unique URL, that anybody could run, from anywhere.

Editing the URL for parameters

In Excel, paste the URL into a cell. For our example, we will use Cell B2.

In the middle of the URL, you will notice it contains the parameters we set up.

https://......./{User}/{Application}.......

As the URL is a text string, we can either:

  • Change the values directly in the URL
  • Use Excel’s text functions to change the values dynamically

For our illustration, I will simply change the URL to include Mark instead of {User} and Excel instead of {Application}.

https://……./Mark/Excel…….

If the values we provide in the URL include any special character (including spaces), we need to encode those characters. For example, the space character for URL encoding is %20, therefore Excel Off The Grid would become Excel%20Off%20The%20Grid.

For this, we can use Excel’s ENCODEURL function. Here is an example:

=ENCODEURL("Excel Off The Grid")

The formula above returns Excel%20Off%20The%20Grid

However, ENCODEURL is not available in Excel Online, so you may need a different approach. Check out this post for a full list of the encoding characters: https://www.w3schools.com/tags/ref_urlencode.asp

Running the hyperlink directly from Excel opens up a new browser window to execute the flow. This means we then need to close the browser window. It would be better to run the URL without opening a browser at all. We can do this with Office Scripts.

Running Power Automate from an Office Script

OK, now let’s make the URL execute from an Office Script.

In Excel, click Automate > New Script

Automate New Script in Excel

In the code editor, enter the following code:

function main(workbook: ExcelScript.Workbook) {

    let httpRequest = new XMLHttpRequest();
    let myPath = workbook.getActiveWorksheet().getRange("B2").getText();
    httpRequest.open("GET", myPath, false);
    httpRequest.send(null);

}

Name the script and save it.

Office Script code

We could click Run to execute the script as it is; however, wouldn’t it be nice to run this from a button?

  • Select a cell in the worksheet where to place the button.
  • Click the . . . (more options) button, then select Add Button.
    Add Button to run script

The button appears on the worksheet. Click the button to execute the script, which then runs the Power Automate flow.

Running Power Automate from VBA

If you would prefer to execute Power Automate from VBA, that is possible too. Use the following code:

Sub RunPowerAutomate()

Dim myPath As String

myPath = Sheets("Sheet1").Range("B2").Value

With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", myPath, False
    .Send
End With

End Sub

Conclusion

Using the when an HTTP request is received trigger is a flexible method for running a Power Automate flow from many different applications. Using this trigger with Office Scripts opens up more automation possibilities directly from Excel.


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

3 thoughts on “How to run Power Automate from Excel with Office Scripts or VBA”

    • Hi Michael – apologies, that is an error on my part. Because the examples use an open URL, I’m not able to share anything. I have corrected the YouTube description.

      You can still copy and paste the VBA and Office Scripts code from the blog post.

      Reply

Leave a Comment