How to use Excel in Microsoft Power Automate Desktop

Excel is the most used business application and as power automate desktop developers we are automating it a lot and here's how to do it so I created a Excel book here on my desktop and I open it and there's only one sheet in it here I have five columns three rows and one header row so the first one that is a good Tablet TV laptop B that is revenue C that is cost for that good and then we want to calculate the profit the profit is simply just the revenue minus the cost and then we want to write a status such as processed a very simple task but it shows you all the nice features about automating Excel in power automate for desktop so let's go do it so I close my Excel book here you will find that automating excel in power automate for desktop can cause some problems and I'll also show you how to solve them so stick to the end first I grab the path of this Excel book I press shift in right click copy as path then let's move over to power automate for desktop the first thing we want to do is to find a launch Excel drag it in this launches an Excel instance that we can use here we can choose to open a blank document or a following document which could be our Excel book on the desktop sometimes you want to create a log from scratch then you choose blank otherwise we want to open something here I can either browse to it by clicking here or I can simply just Ctrl V pasting in my copied path so if I delete these two quotation marks that's it that's the path to make instance visible that's nice in the beginning when you want to see what's going on when the robot runs but honestly it's not necessarily and it hurts performance a bit so close that I'll take that so now and then we have open as read only if we don't want to edit the data in it which we want here then we can tick open as read only that will increase performance a bit but for now we need to edit it so we'll untick that as well then click save generally speaking I don't like to hard code in values that is this path here imagine that this changes I need to go into this action change it here that might not be the problem but imagine that we have used this path four times an offload and I need to update it four times so best practice is to create a variable for path so up in actions find a set variable and drag it in above the launch Excel this one here I could call this Excel path like this and the value that will be this path again here so I just Ctrl V and delete the quotation marks now creating a variable has the advantage that I can now just refer to the Excel path and then get this value out so if I click save here and then go into launch Excel I delete this document path then I can click this little X here Excel path double click it or click and then select like this now it got chosen here so then I can click save so now we created a dynamic a variable that we can easily change up here so now we launch it usually we want to make sure that we also close it again because that can cause a lot of problems in Excel I will show you a little bit later so again stick to the end so here I take a close excel in and what I want to do is that I won't before I am closing Excel I want to save it so and here I you can argue that we want to create a new document if we wanted to create different logs so say that this robot runs every day we might need a new book for each of those runs then we just take save document as and choose a document path with the dynamic expression but now I will just say save document that will just override whatever in it but we will not delete any data we will just update the empty columns now we can start reading from this Excel sheet so these were just the skeleton that we built now we can put on some meat so here I want a read from Excel worksheet drag it in just in the middle of the launch and close Excel so here I need to say what do I want to read I could read a single cell I could read range of cells I can take a selection that is if I work in the UI like opening Excel physically we will not do that out we'll try to refrain from that or we can just take all available values from the worksheet that's the one we'll pick we'll read everything that's in this sheet up here and we can go in advanced and here you can see first line of range contain column names we have column names Heroes so that one will take that one you can see that we're reading this data into a variable called Excel data this is a data table a data table is a very important variable type in power automate desktop as we'll often use it when we want to manipulate especially Excel a data table looks exactly like an Excel sheet except it is zero indexed and it only exists during the runtime that is when the robot runs so when we read it we read it here then the robot stops somewhere down here then the this data table will not be accessible anymore so if you want to save something in it right now we just read it but if we want to update it we need to remember to save this data table into the Excel sheet so let's try to click run so we are launching Excel then we're reading from it and we're closing it if I go over here to the right to variables in case you don't see it it looks like this or it's empty just click this little X up here these are the variables that we have created the Excel path we created ourselves and these two got created while the flow Ram open the Excel data by hovering your mouse over three rows five columns and double clicking that is our data table and it looks exactly like the Excel sheet except you can see here the first row is called 0 and 1 and 2 and actually and this first row in the Excel was column two because the headers was actually all was actually row number one so you just need to to say that in a date table we have a headers um headers row that that doesn't have a name and then the first row is zero index so that is zero just need to get your head around that and you'll be fine don't worry it will come to you after you made your first couple of robots now we want to update um these two here so we have a difficult one that is profit here we need to make a calculation based on whatever's in here here here here and then the status so when we processed these rows here I want to write processed in status let's go create that first that's the easy part and then let's pick the difficult part um afterwards we need to say in this data table I need to iterate through each row one by one and then I can update profit and processed one by one so here I'll find a for each and I'll drag it in right after the read from Excel worksheet the value to iterate here I want to iterate the data table so click this little X here double click the Excel data so that's the one I'm going through I'm now storing each the iteration it takes the row one by one and each time iteration text row I can refer to that by saying current item I often want to create my variables so it reflects the data that's in it that's also best practice so here I might say good then click enter don't worry about these percentage sign that's the sign of a variable in power automate for desktop and power automate for desktop automatically created when we create when we just write here in a variable field you can see it already did it for me as well so then I can click save so now I'm iterating to it I need to do something about it and we want to write to our Excel sheet so I'll find a right to Excel worksheet here and then I drag it in so now we just need to think about what do we want to write well um we want to write processed like this we want to say I want to write on the specified cell so which column do we want to write in and here let me just click save this will give us a few errors so if I just open it up again the column that we want to write in that's column e you can see it here so let me close it again open it up go into write to Excel worksheet and take the column now we need to fill in the row and this is the row that we are in and since there's no way we can we can do it if we pick this little X up here and we can see the goods there's no thing that we can get the row number we can only get the columns count the column names so what we will do here is that you'll create a supporting variable that's an important Concept in Excel automation so just go outside it again find a set variable and this set variable it will correspond to row numbers in Excel so here I'm saying set variable and then I will call this Excel row counter like this and we will give it the start value 2 and that's because we're writing back to it the Excel worksheet and here the first row was in row two let me just show you that as well so we complete this year that we align so this is the first row we are updating it and that because the first row are here that is the headers so here I want to say I want to update this one first then I want to add 1 to that counter each time the for each runs through our data table so we know that we are aligned with this Excel sheet it looks like this so I go back here I have this variable I gave it the value to that corresponds to the column sorry to the row number then I find another set variable drag it in in the end of the for each here I will add one to this counter so click this x here say Excel row counter go into the value Excel row counter and to add 100 you need to go inside the percentage sign and say plus one like this so now we have a counter that we can use up here on the right to Excel worksheet go in here same row click the selection Excel row counter and click save shouldn't we try to actually see that this works remember to save your flows in power automate for desktop because there's no auto save here and you might restart your computer without noticing that you had a flow here then it will be lost so now we are running we can see that we are launching reading set variable it ring true to three rows and closing Excel we only had the process now but let's just see that that worked then we can easily do the conversion that's it we processed three rows here and we wrote in the status now we just need to calculate the profit The Profit here that is calculated where I want to say Revenue Minus cost so it will be 4 000 here 2 000 here and 10 000 here um it looks like numbers and even though if we formatted those as numbers and they will still when they come into power automate desktop they will still be treated as objects or text values so we need to do a conversion so we get them from here and then we convert them to a um to it to a number so we can do a calculation and here um let me just do this and go back here so in our data table I want to say for each one of the rows I want to say the revenue that should be stored into a number variable called revenue and the cost whatever is in here will be stored into a cost variable numeric value so I'll need to find two convert text to numbers like this so here convert text to numbers and we will do it before the processed so drag it in here so which text do I need to convert I will look in the queued item which we named Goods so click this little X here take the goods now I just need to specify in which column do I want to look well the first one we'll take that was the revenue so say hard bracket start hard bracket end single quotation mark and a single quotation marks it might be a little bit difficult to see but it's it says goods and then hard brackets two single quotation marks and we'll start writing inside the two quotation single quotation marks then I just say Revenue so this will take for each one of these Goods row it will convert whatever is in the revenue column it will convert that to a number it's called text as number again we want to rename our variables that is best practice so here I'll say Revenue like this and then I can click save so now we have converted the revenue then we also needed the cost we can do two things we can either drag one more in here or I can say Ctrl C Ctrl V now I have two similar I just need to update this Revenue to a cost so I double click it like say cost instead of this and the variable is produced that will be cost so now for each one of these rows when I'm entering through it I have two values now revenue and cost I can write back to my Excel worksheet so um here I'll say right to Excel worksheet and the value to write well I will just save Revenue move it over here Revenue Minus cost and to do that I'll need first I can get the revenue find it over here like this and when I want to subtract two variables um I need to only have percentage signs in the start and in the end so it will looks like this I can have spaces in it doesn't matter you don't have to have it but I think it makes it a bit more easy to look at for you and then I'll say cost I can also if I want to make sure I don't misspell I can also find that one over here but then I just need to delete these two percentage signs so this is my calculation then I want to say where do I want to write it it will be in a cell and that will be in the column d That's one before our column E before that's where our profit goes and the row that will still be in the Excel row counter make this x here Excel row counter save so now we have our workflow I'll save it again and then we will try to run it so here we are launching Excel with reading from it and each range we can see it takes a few seconds more and let's just go to our Excel sheet that's it that's how easy it is to process rows in Excel I also told you that we often get some errors when we automating Excel that's because OneDrive power automate for desktop and Excel doesn't really talk that well talk that well together it's not a problem you'll usually get a failed to open Excel document or a file related error let's Force the arrow and then let me show you how to solve it don't worry it's very easy so if I go up in the first this right to Excel worksheet here I'll say Revenue Minus cost and then I just say minus a text value so this one will give us an error and um the thing is now we can force the Excel error so what will happen we are launching Excel this will give us an error here the workflow will stop and then we are not closing it properly that means that it locks the Excel sheet let me show you so if I just click play here we're launching Excel and there you go we have an arrow and if I try to run it again I can show you both I can if I open it you'll see that I getting these kinds of Errors I can't really open it and when I run it again we will fail up in the launch Excel where we will get a timeout error that we can retrieve that we can open this Excel instance so let me just fast forward till it fails and you can see that it actually just keeps running this will give us an error eventually and we don't have to wait for that so I'll click stop here so now we want to say I want to fix this and this is actually very easy um so what I will do is just I have my Excel sheet here so I'll go down to the start menu here I will just start typing CMD that will give us a the command prompt so we'll open this so now we want to close Excel by force and remember if you have any Excel sheets open on your computer this will also close them so remember to save them because they it will not prompt you so what we are going to write here is task kill forward slash F that is force and then we want to say the image of excel XL so when I run this command I just press enter you can see that we now shut down two processes we also need to go into Data now this is unlocked and click close here and then we can click save so now and just for good manner press the upper arrow in here and just do this again it's not found now but sometimes it opens up again so now we can run it and we have freed so to say our Excel sheet and here you can see we still have the arrow so we should of course fix that one and we can do that by saying this and now we will get the error again so let's just count that by doing show here click close here we repeat it it's nice to see so then we do this now you can see we have the process has been terminated so again whenever you went in and click close into document recovery remember to do the task skill now the process will run perfectly so that's it I prepared the next power automate desktop lesson for you and it's right up here see you

Channel: Anders Jensen

Views: 6,738

Keywords: power automate desktop, anders jensen, power automate desktop tutorial for beginners, power automate desktop tutorial, power automate desktop excel, power automate desktop excel loop, power automate desktop for each excel, power automate desktop excel error, how to solve excel error in power automate desktop, how to use excel in power automate desktop, how to use excel in microsoft power automate desktop

Id: 4Py-V85bPfo

Length: 19min 53sec (1193 seconds)

Published: Fri Oct 07 2022