Excel remains one of the biggest parts of thousands of organisations around the world. Macros housed in Excel are in some ways the precursor to the "citizen developer" approach that Microsoft has taken with it's Power Platform offering. Some of these macros are so ingrained in processes that they cannot be easily or quickly replaced, but that doesn't mean they cannot be enhanced and integrated with the Power Platform. In this post we will look at how you can use Power Automate to enhance your VBA solutions.
The Reason
Starting the move to the Power Platform is not without its difficulties, but this process could allow for that transition to be a bit easier. Having the ability to push our offline data into the Power Platform using Power Automate, can allow us to integrate with systems and applications in our Microsoft 365 environment, such as Teams, SharePoint, Dynamics 365 and Azure, as well as a host of other third-party applications. It allows us to create a portal from Excel into our online environment that we may not have had otherwise.
Sending a Single Object
Our first example is to use a VBA userform to send a single object to Power Automate. We will then be able to manipulate the data to complete our process with a Teams integration.
To start with we will need to setup a new flow using the "When an HTTP request is received" trigger.
In order for our Flow to receive data from the VBA script, we will need a JSON schema. To create this, we will use Power Automate to build the schema for us. Click on "Use sample payload to generate schema".
By adding the below JSON, Power Automate will generate the full schema for us. Adjust to meet your needs, but it will need to be replicated in VBA.
{
"Title":"Title",
"Details":"Details",
"Date":"01/01/2020",
"Requester":"requester@email.com",
"Approver":"approver@email.com"
}
Once done, you will see the full schema in the trigger.
And we can now add our data points to variables.
Once we have saved the Flow, a request URL will be generated. Copy this, we will need it later.
In Excel, open your Visual Basic Editor (either through the Developer tab or by pressing "ALT" + "F11"). Here I built a simple userform that we can fill in and trigger our script from.
In the userfrom we will use the following script.
The important aspects of the script are the actual HTTP request lines, and the JSON schema that is built, otherwise you can adjust the script to whatever is needed.
Building the JSON Body:
reqBody = "{'Title':'" & reqTitle & _
"','Details':'" & reqDetails & _
"','Date':'" & reqDate & _
"','Approver':'" & reqApprover & _
"','Requester':'" & reqRequester & "'}"Send the HTTP Request:
'Assign request url to variable
reqURL = "<YOUR REQ URL>"
'Create HTTP Oject
Set req = CreateObject("MSXML2.XMLHTTP")
'Send HTTP request
req.Open "POST", reqURL, False
req.setRequestHeader "Content-Type", "application/json"
req.send reqBodyThis is my test script:
Option Explicit
Private Sub UserForm_Initialize()
Dim AppOutlook, ExchUser As Object
'Get Signed in user email address
Set AppOutlook = CreateObject("outlook.application")
Set ExchUser = AppOutlook.Session.AddressLists.Item("All Users").AddressEntries.Item(AppOutlook.Session.CurrentUser.Name).GetExchangeUser()
'Add today's date and signed in user to the Userform
lblSignedInUser = ExchUser.PrimarySmtpAddress
lblReqDate = Date
'Initialise userform in centre of Excel window
Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
Me.Top = Application.Top + (0.5 * Application.Height) - (0.5 * Me.Height)
End Sub
'**********************************************************************
'Submit HTTP Request
Private Sub btnSendHttpReq_Click()
Dim req As Object
Dim reqURL As String
Dim reqBody, reqTitle, reqDetails, reqDate, reqApprover, reqRequester As String
'Create HTTP Oject
Set req = CreateObject("MSXML2.XMLHTTP")
'Assign request values to variables
reqTitle = txtReqTitle
reqDetails = txtReqDetails
reqDate = lblReqDate
reqApprover = txtApproverEmail
reqRequester = lblSignedInUser
'Build JSON body
'{
' "Title":"Title",
' "Details":"Details",
' "Date":"01/01/2020",
' "Requester":"requester@email.com",
' "Approver":"approver@email.com"
'}
reqBody = "{'Title':'" & reqTitle & _
"','Details':'" & reqDetails & _
"','Date':'" & reqDate & _
"','Approver':'" & reqApprover & _
"','Requester':'" & reqRequester & "'}"
'Assign request url to variable
reqURL = "<YOUR REQ URL>"
'Send HTTP request
req.Open "POST", reqURL, False
req.setRequestHeader "Content-Type", "application/json"
req.send reqBody
End SubWhen we fill in the userform and send our request the Flow will be triggered and we will be able to process our request.
Now that we have the connection established, we can expand our workflow. For this request we want to seek approval, so we'll add the Teams action "Post a choice of options as the Flow bot to a user", and once we have a response from our approver, we can inform the requester of the decision using "Post a message as the Flow bot to a user".
These can be adjusted as needed. You will also be able to add approver comments to your output.
When we save the Flow and rerun the process, the approver will get a first message from the Flow bot asking if they approve:
And the requester will receive a second message with the approver decision.
Sending an Array
The main process of sending an array is the same. The thing that changes is our JSON schema. We need to generate the schema for an array of items, and we need to build our JSON to match our schema. In this example we will be sending an entire table of data to our flow, rebuilding it as an HTML table and sending it in Teams to be reviewed.
To generate the schema, we will do exactly the same thing, except we'll add in square brackets around our schema "[]". This tells Power Automate to generate the schema for an array.
[{
"Title":"Title",
"Details":"Details",
"Date":"01/01/2020",
"Requester":"requester@email.com",
"Approver":"approver@email.com"
}]
Once our schema is setup, we can add our trigger body directly to our HTML Table composition.
To send our array into flow we will grab our table, cycle through it and build our JSON body. I have not been able to find an out of the box "Convert to JSON" function for VBA, either you can create your own, or if you can find a better way to do this then let me know.
Building our JSON Body:
For i = 2 To lastRow
reqTitle = Cells(i, 1).Value
reqDetails = Cells(i, 2).Value
reqDate = Cells(i, 3).Value
'Build JSON body
reqBody = reqBody & _
"{'" & headerTitle & "':'" & reqTitle & _
"','" & headerDetails & "':'" & reqDetails & _
"','" & headerDate & "':'" & reqDate & "'}"
'Add "[" <JSON> "]" when we hit the last row, otherwise add ","
If i = lastRow Then
reqBody = "[" & reqBody & "]"
Else
reqBody = reqBody & ","
End If
NextOnce the JSON body has been built, it is sent in the same way as our single object:
'Send POST request
req.Open "POST", reqURL, False
req.setRequestHeader "Content-Type", "application/json"
req.send reqBodyThis is my test script:
Private Sub btnSendHTTPReq_Click()
Dim req As Object
Dim reqURL As String
Dim reqBody As String
Dim headerTitle, headerDetails, headerDate As String
Dim reqTitle, reqDetails, reqDate As String
Dim lastRow As Long
Dim i As Long
'Create request object
Set req = CreateObject("MSXML2.XMLHTTP")
'HTTP request URL
reqURL = "<Req URL>"
lastRow = Cells(1, 1).End(xlDown).Row
'Get JSON headers
headerTitle = Cells(1, 1).Value
headerDetails = Cells(1, 2).Value
headerDate = Cells(1, 3).Value
For i = 2 To lastRow
reqTitle = Cells(i, 1).Value
reqDetails = Cells(i, 2).Value
reqDate = Cells(i, 3).Value
'Build JSON body
reqBody = reqBody & _
"{'" & headerTitle & "':'" & reqTitle & _
"','" & headerDetails & "':'" & reqDetails & _
"','" & headerDate & "':'" & reqDate & "'}"
'Add "[<JSON>]" when we hit the last row, otherwise add ","
If i = lastRow Then
reqBody = "[" & reqBody & "]"
Else
reqBody = reqBody & ","
End If
Next
'Send POST request
req.Open "POST", reqURL, False
req.setRequestHeader "Content-Type", "application/json"
req.send reqBody
End SubNow when we run our script, our table will be turned into JSON and sent to our Flow.
Once our connection has been established, we can send our table in Teams.
We'll add the table to teams with the following formula - this will give our table boarders and lines:
@{replace(body('Create_HTML_table'),'<table>','<table border="2" align="center">')}
And when we rerun our completed solution, we receive the table in a Teams message.
In Conclusion
While this is not a solution that everyone would need to use, if VBA is an entrenched part of your organisation's processes, then this solution can help expand the functionality of these processes. Once you have built that connection, you are limited only by your imagination and the connections you can use and create in Power Automate.
Comments