How to use Excel in Power Automate Desktop

my name is anna jensen let's learn some microsoft power automate desktop today we will look at excel in power automate desktop we will see how we can read an excel sheet how we can use the data from the excel sheet and type into applications or website and finally how we can update the excel sheet back let's get started so i create a new flow here i can call it something like excel and then i click create while this loads we can go back to our excel sheet because here we got some columns and rows the columns that's first name last name up until rank and then we have 10 data rows which cost corresponding data we want to read the data here and populate it into an application first we will just use a notepad document but afterwards we will type it into a crm system let's get started so here we have our flow the first thing that we will need to do is to localize where we store our excel sheet i store mine here on the desktop so if i go back to the flow the first thing that i will do is to get the special folder here then i will drag that in we will be getting the desktop but we could use a lot of other common folders like program files or cookies or history but we'll use desktop as that is where my excel sheet is on the power of this action is that if this flow goes to another user's computer then it will find his or her desktop and use that you will see that we produce a variable called special folder path we can call this variable afterwards and then it will call my desktop or actually the user's text tab so click save then we need to launch excel so we search for a launch excel here and drag it in we can launch excel with a blank document or we could choose to open the following document and what do we want to open well we want to open our excel sheet so up here in document path the first thing that i'll do is to click the fx here and choose the special folder path double click that and that's not enough because this is only my desktop we need to specify what file we want to use so use a backward slash and then we can say mine is called data xlsx right let me go back and show you or actually i will click save first then we'll go back and we can see here that it's a data and it's an excel file so we go back we can even open this launch excel again and we can see here that we produce a variable called excel instance we will refer to that so let me just click save and now we need to know because we have an excel sheet here we need to know where the first three row is that is row 12 we can of course shoot but we don't know because this excel sheet could be dynamic and we want to know where the first free column is that's here because we'll use that in our flow so go over to actions and then search for get first free column row here choose this one get the first free column row from excel worksheet you will see here that we will read into our excel instance that we created up here in launch excel and we will produce two variables we will produce the first three column and the first three row that is integers so it will tell us that this is row 12 and this is row i that is one two three four five six seven eight nine that's it so we click save here and now what we want to do here is that we want to read from the excel sheet so we'll search for a read from excel worksheet that's it drag it in here what do we want to read well we want to read the excel instance that's right we got it up here in the launch that it will be the sheet with the data in that's fine we can either choose to retrieve single cell values or we can click the drop down here because we want a range of cells that's it then we can choose the start column and the start row let me show you the excel sheet we want to start up here so we want to start in column one row one so here one and one and the end column well we know that we want to end one before this empty row and one before this empty column how do we do that the first thing that we can do is that we can use the two variables that we produced up here in the get first free column and row from excel sheet so we can click the fx here so the end column well that would be the first three column however this will be will be one too much because then we'll end here and we'll get a blank column and vice versa with the row so what we do here is that we can go in here and then we can simply just say minus one like this the same thing we can do with the end row so click the fx here say first free row double click it and we will say -1 that's it then we know where to end then we can read the first line of rings is that containing column names well certainly it is we have column names up here so activate that we will now populate data that will be stored in excel data that's it so we click save now we actually read our excel worksheet we read the entire data let's try to propagate it into a notepad so we will search for action populate text in window here public text field in window where do we want to type it in we want to type it in here in the notepad however we need to create a ui element for that we could either do it over here if we haven't opened up this action or we can do it directly in this action so in here in the text box click the drop down we can see that we have no ui elements to display we have not created anything so click to add a new ui element like this now you can see that we can find different parts of the ui and we want to populate it in here so when you have highlighted this this is red in the border then you click ctrl and left click now you can see that the selector over sorry over here is created so we click done you will see that the selector is automatically filled in here in the text box and then we can choose what text we want to fill in well we just want to fill in the excel data that we created up here in the read from excel worksheet so click the fx choose the excel data then click save like this we could have a display message some sort of a log message telling us that the workflow is done so find a display message drag it in the message box title well we can call it something like log message what do we want what what do we want to display we want to say something like entry updated imagine that this application is very advanced and we just want to tell the user that we have updated the entry so then we click save what we need to have now is a close excel because if we don't do that then the excel sheet will be locked for editing and we don't want that so always choose a close excel we want to close the excel instance do we want to save it well in this workflow we are not updating the excel so it doesn't matter however we will update it later so choose the save document you could also choose to save it as another name here but we'll just take the save document then click save now let's close this one here and what we can do here up here in the launch excel we can see here that the instant is visible we could choose to make it not visible however for this example we will make it visible as it is now we can close down the excel sheet here like this and run our flow so power ultramate is running it will take a few seconds into reading the excel sheet here and remember not to touch the mouse or anything because we will populate text field in the ui as you see here you can see how quickly it goes like this and we have our log message and excel will close down in a few seconds so we can click ok here and the workflow is done however you also you saw that we got display message and then when we click the display message then the excel sheet closed down if we want to change that we can just easily drag the display message down here so we close excel first and then we have the display message i don't have any preferences but do as you want let's make it more applicable now i want to iterate through each row of the data in the excel sheet and then i want to populate it into my crm system my crm system is just a demo application very simple however the intuition is the same as if it has been salesforce or dynamics so let's look at the data and see what we want here we have our data again and we want to take each row and then we want to populate it into the crm system however let's just take first name and last name here otherwise it will just be boring repetitive work which we're trying to avoid so we'll see that the column names that is first name and last name then we can close down the excel sheet we go back to power automate so we will delete this public text field in window because that was the notepad so click delete like this then we will need a for each here and drag it in take it right after the read from excel worksheet and before the close excel and the display message what a for each does is that it can iterate values into a data table and here we can say what do we want to iterate so click fx and we will choose the excel data like this we can see that the variables produced that secure an item so each row will be iterated and every time the row is populated then we will have a cue and item and we can work with that let's see how that is done we click save now we can see that we have for each and an end so let's try to populate the two fields the first name and the last name in our app so what we'll do is that we'll do as before populate by the way i tried to speak a little bit slower because a lot of you had told me that i speak too fast so tell me if you're satisfied with the speed of my voice and also if there's anything else i can do better i'll be happy to hear that but let's take a public text view in window so drag it in here in the for each so now we need to create new selectors and we can create both at the same time so click the drop down here click the add uiu element you can see that the one from before is still here we could have deleted it however let's just let it stay but it's always good practice to delete old ui elements that you no longer need to use however let's just take a add a new ui element we need the first name here so control left click we can see that over here we can see that we have the selected now we can just take the other one as well so click control left click and we have the two fields that we need so then click done first here we got need the first name so click the drop down and choose the first what text do we want to fill in well now we can refer to the cue and item that we are iterating through up here so click the fx and take the cue an item however we don't need the q and item because that's the whole row so we need to specify what column we want from and we can do that by making these hard brackets here and then we can say single quotation mark and then the column name so here it was first name single quotation mark and then the end heart bracket back here so that is that's it that's how we populate each row with the first name so click save here and then we can do exactly the same thing as up here so let us drag in another popular text window click the drop down here now we want last what text do we need to fill in well that's just the same as before so click the effects take the q and item and then we need to specify what column we want so we do just as before just just with the last name here like this then we can click save now we are done with this workflow let us open the crm and we can see that it actually works we are reading the excel sheet we are populating the fields in our cm system you can see here that it's actually michael robertson dog derek and so on so it will take all 10 rows and populate these fields like this we have completed the use case so click ok now let's look at condition in the excel data so let me open up the excel data again here on my desktop save that week let me move it over here say that we for each row of these entries we want to look at the rank if the rank is bigger than eight then we want to do a comment of high ranked so it will only be john smith and doug derek right let's create that in power to make desktop so close this data and go back to the flow the first thing that we'll do is to move the display message into the for each because we'll use that as a breakpoint that is it will stop the workflow every time an iteration has been done and we can use that when we want to inspect the data so let's move it in here we want to add the condition we still want to type in the first and the last name and then we want to have a condition so in the actions find an if drag in the if just below the populate text field in window and above the display message so what do we want to look at we want to look at the q and item here and the rank so again these hard brackets by the way i think they are named hard brackets but if you know a better name for them please tell me so here we can just say rank and heartbreak so we will look at the rank then we will say if that is greater than 8 then we want to do something we can see here that we have our if and the end if if it was greater than 8 then we wanted to fill in this field here so we'll just do as before we'll find a populate text field here and drag it in the if so what do we want to fill in we need to create a new ui element so click here left click and control we can see we got it up here the selector click done like this what do we want to fill in now we will fill in a static text so we just say hi ranked then we click save that's it but because this is a very simple crm system actually it's only a demo app then we want to clear this comment section so we can see that we will actually have something updated so we will have another public text field we will have that in the beginning we will use the selected from before and we want to have empty data in how do we do that well we have two percentage signs then two quotation marks that will be a blank so we just click save that's it now we can try to run the flow again remember we have made a breakpoint down here the display methods so we can verify our data and remember we only had two high ranked members but let's click run we are reading the excel data and soon we will go into our for each loop here john smith he was high ranked that was right we can even open up the excel sheet we can see that indeed john smith was high ranked duck dag was also high ranked so let's see if our power ultimate flow will figure that out now it will be jane dorsey nothing spectacular with her albert kipling nothing as well then it will be michael robertson here and now it will be doc derrick like this here's high rank well there's no need to continue the rest of the rows here as nothing will happen it will just update first and last name so click stop now let's say that we want a target in our excel data here we want to target sheet2 i have some other data here you'll see that it's only two rows anachient and jensen let's see how that is done so we can close down this again what we want to do is that we want to set the active sheet here we will do that just after the launch excel so we will refer to the excel instance again that one was from up here then we can activate the worksheet we can say name or index this one will be name the worksheet name that was sheet2 and we can click save it will still refer to the excel instance so we don't have to change anything down here and we can simply just run the workflow anna's jensen he's high ranked that's fine and then we'll have jens anderson as well that's how you change it to another sheet finally let's write something back to our excel sheet we want to update the processed column when we have made the entry in our crm system so let me click ok here we will close excel so again let's open the data here we want to update this so we just want to type something like yes in here when it's processed again let's close down the excel and open pop the power automate flow we will need to introduce an index variable into our for each so the first thing that we will do is to set a variable here we can simply just place it right above there for each it doesn't matter as long as it is before the for each so let's call it new vowel and the values should be two because we want to read the second row that's just right out of the headers so that's why we take two here save now we need to increase the variable so when we go into the for each then the value is two that is we want to look at the row 2 however next time it will be 3 4 and so on so we need to increase the variable so find a increase [Music] here and we will put that in the end of the 4-h so the variable name that was new var right here what do we want to increase by we want to increase by one then click save so now we have our index variable we know that we have a number and that corresponds to the row we want to target so right after the end of the if down here then we want to write something to excel so we'll find a write to excel here and drag it in down here we want to write to the excel instance that's fine what value do we want to write we will just write yes right then we'll say a specified cell that's fine the column well that was column i that was the processed column big i then we want to target the row and now we have the number as the row was that was our new var so click the fx choose the new one like this we'll click save we could also choose to update the display message that was our log message so open that oh sorry let me drag it down here we just say empty entry updated and then we can say entry then we can say number and we can call our new var because that was the row so let's say that row corresponds to an entry so we'll say new bar like this and a space that's it let's see this workflow in action [Music] entry 2 update however that was a little bit wrong because we could actually argue that this was entry one because that was the first one however it was in row so let's just keep it that if you wanted to have entry one then we can just say in the new bar we could say minus one then you would have an entry one here and f and afterwards two then click okay we'll have the ends unless updated fine we will close excel and if you go out to the data you will see that we have processed both items boom that's it if you want the next lesson in this guide just click the video to the left if you want my beginner's tutorial for power automate the web part then click the right don't forget to subscribe to the channel to get all the new future videos have a good day bye

Channel: Anders Jensen

Views: 86,724

Keywords: power automate desktop, power automate desktop for beginners, using power automate desktop, how to use power automate desktop, power automate desktop excel, how to use excel in power automate desktop, anders jensen, power automate, microsoft power automate, microsoft power automate desktop, power automate desktop guide, power automate desktop rpa, power automate desktop tutorial, power automate excel, excel automation in power automate desktop, excel power automate desktop

Id: Xtc3bHcDrW4

Length: 23min 49sec (1429 seconds)

Published: Tue Sep 29 2020