Power Automate (previously Microsoft Flow) is an automation tool that allows you to pass data between one or more services. It can be configured to run on various triggers such as: When a new SharePoint list item is created, or When a new Form response is received. Discover how to create apps and forms in Microsoft Power Apps, and automate workflows in Power Automate. Learn to streamline your business processes.
We’re pleased to announce that Microsoft Flow is now integrated into Excel. With this integration that uses the For a selected row trigger and the Flow launch panel, you can create and trigger on-demand flows for selected rows in any Excel table on spreadsheets hosted in SharePoint or OneDrive for Business. The Microsoft Flow for Excel add-in enables you to connect your data to a wide range of services such as SharePoint, Outlook 365, Dynamics 365, Teams, Visual Studio Online, Twitter, Approvals, etc. In this post, we’ll walk you through this new capability with a hands-on example.
To get started, in Excel, go to the Insert tab in the ribbon and select Store. Then, in the dialog, search for Microsoft Flow. Then, click Add.
Let’s imagine that you work for Cronus Energy, a multi-national energy production company, which generates energy through wind turbines and hydroelectric power plants. Cronus is on the lookout for better ways to streamline and standardize internal processes to make things easier for their employees. They’ve identified a key process they want to modernize:
Transfer market data entered by Commercial Analysts (minimum energy to generate, maximum energy, and target energy based on revenue goals) to SharePoint so that the Operations team can decide which turbines to use for the week. After moving the data to SharePoint, they also want to send an alert to the team on Microsoft Teams and facilitate a discussion if needed. The Operations team uses a SharePoint list called Turbine Energy Distributions with the columns shown below.
Their development team is already short on resources, so they want to be able to stand up the solution quickly while avoiding as much custom development as possible. Let’s see how Flow can help.
The Commercial Analysts at Cronus Energy enter market data in a spreadsheet hosted in SharePoint. To follow along, download this spreadsheet and upload it to SharePoint or OneDrive for Business.
To get started, click the Flow menu from under the Data tab in the ribbon.
This will open the Flow launch panel in Excel where you will be prompted to Sign in and consent to the permissions requested by the add-in. Click Accept.
Once you’ve signed in, you can explore several templates to quickly connect to a wide variety of services with minimal set up. Scroll down the screen and choose Create an item in SharePoint for a selected row.
Selecting the template will prompt you for your credentials and provide additional details about the template. Click Continue.
In the trigger (For a selected row), click the drop-down next to the Table field and select your table, e.g. Table1. The trigger may be collapsed; if so, click on Edit and confirm that the Table field is set to Table1.
The For a selected row trigger is similar to other manual triggers like the Flow button for mobile or SharePoint’s For a selected item – users can be prompted for inputs when they run the flow (Text, Yes/No, File, Email, or Number) and all flows run with the credentials of the invoker. For this flow, add a Text input called Message with the hint text of “Enter a message for the team.”
In the SharePoint-Create item action, enter the Site Address and List Name for Cronus Energy’s Turbine Energy Distributions List.
Click the Week field and select Week from the Dynamic content pane.
The parameters in the Dynamic content pane consist of your table’s columns – Week, Min Energy (mWh), Target Energy (mWh), Max Energy (mWh), Price ($/mWh), Revenue, and Profit, information about the person invoking the flow – Timestamp, User email, User id, and User name, and lastly any “manual” Outputs you add to the trigger like Message.
Repeat this for the Energy Target, Min Energy, and Max Energy.
Now, add a Microsoft Teams – Post message action. Choose a Team and Channel to post your message to. In the Message field, enter a link to the newly created item along with the Message populated by the flow invoker.
Save the flow by clicking the Save button.
Select a row in the table and then click Run flow in the Flow launch panel.
The first time you run this flow, you’ll be asked to confirm your credentials. You can also learn more about what this flow does. Click Continue.
Now enter a message to send to your team, requesting feedback. Click Run flow.
Voila! An item is created in SharePoint with details from the row you selected in Excel and a message is posted on your behalf including your note asking for feedback and a link to the item.
Now that you’ve created the flow, you can share it with colleagues either by adding them as an owner of the flow or as a run-only user. The latter allows you to maintain ownership of the flow, while enabling your colleagues to run it. In Flow, head over to My Flows and choose the Create an item in SharePoint for a selected row flow. Note – To run the flow, they must have access to the spreadsheet.
Under Manage Run-Only Users, click Add another person.
Here you can enter individuals, AD security groups, O365 groups, or even anyone that has access to the SharePoint list. For each connector used in the flow, you can decide whether the invoker should bring their own credentials (“Provided by run-only user”) or use your credentials (“Use this connection”). Click Save to add the user as a run-only user.
We hope you enjoy this new update. If you have ideas for templates or other feedback, please leave us comments below or post on our Community forums.
How to get data from MS Word Forms automatically into Excel using VBA.
Creating forms in Microsoft Word is easily including forms with dates. Analyzing the form data in MS Word can be difficult. Now we can transfer the data from Word forms into MS Excel quickly and easily using VBA code.
Watch the training video below to see how a form is created in MS Word and then using VBA we transfer the data into Excel where it can be analyzed conveniently:
View the video on YouTube.
The complete VBA code to transfer data from Word forms into Excel:
Sub getWordFormData()
Dim wdApp As New Word.Application
Dim myDoc As Word.Document
Dim CCtl As Word.ContentControl
Dim myFolder As String, strFile As String
Dim myWkSht As Worksheet, i As Long, j As Long
myFolder = “C:UserstakyarmyForms”
Application.ScreenUpdating = False
If myFolder = “” Then Exit Sub
Set myWkSht = ActiveSheet
ActiveSheet.Cells.Clear
Range(“A1”) = “First Name”
Range(“a1”).Font.Bold = True
Range(“B1”) = “Last Name”
Range(“B1”).Font.Bold = True
Range(“C1”) = “House No”
Range(“C1”).Font.Bold = True
Range(“D1”) = “Street/Locality”
Range(“D1”).Font.Bold = True
Range(“E1”) = “City”
Range(“E1”).Font.Bold = True
Range(“F1”) = “Zip”
Range(“F1”).Font.Bold = True
Range(“G1”) = “Gender”
Range(“G1”).Font.Bold = True
Range(“H1”) = “Date of Birth”
Range(“H1”).Font.Bold = True
Range(“I1”) = “Email”
Range(“I1”).Font.Bold = True
Range(“J1”) = “Mobile”
Range(“J1”).Font.Bold = True
Range(“K1”) = “Course Joined”
Range(“K1”).Font.Bold = True
Range(“L1”) = “Fees”
Range(“L1”).Font.Bold = True
i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(myFolder & “*.docx”, vbNormal)
Power Automate Form Response To Excel
While strFile <> “”
i = i + 1
Set myDoc = wdApp.Documents.Open(Filename:=myFolder & “” & strFile, AddToRecentFiles:=False, Visible:=False)
With myDoc
j = 0
For Each CCtl In .ContentControls
j = j + 1
myWkSht.Cells(i, j) = CCtl.Range.Text
Next
myWkSht.Columns.AutoFit
End With
myDoc.Close SaveChanges:=False
strFile = Dir()
Wend
wdApp.Quit
Set myDoc = Nothing: Set wdApp = Nothing: Set myWkSht = Nothing
Application.ScreenUpdating = True
End Sub
Power Automate Forms To Excel Format
Download a sample file:
Microsoft Form To Excel Flow
Power Automate Add Row To Excel From Forms
Further reading:
Extracting Word form Data and exporting to Excel spreadsheet