Cannot trigger Power Automate Request trigger from Excel VBA

Question

I've made a process in Power Automate which I want to launch from VBA. The flow responds to a HTTP request and is working fine in chrome using the code:

http = new XMLHttpRequest;
http.open("POST","https://prod-32.westeurope.logic.azure.com:443/workflows/####/triggers/manual/paths/invoke?####");
http.setRequestHeader("Content-Type","application/json");
http.send('{"identity":"test","usage":["asdf"]}');

In chrome this code executes successfully. I also tested this code:

In internet explorer, from a webpage which is a file on the desktop - this fails with "Access is denied"
In internet explorer, from a website e.g. azure.com - this succeeds.

In VBA I do the following:

Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
Call http.Open("POST", "https://prod-32.westeurope.logic.azure.com:443/workflows/####/triggers/manual/paths/invoke?####", False)
Call http.setRequestHeader("Content-Type", "application/json")
sData = "{""identity"":""test"",""usage"":[""asdf""]}"
Call http.send(sData)

When running the code from VBA, there is a lag spike, and then VBA responds with "The operation timed out" looking in Power Automate, there is no evidence that the HTTP Request was ever received as the request does not appear in the run history. Are there any caveats to using the HTTP requests in Microsoft flow?

Perhaps this is because like IE VBA is not "online" and thus is getting a CORS error? Not really sure how I can debug this issue... Any ideas?

Edit: A bit of an update on this, I believe this might be because of my company's proxy that is used. Unfortunately I'm not sure WinHttp.WinHttpRequest supports this, but I know Tim Hall's web request library does appear to.

Sancarn · Accepted Answer · 2019-12-09 18:17:07Z

It's not a full answer as I'm still uncertain what is actually happening nor a proper way around this problem. However I did think of a work around which works for me:

Private Sub SendData(ByVal sMethod As String, ByVal sURL As String, ByVal sJSONData As String)
    On Error Resume Next
    Dim ie As Object

    'Launch IE and Navigate to azure
    Set ie = CreateObject("InternetExplorer.Application")
    Call ie.navigate("http://azure.com")

    'Wait for IE to load
    While ie.busy Or ie.readyState <> 4: DoEvents: Wend

    'Define javascript from params
    Dim sJavascript As String
    sJavascript = "javascript:void(0);window.http = new XMLHttpRequest;http.open(""${method}"",""${url}"");http.onreadystatechange=function(){if(http.readyState==4) window.name=""DONE"";};http.setRequestHeader(""Content-Type"",""application/json"");http.send('${data}');"
    sJavascript = Replace(sJavascript, "${method}", sMethod)
    sJavascript = Replace(sJavascript, "${url}", sURL)
    sJavascript = Replace(sJavascript, "${data}", sJSONData)

    'Call the javascript
    Call ie.navigate(sJavascript)

    'Wait to ensure data is sent
    Dim Window As Object
    Set Window = ie.document.parentWindow
    While Window.Name <> "DONE"
        DoEvents
    Wend

    'Read response
    Debug.Print "HTTP Response: " & http.status

    'Quit ie to save RAM
    Call ie.Quit
End Sub

Other users should be able to modify this to suit their own needs

Collectives™ on Stack Overflow

Cannot trigger Power Automate Request trigger from Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related