Excel in Power Automate for Desktop (Full Tutorial)

excel is the most used business application and here's how to automate it with microsoft power automate for desktop you'll learn all the basic things when we work with power automate for desktop and we will implement some very advanced features such as dynamic file naming and visual basic for applications don't worry this is a use case i'll take you through step by step while i hold your hand the only thing i want you to do is to open up power automate for desktop and do the things with me my name is anna jensen let's learn some microsoft power automate for desktop automating excel is a central part in all automation development because we'll use excel a lot in the businesses today you will learn to automate excel and you'll also learn how to use macros visual basic it's not beginning stuff but it's essential to understand because you need vba you need to learn just the syntax of it and how to invoke it don't worry you will not have to learn code but let's get started so we go to the lessons page here and then we download the course data so i download the excel book i click the three dots here and then click download that one will download the excel sheet i will just do this and let me do this and then place the excel sheet on my desktop i do this because then i can easily get to it you can place your excel sheet wherever you want let's open the data here we have it so we have six columns employee id name q1 q2 q3 q4 yearly sales and monthly sales we have 10 employees each one with a employee number then they have a name then they have sales for the first two quarters of the year and sales for the next two quarters of the year we want to calculate the yearly sales the monthly sales and then we want to find out our best sellers and our worst sellers that seems very rude but we want to see how we can differentiate between them easily and we'll do this by color coding them so for monthly sales below ten thousand we want this cell to be colored red if this one has a monthly sales of less than ten thousand more than thirty 000 that one will be green and between 10 and 30 000 that one will be yellow so shouldn't we start automating excel in power automate for desktop you go to power automate for desktop i click new flow and then give my flower name i will say excel and vba vba is a central part in excel and don't worry it can seems a bit overwhelming but it's very easy it's not rocket science first we'll start with the excel basics i want to maximize this i want to read my data and the first thing i'll do is launch excel and then read this excel book you can see we have two sheets and i want to read the employee sheet not this anishinsnog where you can find information about me so i want to read this and let me just close it we can do it in two ways first i'll grab the path i'll shift right click i repeat shift right click with the mouse then copy as path go back to power automate for desktop then i'll find a launch excel here and drag it in we need to alter a few parameters first we will launch excel not with a blank document but we'll open the following document here you can paste in the document path you could also have clicked the selected file here and then find your files in your explorer that one will also be fine but i'll do it like this and remember to move the quotation marks around the file path so it will look like this make instance visible do we want to see the excel sheet whenever we run the robot usually we will not but now it's quite exciting to see what we have accomplished step by step so let's keep it on open as read only we will not do that because we want to write back to excel in advanced settings those ones we'll come back to later in this lesson but variables produced this excel instance when we open this excel sheet that will produce an instance called excel instance this is a variable you can click on it and you can see the two percentages signs on each side that will indicate it's a variable we click save so now we have launched it we also need to close it otherwise power automate for desktop can and will close it for editing that will be quite annoying so always remember to close your excel automation again so i'll find it close excel and drag it in here i'll just close this excel instance and i'll choose to save it so whatever i have written in it i will save it then i click save i can run the automation so if i click run here and just test that everything works that i can actually read the excel book and i can close it so far so good you can see that we have one variable that is the excel instance now our strategy is and let me open up the employee sheet again i want to find out where my data is because i need to provide power automate for desktop with the range for my data this is the range but power automate won't know so what i will do is that i'll find this row here that is the first free row and i'll find this one that is the first three column because then i know one column before that is where my data stops that is the one column before the first three column and similarly down here one row before the first free row my data stops and then i will have my range so let's do that in power automate for desktop so i go here i'll then find a get first free row column so i'll search for get first free column row and please remember to take this one here the get first free column row and not the get first free row on column so take this one this is not in the advanced section so drag this one in drag it in in between the lines and the close here i'll just read from the excel instance and then i'll produce two variables the first free column and the first three row that is fine i click save so let me close down my excel sheet here and run the automation there we go we can now see that we have a number that is the first free column and the first free row we can read our excel data so if i go out here and i'll say read from excel worksheet find that action and drag it in underneath the get first tree row column now i want to retrieve the value from a range of cells so i picked that one the start column that is the first column that is column a the start row that is the row one so we will start all the way up from the upper left corner in an excel sheet the end column now we will use this variable over here or the content of it so to pick that variable pick the x here first free column double click that now we have it but we want to go one before the first three column that's where our data ends so i go inside here the percentage sign and press minus one that's it the same thing with the end row click the x here first three row and then i choose minus one one thing that we'll need to alter is in the advanced section we'll need to say that our first line of range contains column names i'll take that one because we have headers that was the name the employee id and the monthly sales and all these things those ones are headers we specified here that's important so i click save i can try to run the automation once more this is a good approach to when you have made one or two steps in the beginning try to run your flow see it works see that you can read the data now go over here you can see the excel data that is a data table that we produced here in this excel read from excel worksheet you can see it here and you can see if you opened the action so if i double click here i have my data table that is the excel sheet written as a data table you can see the structure is entirely the same here we have the row numbers and this is zero indexed so the first row is 0 then 1 2 3 4 5 6 7 8 9 and so forth then we have the employee idea one two three four five six six seven eight then we have the name then we have the sales here and so forth now we can do calculations on it so i click close so the strategy here is that i want to iterate through this data table with our data so i'll find a for each so search for it for each in actions here and drag it in underneath the read from excel worksheets here we just pick our excel data and this current item that is each one of the rows one by one they'll be stored into this variable called q and item that is fine you can for example pick person you can give it any name you want this is just a reference for you to remember so you have more for eaches you want to give them a very describing name so you don't confuse yourself then i click save so now i'm iterating through each one of these rows everything that comes between the for each and the end those ones will be actions to be performed we have nothing in it so nothing will happen no need to run the automation but will know that it will happen let me open up my data once more here and here i want to do the calculations but first since these are excel objects stored as text whether or not these are numbers i want to convert these things to numbers in power to make for desktop so that's it the q1 q2 column and the q3 q4 column i'll convert those ones to numbers so i can calculate armin so i'll close it again go back to my workflow so i'll find a convert texture number and this is somewhat annoying that you'll have to do these actions even though that your data is formatted as numbers you'll end up doing this so i'll have a convert text to number but that's okay it's just the approach so no need to be in a bad mood about that so text to convert now i will target each one of my rows that was the cue and item so i'll find it here cue and item but i only want to target the q1 q2 column here so to do this i can go in here and then i can say heart brackets start then i can say quotation marks i can say q1q oh sorry q1 q2 quotation marks heart bracket end this will target this column i could also give it the column number but then we will have the situation if the business side they decide to change the order of columns or insert a column then the numbers wouldn't be right of course here if they decide to change the column headers we will be in problems as well so always tell the business side if they handle the data to do it right then there's no problem the variable is produced it will produce a text as number let's give it a better number you can just overwrite this i say q1 q2 you don't have to worry about the percentages signs those one will be added automatically as you can see here then i click save so now i'm having this one q1 q2 as a variable number i can work with it similarly i will right click here or just press ctrl c then ctrl v or right click paste it in now i have two similar actions i'll just change this q to q g q 3 q 4 so i'll open it double click it this is a nice way of doing it instead of creating it all the way from scratch i'll do this i'll also store it in a q3 q4 variable like this and then i click save so now i just have two variables as numbers i can run my automation once more here we go you can see that we're iterating through each one of the rows that is this for each symbolizing and here we have our variables this is just the variables of the last row because we have 10 so these ones will get overwritten once we do a for each so we'll have them over and over and now we will take advantage of these two variables because we want to do our calculation so let me open up the excel sheet again by double clicking it here i want to do the calculation so i want to add this one to this one and then print it out in here the sales and so forth for each one of these rows so this is the one two three four five fifth column and you can see that this one starts with number two then three four five six seven and so forth we'll create a variable that will start with two and then we'll add one to them so we can write it back to the right cell that one would be this this this this let me show you so back to power to mate for desktop then we will find the set variable first so i'll say set variable here drag it in above the for each because we will define it once and for all before the for each so i'll do the row number like this and we'll give it the value of 2 because that one was the row number that our excel data started in so i'll do this and then each time i have done a calculation that is the these two and we'll have a few more actions then we want to add one to this number we'll do that by dragging it in here then we'll find the right variable that one will be the row number like this and here we can say row so i click the x i'll find the row number here and to add one to it i go inside the percentage signs and press plus one that's it so now i have it now i can write it back because i got this supporting variable i know it was column five and i have this row number as the q and row one and one so let's find a write to excel worksheet like this drag it in above the set variable but below the two convert checks to numbers i'll use the excel instance that's one is from up here then the value i'll add these two together so first i'll find the first one here that one will be the q1 q2 and inside the percentages signs i can make a space that doesn't matter the spaces will be ignored it's just easier for you to read so we'll have a plus then i'll add the q3 q4 and now it's very important that you delete this percentage sign and this one so here we have the percentage signs and then we have the variables and in between we have no percentages signs so that one is fine we'll write to a specific specified cell that is fine the column we want to write to that was number five and the row that one was the row number so here i can just take the value from the row number like this then i click save let's close our excel sheet and see if we can run it successfully and we can write data back to our sheet so here you can see we are iterating to each one of these rows there you go let's go out and inspect our data we now have yield sales and those ones look approximately right i think they are right of course they're right so now we will calculate the monthly sales and then we can start to get a little bit more advanced because the money saves that is column six by the way and i will go back to power to made for desktop i will create a monthly sales variable which i will use to write back to excel but i will also use it for one other purpose but let's first find a set variable i'll drag it in underneath the right to excel worksheet in the variable new var i will call this monthly sales like this and the monthly sales is just calculated as q1 q2 plus q3 q4 those two added divided by 12. so let's just do q1 q2 like just like before we say plus then we'll say q3 q4 here and i'll delete these two and these two and i'll have a parenthesis because now we will add these two first and then we will divide by 12 like this we click save so this one is our monthly sales now we will write that back to excel as well so i will find a write to excel worksheet drag it in beneath the set variable here so here we have the excel instance that is fine we'll do the exact same thing at which as we did up here the value to write that one will just be our monthly sales we already did the calculation here specified cell that is right we take the column six the row will still be the row number so i'll choose the row number out here like this i'll just let it be for two seconds so you can do the same then i click save so now we write our monthly sales back again we can run the automation to see that we actually succeed it will take like 10 iterations iterations of these rows that's fine 8 9 10. i like these things to see that things succeed so if i do this i open up the sheet now we have the monthly sales so now for the advanced part of this exercise i want to apply these rules to it so i want to say if this monthly sales is below 10 000 you can see that we have one here 4800 then i want to paint it red similarly i want the highest sales screen otherwise yellow we can't do it directly in power to made for desktop we could open up the excel sheet and then click buttons here but we want to do it directly so what you need to do is to scroll a little bit down i cheated and created the script for you you don't need to do it you'll find this lessons again in the description below so here we have a macro this is visual basic for applications it's called color code we have two in-going arguments one called monthly sales and one called row number the monthly sales is the monthly sales from before and so is the row number here we will say if the monthly sales is below 10 000 then we want to take the range f and then the row number that will be the cue and row number and we want to paint it in color tree that is red and similarly if it's above ten thousand and below thirty thousand then we want to paint it six if it's above thirty thousand then we want to paint it four so what you do here is that you click copy and then we go to our excel sheet so that is here here you should see a developer tab if you don't see it then click file then find the options down here click customize ribbon and make sure that this developer is ticked here do that minus so i have it so then i click ok now click the developer up here we will use this code in this sheet so we can call it from power automate for desktop so click the visual basic and one is over here now right click insert module and then we can copy in what we copied from here we can paste in what we copied from here this one is our macro so this one is color code so now i will just save it and this one is saved as xlsx now you can see we can't auto-save because we have a macro so what i want to do now i go back to my excel sheet i click save else then instead of calling it i want to call it employees and place it on the desktop but i want to choose it as macro enabled so that is the xlsm make sure that save it the same place now close down our sheet here and you can see that we now have a macro enabled workbook if i open it then i can click enable content i want to make this as a trusted document that one you have to this is just this code but if i go to developer again visual basic you'll find our macro this one is the one that we want to call so we want to call color code that was one was the name so let me just close this close this what do you think about the quality of this lesson please post it in the comment below that will help me a lot thank you first thing that we'll need to fix is that we now have the employees xlsm name so we will go back to power to make desktop and fix it up here in the launch excel so instead of xlsx we will say xlsm we could of course also find it by clicking the select file then you need to go down to advanced choose nest under a new excel process and load add-ins and macros i repeat go down to advanced take the nest under new excel process and load add-ins and macros then click save so now we have created the macro and we we're reading it here then we need to run it from power automate for desktop again you don't need to understand the code you just need to understand what we're doing here so rewind the video if things goes too fast it's designed for that now i'll have to run excel macro drag it just beneath the right to excel worksheet but above the set variable so i want to target the excel instance that is still this one even though we also the address so the macro i'll just say color code that was my name of my macro and then we have two in-going arguments remember in the script we had the monthly sales and the row number so separated by a semicolon i will then have my first argument that one was monthly sales so i can click here i will take the monthly sales variable that's why we created it then another semicolon and then i'll find the row number that is the q and row number so we know which cell we want to paint then i click save so now we can run it this one will paint our cells in the correct color when we have run the automation so now we'll run it like 10 times here we are running the excel macro for each one of these rows and paint the right cell here let me open this one here there you go we have now painted the correct cells in the color that we wanted that one is fine a thing that we can change is that each time we run this automation this one will just get overwritten it would be nice if we could have a timestamp so each time we run this there will be a unique identifier that one could be a timestamp let me show you so we just have new files each time this robot runs so let me go here so the first thing i want to find is to get the cue and date and time so i'll find a get current date and time and this one will get the current system time when the robot starts because i place it in the beginning so i'll have the cue and date and time the time zone that is the system time zone that one is my time zone so then that one will be stored into a variable called current date and time i click save now i need to convert this date and time into a text so i'll go over here then i'll say convert date time to text here and drag it in beneath the get current date and time so the date time to convert that one is this variable up here queue and date time so i'll pick it here here i can choose a standard format this one lets me choose between some very standard and fixed formats those ones are good if you want you can see an example of it here but i want to customize it so i customize it here i can customize the format and these ones are net that is a programming language or framework so i can say four wise i say month i can say days you can see an example comes here this one i will use for my file name then i'll say hours like this and then i'll say minutes and seconds that's it this one will get saved into formatted date time so i click save here now i can go down all the way to close excel here so here i can save the document but i want to save it save document as and here i will just choose the same path as before so i'll find it on my desktop make sure you choose the right one that is the macro enabled workbook and i click open and i will use the date so here i will just put in my variable just before the employees so here i'll have the formatted datetime variable i will find it over here there you go so now i have that in in front of the employees i can click save so now i run it i will run it 10 times this is just as before it will just give a new file name to the excel macro enabled workbook with our data in that one will be on our desktop so we don't overwrite the previous there you go we now have a unique filing this is how you can do unique file names which is pretty clever because we want to report each time our robot runs another thing that we can have a problem on this is our data let me open it if i do this then this sheet is active i just click save and i click close and then if i try to run the robot so let me try to run it here we are launching the excel and now we have a problem because we can't find the data that is because we are looking in the wrong sheet so this one will just take this one will launch excel let me just show you here it will launch excel but it will just take the active sheet so to fix for that we will open the book that one is this one and then we will set the active sheet so if i go over here and i'll find a set active excel worksheet so just below the launch excel then i'll have the excel instance i'll activate worksheet with name and then i will find it mine one was called employees so let me just rename because i'm lazy so i'll copy it here write it here so i will make sure that my employee sheet will get activated like this let's try to run the robot again so this is just a way to make sure that we are using the correct data that we are on the right sheet if you have multiple sheets this is important say that i've been in or another colleague have opened up the data then i want to make sure that i've actually opened it now you can see it ran again it made a new record i can open it there you go we now have a nice new sheet which is formatted correct the next lesson is on the screen just click the video

Channel: Anders Jensen

Views: 19,398

Keywords: anders jensen, anders jensen power automate, power automate desktop, power automate desktop excel, power automate desktop excel macro, power automate desktop excel data, power automate desktop excel instance, power automate desktop excel loop, power automate desktop excel examples, power automate desktop excel first free row, power automate desktop excel current row, how to use excel in power automate for desktop, how to use excel in power automate desktop, rpa, full tutorial

Id: zVvaRCZqZ7E

Length: 28min 39sec (1719 seconds)

Published: Wed May 04 2022