Excel & DataTables in Power Automate Desktop - Including VBA and VBScript

Excel is the most used business application across all companies a Microsoft most valuable professional let's get started our department receives data in an Excel sheet and needs to do several calculations afterward we will create a project folder on our desktop so anywhere on your desktop right click new folder we will call it project then we will download the Excel book to it go to the course page the link is in the description below scroll a little bit down to course materials here you will find candy.xlsx right click save link as and place it inside the project folder so I double click it here and I'll click save let's go inspect the data first so I open up the project folder then I'll open up thecandy.xlsx this is the Excel sheet as our department received it we have a sheet called products and we have four columns product which is our products it's candies the cost to produce and the price sold here I want to for each row I want to say price Minus cost equals profit then create a profit column here in between the price and units sold and put that number in it then I also want to create a total profit column here after the unit sold and that is profit times unit sold later on I want to do some more advanced stuff that is making the headers build I also want to do conditional formatting that is paying the cells in green yellow and red based on their values finally I want to see if it works with 100 rows as well of course it will we will use the native Excel actions in power automate desktop but I'll also show you how you can use VBA and VB script to make more efficient workflows close down your Excel book again and close this remember we copied the path to this project folder in case you didn't do simply just shift and right click copy as path go to Power automate desktop create a new flow I will call mine Excel and we will click create find a set variable we will create the project path as a variable so we drag in the set variable and we will call it project path like this Ctrl V in value double paste in the path I'll delete the two quotation marks in the start and in the end I'll click save to create the Excel instance we go up to actions find a launch Excel and drag it in we can open a blank document we will do that if it's a log that we are trying to create but since we are trying to edit the data and we already have we will open up the following document the document path click this little X Out Here choose the project path this is only the main folder so I'll have it backwards less and then I will say candy dot xlsx the reason why we do it is because this project folder can move for example if we move this flow into production on another computer or a server it will definitely not be placed on an assistant's computer and not on the desktop we'd only place this project folder on our desktop now because it's easy to work with so always create a variable for path that can change this we will not make it visible it's fun in the beginning to see the robot work in the Excel sheet but it hurts performance a bit we will not open it as read only we will only do that when we don't have to write data back to the Excel instance now we do so we click save one thing that is important is to close the Excel instance again otherwise power automate desktop can and will lock this Excel book until you have restarted your computer for now we will just refer to the Excel instance that got created up here in the launch Excel we will not save the document we will later so we click save try to run the robot again let's see that you can launch and close it without any problems that's it it worked fine let's read the Excel data one thing that we want to do is to let's go back to our Excel book here in the project folder double click to open it is that the default OneDrive syncs here this auto save on we will not have it on and that is because when we work with this instance we will get some unwanted data if we choose Auto Zen gun so have it ticked off now save it and we will exit again and go back to Power automate desktop so we want to read the Excel data so find a read from Excel worksheet and drag it in between launch and close we will again read from the Excel instance we will retrieve we can retrieve a single cell but we want to say all available values from worksheet in advanced first line of range contain column names yes we have column names in our data so check that the variables produced at called Excel data that is a data table always rename your variables so it shows what data you have in them since we have a Excel data products this will say we got products in it and I click save and it's much more easy to maintain this robot if you or your colleague come back to it and see just this action we don't need to open or run and expect try to run it again and just inspect that you can actually read the data into a data table over here we see the Excel data products double click to open it here we have our data that is the 10 rows what I want to pay close attention to is that it is a data table we have our headers here and then we have our rows but look the first row that is index 0 that is because data tables are zero indexed so when we want to refer to the first row we say row 0 second row is Row one and so forth it's just taking a few seconds to comprehend because we are used to saying that the first element is obviously index one but it's not like that in programming so a data table is sear indexed like close so now it works we can read from it but let me go into my Excel book again say I move to another sheet that could be the aneshension worksheet save it here and I'll exit it try to run the robot once more so here we are launching we are reading from Excel worksheet and we're closing if I go over to Excel data products and double click look we read a sheet that we did not want to that is because power automate desktop reach the last active sheet so here's a problem it's not a problem now because imagine that we did some calculations on the products it will just fail here that will be the least of the problems of course it's annoying when the robot fails but imagine that two sheets look like each other that might be data from different years or different weeks then it will be a disaster if our robots start calculating and might put out some data for use later on we will have been making a huge mistake so we want to combat this close here go up and find a say it variable drag it in beneath the first set variable we will call this active sheet and you see that we want to use is the pro dots then we'll click save now go up and find a set active Excel worksheet we will do it after the launch and before we read so here we want to specify we want to read this product sheet and then we will read it so we are in the Excel instance we want to activate a worksheet with a name and we will use the variable that we created up here so active sheet double click it there you go we could of course have put in products here but we want to use this variable a little bit later in the flow so we are doing it like that it will also make it easy to change the sheet by just changing the value of this variable try to run it again and see that we can actually get the correct data now and we are not having the energy and snork over in Excel data products there you go we now have our candy once more click close now we want to calculate the profit and that was let me just open up the data once more that was in the product I want to say I want to put in a private column right here in between and then I want to say price Minus cost for each one of these rows and have it here this means that we want to insert a column to the left of units sold that is to the left of column four and it looks like this so I close here go to Power automate desktop go up into actions and then find and insert column to Excel worksheet we will have it after the read from Excel worksheet so again we use the Excel instance and then here in column that is I need to give the index of the column that I will insert a column to the left off and that was column index 4. when we talk columns we can also use the letters so if if you prefer that we can also write in D and we click save so right now this will insert a column to the Excel worksheet now we just need to save the Excel data we will not override the original data but let's create new Excel book where we can save the data too so open up the close Excel and so right now we are not saving so I click this drop down and then I save save document as then in the document path click this x double click the project path then I want to say it backwards less and I can for example say output dot xlsx like this then I click save try to run your robot once more this will only insert a column to column D it will be a blank column but it is nice to see that we can actually do it so I open up the output again there you go we now came a little bit longer now I need a header here and I want to do these calculations so close down this again go back to Power automate desktop let's do the results first so right now I want to iterate to our data table that will be the Excel data product I want to take each row one by one and then I want to say price Minus cost and put it inside the newly created column it looks like this to iterate through a data table we will have it for each okay for each Loops a collection from start to finish and then it's finished so after the insert column to Excel worksheet we have our for each here we will Loop the Excel data products so go double click that I will say store into product like this I'll click save now for each one of the rows I can refer to that row by referring to the Q and product once we are iterating through it we need to convert this text from our data table in to a number in order to do the calculations so go up to actions and find a convert text to number and drag it inside the for each here I'll refer to the qn product so I'll click this x double click the current product I want to take my price first and here I can refer to the actual header of that price column that will be price so in hard brackets single quotation marks we will have in the price so this is the text to convert the variables produced let's call it actual price like this we'll click save we also want to do the same with cast and here I can mark this convert texter number Ctrl C Ctrl V now we have two identical move into the second one by double clicking here we need to change the price to cast and we also change it down here you could of course make a brand new convert texture number and fill in these things but here we saved a few keyboard clicks we liked that since we're lazy as automation Developers Xavier now we have converted it we can do the calculation but first we need a helping variable so we will find a set variable and drag it in before the for each this helping variable will help us keeping track of where we at so because there's no link between the Excel data products the data table and the Excel actual data we only read it into this data table and then the link is broken so we need to establish that link and that is we start here in index 0 but that is Row 2 in the Excel sheet because the first row in the Excel sheet that is headers and then our data starts two so I go up here we'll call this helping variable row number we will start in twos since that is the Excel row that we're starting the data in we'll click save then we know for each one of these iterations we are moving one row below in our data so I'll have another set variable here and I'll say row number equals to whatever the row number is do this we will add one trade by saying plus one inside these percentage sign so now we have a Tracker here let's write in the profit go up to actions find a right to Excel worksheet and bracket in it's very important that you drag it in before the set variable inside the for each so between the convert texter number and before the set variable the value to write now we will do our calculation and that is price Minus cost so we will have the price and inside these percentage sign and that is because when we do our code blocks here it's a calculation we need to do it inside the percentage signs click the X here and double click the cast we will only need percentage sign in the start and in the end so I'll delete this now delete this so this is our profit we will do it for each one of these rows that we're entering to where do we want to write it I want to write it to a specified cell that will be column D that was the newly the place of the newly created column then we also have a row and here we start with row two so I've just find the row number variable like this that's the reason why we created it then we click save try to run the robot once more so now we are doing the exact same thing as before but now we just have some private data into Alum d we're also saving the data like before that's it move out to your Excel book called output.xlsx there you go we now have our profit which is the price Minus cost we still need a header but that's the least of it so close the Excel again move back to Power automate desktop we will write in the header we will only do it once so we will not have it inside our for each but we will have it before and I'll go up here I'll find a right to Excel worksheet and I'll drag it in after the insert column to Excel worksheet so the value to write we will just hard code in profit unspecified cell that is fine that will be in D and the row will be one that is the headers row so if you put it here that will be kind of redundant because then we will do it as many times as we have rows in this Excel data products that is unnecessary try to run the robot again and yes we tested a lot that's because we want to see that each step works and then we proceed imagine that we build a full robot without testing and then we realize that something is wrong that is not beneficial now I go out here open up the output there you go we have now done the profit column now we need to have this column here that is the total profit that will be unit sold times profit let's go create that so we go back find another convert text to number so I'll go up here and convert text to number here and drag it in below the first two ones so the text to convert and now again we could be lazy but let's just write it here so that'll be the qn product and then we will have this hard bracket single quotation marks and we will say we want whatever is in the units sold like this we want to save it into a unit sold here you need to have no spaces in we cannot have spaces in variables so this one is called units sold then you'll click save so now we will insert another column I can just copy it here Ctrl Z Ctrl V I'll move in now we will place it to the left of column F that is because we already inserted one column we will click save and we can do our calculation you can also write the header and again I just copy these things because we are extremely lazy so I press Ctrl C here and I press Ctrl V when I say we are extremely lazy that is just one side of the story the funny one but it's actually just to make the minimum of arrows and of course we want to increase the development speed not that it's the most important parameter but it's still the parameter so I go in here where do I want to write now it's F1 and I want to write total profit instead like this I'll click save then we can do the calculation down here so this is not a thing to copy right now I'll just make a new one and might be limits to our laziness so we have our right to Excel worksheet what do I need to write well I need to write in the profit times unit sold and since we are not having the profit directly here we have it as price Minus cost we will just do this calculation here so we'll have the price then we'll say minus then we'll say cost like this so we want to calculate this one first let's replace it inside parameters this is just simple math then we will say times and you can see I play spaces in between these operators those will be ignored at runtime then we will say times and we'll have the unit sold remember to move the percentage sign like this you could make a case for since we are now using this price Minus cost a couple of times we could create a variable for that but for now it's good then we will say where do I want to write it to I will write it into column F and again we will use the row number so scroll a little bit down double click the row number and click save before we run an inspect it you can go down here to run delay the Run delay that is to lay between each one of these actions it's there for a reason when we develop it's only here when we run our robots from the actual flow overview here it's not there and if we want it from the cloud it's not their item this is just to see what's going on but try to make this one then this will go much faster do click run and now we will see that everything works now you can see it runs extremely fast we will close the Excel let's go inspect the data go into the output there you go we also have a total profit column now which is simply just profit times unit sold one thing that I want to do is to resize these columns you can see here we only have some of the names here and the total profit is also a little bit is not wide enough this column so let's go fix that I'll click this X and I'll go back to power to my desktop here I only need to do it once and that is after we write all this data I will have a resize resize column rows so I'll search for a resize column rows in Excel worksheet drag it in after the end the Excel instance is still the Excel instance I want to resize a column I will choose all available and I will choose autofit that is will fit the column width to the data in it so I'll click save try to run it once more again we will do it quite quickly because we have set run delay to one that's it one thing I'll also say remember to save your flows by clicking save here power automate desktop will not have an auto save Within that's it let's go inspect the data in output there you go we now have auto fit on you could also have specified that you only wanted in product for example but let's stick with this for now we go back to Power automate desktop right now each time our robot runs then this output file will get overwritten I want to do Dynamic file naming so for each run we will produce one output file we'll go back here scroll all the way up find a get tune date and time and drag it in before the launch Excel and after the two set variables here we will just get the current date and time and the intuition here is that we want to use it in the file naming the available is produced is called current date time I'll click save I'll need to convert this to a text and that is so I can use it in the file naming so find a convert date time to text and drag it in here the date time to convert that will be the Q and date time click the sex if you're in date time the format to use we will use a custom format so we can specify exactly what we want I want the year first four wise and that is because we will take the years then the month and the days that is because we make it sortable since this robot is probably running many times a day I also want to get the time within take 20 hour format that's more easily sortable so our minutes seconds and you can easily skip some of this or if you want like you can have all sorts of separators in I like it like this it's easily sortable we will save this variable into a formatted date time variable click save now we will use this format date time down here in the close Excel so I open that in front of the output like this little X here double-click to format a date time then let's just have an underscore that's just for the visuals so we separate the actual Dynamic date with the output.xlsx click save run your robot once more now we have Dynamic file naming once this robot finish so again we add nice things to our robot step by step and the data is obviously still the same I also want to make the headers bold and we want to do some conditional formatting over here to the profit column and the formatting I want to do here is that if the total profit is less than 70 I want to paint it red if it's between 70 included and less than 100 I want to paint it yellow above or equal to 100 it should be green so what this is is that it's actually impossible to make negatively in power automate desktop and here we can either use VBA or VB script I want to show you both because this is so powerful for your flows let me close this data again open up the original data here it's candy Dot xlsx if you have a developer tab here you don't need to follow the next step to activate it in case you don't have it let's just activate it go to files here then we need to find options in case you don't find the options you just need to click more and then options so click options here customize ribbon go over here scroll a little bit down in here you find the developer it looks like this if you don't have it simply just mark it click OK and you find it up here then we need to go to developer click Visual Basic so what we're doing now is to create a little bit of code here in our Excel book that we can use with our robot so here you'll say right click insert module here our code goes in and don't worry you don't need to write it I've already did it for you so go to the course page scroll a little bit down rpn VBA code for the lesson I've created the VBA VBA is Visual Basic for applications what this does it that is generally just make the headers bold and then we color the cells based on this condition that we set up copy it up here go back to uxl simply just press Ctrl V to paste it in this little VBA macro is called format data that is the name up here so that is important to remember because we will use it in power domain desktop now go click save here it will give you a warning it says that if you continue by saying yes this will be a macro free workbook we are not interested in that because we created this macro so click no now it's asked you to save it and here you need to go down to save as type and choose Excel macro enabled workbook that is asterisk dot xlsm like this so now it's called candid.xlsm and not candy.xlsx just stay safe you can exit this one here because we stored the actual macro in it so I can close it then we need to call it here in power automate desktop so right after all of this has happened we want to call the macro so say run Excel macro and drag it in here the macro that was called format data remember and we'll click save we also need to say so now we run it but we also need to say up here in the launch Excel we'll need to say we want to use not the Excel candy.xlsx but candy dot xlsm that was the workbook where we have our macro in go into advanced here take the nest under a new Excel process scroll a little bit down and choose load add-ins and macros click save now try to run your robot and you will see the magic happens in a few seconds in case you like these videos please give it a thumbs up that will really help me a lot so now here we have this output which was this here double click to open it there you go we now have bold headers but we also have color-coded after our scheme that is if it's bigger than 100 it will be green if it's very low but lower than 70 it will be red otherwise yellow so using a macro is a very clever when the native power to make desktop solution is not enough but it still needs to go inside an Excel book and that can limit a bit what we can do instead let me close this and scroll to this VB script for the lesson on the course page here I created a VP script this can run natively in our flow it doesn't need to run in an Excel book I just need to specify the path to my Excel book and I need to specify the sheet then it does all the same thing it Paints the headers bold and it also colors the cells based on our condition so when I say this is smarter of course if you are very good in VBA and don't know vbscript although those are similar you should obviously go with VBA also if your colleagues are skilled in those stick here but I recommend you to learn VP script you're more than welcome to copy this so we do that copy here but it's easily understandable I also made comments then we go back to Power automate desktop so what I want to do here is that I want to move into launch Excel we will just need to read the original one without the macro so we will say candy.xlsx I'll also go down to Advanced I'll untick this Nest under new Excel process I'll click save scroll further down the Run Excel macro I don't need that anymore so I'll disable it then we can run a DB script we already searched for run that was to run Excel macro and up here is the VB script make sure you put it in after the close Excel but that is after we made all our data we can run it and that is because we need this path here which we will only get in the end of the flow so we cannot place it here that will not work so here I'll put in control V to put in the actual code then I just need to say it's not sheet1 so I will delete this and that's why we created the active seed variable so make sure your mouse blanks inside these two quotation marks go over to this x scroll up double click the active sheet now we're using this variable we will also use the path to our Excel worksheet and that is the easiest thing to do is to click save go into close Excel copy it from here then we know we get the correct one go into the Run VB script again and let me just maximize it a bit delete all this inside these two quotation marks remember to keep the quotation marks press Ctrl V we'll have it here we'll click save try to run it once more and see that this actually works and now we are independent of a script inside an Excel book so that's it let's go to our new one here that one is this there you go it did the same thing now let's put it to the test and see that we can actually also do 100 rows that will be the sheet we just need to change the active sheet to products 100. close it here move in here scroll up in the active sheet I'll just say products 100. as you can see here it gets a little bit easier because now we are by using this variable because we're using it here then we're also using it in the script if I can run it once more it will go quite fast and although we don't see it is quite satisfying seeing it working it through you can see the row number out to the right it says rule number 40 we need to take up to row number 101. so it's almost there and that's it run the Ruby script remember to subscribe to the channel to get more of these nice things then we'll click minimize let's open it here now we worked within the products 100 there you go that's how easy it is to use Excel with power to make desktop Network and solve power automate problems with more than 6 000 developers by joining the I love automation Discord Community the next lesson is right here

Channel: Anders Jensen

Views: 12,057

Keywords: power automate desktop, anders jensen, excel in power automate desktop, excel power automate desktop, power automate desktop excel, power automate desktop vbscript, power automate desktop vba, power automate desktop excel beginners, how to use excel in power automate desktop, automate excel, excel rpa, vbscript in power automate desktop

Id: urFyD7QOkac

Length: 35min 24sec (2124 seconds)

Published: Fri Mar 31 2023