In this article we will look how to simply copy data from a cell in excel into a word document. It is short an simple, but nicely highlights the concept and the ability to automate moving data from one place to another.
Robot Process Automation (RPA) and Microsoft’s new UI Flow capability are allowing Microsoft 365 users to automate processes which were previously though to be out of reach.
To create UI Flows you must have access to Microsoft 365 and a Power Automate license. If you don’t then you can always sign up for the free Office 365 developer account and you can have your own me.sharepoint.com tenant allocated for learning and exploration.
Installing Power Automate Desktop
From within Power Automate you can find the UI Flows under the New icon at the top
Select Power Automate Desktop
Select a name and Launch App – If you do not have it installed you will be prompted to download and install it
A simple test excel sheet
I created an excel file locally and left a simple message inside the message is in cell d4. The file is located at C:\Users\mroden\Desktop\temp\RPATest\RPATest.xslx
Power Automate Desktop
Back in the desktop tool I am going to start to build the robot. There are many different ways to achieve the same thing. being efficient is one thing that is important for robots, but that’s for another day. These are the steps we want to automate the moving of text in an excel cell to a word document.
- Open the Excel file
- Get the current Sheet
- Go to Cell D4
- Copy the Cell
- Open Word
- Execute a Paste.
So let’s start. Within Power Automate Desktop we are able to find the open to “Launch Excel” and from the resulting dialog select the path to the excel file we want to open.
We then add steps to Read from Excel worksheet – selecting and copying cell D4.
NOTE – the variable produced is “ExcelData” we will use this later once word is opened.
We are going to be good robot writers and now close the excel sheet again so that we don’t leave copies of excel all over the place.
Next we will open word and paste the ExcelData variable into the new book. We will do this by running a program and opening a test word document (blank) file – RPATest.docx.
Once open we then have to click into the window to make it active.
We do this using a Click UI action. We have to have the word document first open so that we can direct the Desktop app to pick where to click.
We select to add a new UI element and then the mouse highlights different aspects of the applications open. We mouse over the Word document and select CTRL-Click to record
The final step is to then send the text of the copied Excel cell back to the Word document. We can do this using SendKeys, and then selecting the ExcelData variable to be sent.
The final bot is finished and while it is simple it demonstrates how we can integrate multiple applications without having to write any code.
Running the robot…..we get this…. 🙂 You will see from the video that the variables are being stored and shown on the screen.
In this article we have seen how to install Power Automate Desktop and create out first simple bot. It’s takes a little time to get used to the interface but is pretty simple and worth having a go at yourself.
NOTE – This article is written in October 2020 and the content is likely to change over time as the platform evolves. It is written to help people wanting to get started with the preview version of Power Automate Desktop