Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


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

Run Power Automate From Excel

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).

Watch the video

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.


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

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

  1. Michael says:

    FYI, Your related YouTube video lists the link where example file can be found. However, unless I am missing it, I do not see it.

    • Excel Off The Grid says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *