Advanced Excel in Microsoft Power Automate Desktop - Use Case for Accountants

Excel is straightforward in Microsoft power automate for desktop today we will solve an accounting case the mission is to get the latest Community rates from the web page and write them in the correct places on this sheet Danish krona DKK is our base currency then in our accounting sheet we have multiple transactions with different currencies for each row we want to determine which currency is used then look up the previous sheet and write the correct exchange rate finally do the calculation qnc amount times exchange rate divided by 100 and write it in the amount DKK my name is Anna Jensen let's learn some Advanced Microsoft power automate for desktop first navigate to the course page there's a link to it in the video description then click download the Excel book for the exercises now go up here click the three dots and take download I will be placing this Excel book on my desktop so I move it out here let me just minimize this so you can see my desktop then I make a folder so I right click new Boulder and I will call this currency RPA like this then I move the Excel sheet into here and then I will get the path of that here comes the trick you can press shift on your keyboard I repeat shift on your keyboard and right click with your mouse and then you can see a copy as path we will use that so click here then we go to Power automate for desktop where I have been creating a flow first we set a variable for the project path so find a set variable here and drag that one in we will call this project path so up here in variable say project path that is the name and then the value I'll press Ctrl V Ctrl V to paste this one in remember to move the quotation marks like this then I click save having a variable for this value instead of hard coding it in in my Excel activities in a little while makes my flow more robust and I can easily change the path when I move the project from my desktop let's go to the Excel work so I find a launch Excel here and then I drag this one in this just launch an Excel instance here I want to open up this Excel book that I just downloaded so I click this drop down and then I say and open default document the document path well I used the project path variable that I just created up here so click this little X and then you can choose this variable then that is the project path then I just need to refer to the Excel book in there so I make a backwards less then I'll say qnc data dot X LSX like this I won't have my instance visible so I'll untick that down here you can see that the variables produced is Excel instance I can refer to this variable later in the flow and then I will refer to this instance then I click save we also need a close Excel so up here in actions find a close Excel and drag that one in here you can see that I'm referring to the Excel instance and in the before closing Excel I click this drop down and choose save document then I can click save like this so here just open Excel close it again you can try run it to see that this path actually work we can do that it is just a very very quick flow it doesn't do anything it just launches the Excel instance and closes it again but we're using this variable One Challenge as a power automate desktop developer is that power automate desktop often locks an Excel sheet that we're working on yeah that's kind of crap we might see it today but don't worry it's easy to combat it's preferred to make sure that Excel is closed before a power automate desktop flow starts so that is before we're trying to launch it let's make sure that we have closed all other Excel processes so here I find a terminate process and drag that one in right between the set variable and the launch Excel down here in the process name I can just type Excel like this and then I can click save so this one will terminate any process that is open before we are launching this Excel just make sure that you don't have any important data on because we are just terminating without saving here let's inspect the data that we just downloaded so go to the project folder and open the Excel book here here you can see that we are having multiple sheets and we need to find the correct one the one we are using is called 2A dot currencies and it looks like this so let's grab the sheet Name by right clicking down here choose rename Ctrl C to copy it and then close it down again like this so I move back here and then I'll say set active Excel worksheet like this and move that in just after the launch Excel so here I can see I'm referring to the Excel instance and then I can activate a worksheet with a name and I just got the name so Ctrl V to paste that name in and then click save so now I have activated that sheet I can read from this sheet so up in the actions find a read from Excel worksheet and drag it in after the set active Excel worksheet again I'm in my Excel instance and I want to retrieve all available values from the worksheet so click this one here in advanced choose first line of range contains column names yes our data contains headers so that is important that you take this one here here you can see that we're producing a variable called Excel data and this is a data table more about that later then we can click save so now we're actually doing something we are closing Excel here in the beginning and we are launching it and opening this book we are setting the active Excel worksheet so we make sure that we read the right sheet then we read the data into Excel data and finally we close it so again let's try to run the process it's always nice to see that whenever we make these steps that our robot works and it did you can go over here in variables in case you don't see it it might look like this or this then you can just click this little X up here that will get the variables up go in Excel data power your mouse over this eight rows and four columns and then double click that will open up the data now you can see that we read the data correctly and we can start a second part of our case so we want to for each one of these rows here in this data table that is for each currency we want to get the current rate and the date of that rate and we're going to the Danish national bank so let me just close this one here move back to the course page which is here scroll a little bit down and there's a link to the Danish national bank here so click that let me just maximize it again so I want to navigate here first so let's go create that so I'm grabbing this link at least the beginning of it so this one here I copied that one over in power automate for desktop here I am launching a new web browser so I choose launch in case you haven't installed the browser extensions you can do that there's a link to a video guide up here in the right corner in case you have the extensions we can just pick which browser we want to use I can recommend using Edge or Chrome maybe Firefox but never ever Internet Explorer that one is outdated so it's both unstable and unsecure so don't take that one we'll take Chrome here so I take launch new Chrome drag it in here just right after the read from Excel worksheet so here I'm going to launch a new instance and in the initial initial UL I just paste in my address you could also create a variable for this address in case that this address changes a lot or we're using a lot in the flow but we are not so we're staying here you can see that we can refer to this browser instance by the variable this produces called browser then I can click save similarly I also want to close the browser when am I whenever I'm done again so I find a close web browser here and drag that one in here this seemed to just close the browser instance called browser and then I can click save again we can run the flow and inspect that we actually able to open up a browser here we go but we're actually closing it again as well so let me just disable this close web browser for now so we can just work a little bit in the browser we will enable it later let me click run this is just to open up the web page I'm lazy it's easier to run my flow than to actually find a link elsewhere let me maximize the web page what I want to do now is click click the more currencies down here so in power automate for desktop right after the launch new Chrome go up here and then find a click link on web page drag this one in here and here you can see we are left clicking in something in the browser instance in the UI element click this drop down here we're going to add our UI element a UI element and let me just click this first here the UI element is everything you can see here in this instance is a browser but it could also be on your Windows desktop in that way we will use the UI automation but for now it's browse Automation and you need to go down here so you can see this red thing here recognize all the UI elements we are going to the more currencies down here it's called anchor you can see that Globe if you press control on your keyboard click with your mouse we have created this UI element then I can click save so now we're actually clicking on it I'm going to show you in a few seconds but first let's inspect this UI element go over here to this stack of papers and here you can see something called anchor more currencies which is exactly the UI limit that we just created so first I want to rename it so I go up here and press either F2 or right click rename I will just call this more currencies like this and then I can double click to inspect it we won't dig further into this but this is a selector a selector is just an address of an element on your screen and this address looks like this very weird we won't go deeper in it in this section but in case you want to know more about selectors click the video up here in the right corner I can just click save or cancel we haven't changed anything so and let me just show you the data now we click the more currencies of our robot do and we want the currencies for Euro US dollar pound sterling and so forth all these up here with these rates here and the current date of the newest rates so in power automate for desktop find and extract data from web page make sure you take the one on the web data extraction and not the one on a data extraction I repeat take the one on the web data extraction now drag that in right after the click link on web page here we need to open up the web page with the data that we want to scrape you can see again now this little red thing comes up here it's not entirely the same before because right before it was UI limits that we're searching for here we're searching for things to scrape and it works a little bit different but almost the same I first want the urony so I just right click here then extract element value and take the text here then I take the US dollar extract element value and now the clever thing comes in how automate for desktop recognize that this is a pattern so it just gives us all the rates this technique will also work when the rates changes so we will be getting the right rates tomorrow next week next year and so fourth right now we're just taking these rates so I click finish down here I will give it another name that data from web page is always nice to name our variables well so we can make sure that we can easily differentiate between the names these ones I will call Q and C's scraped like this and then I click save we can try to run again just remember we are opening up a new browser window each time that we're running this robot that's fine we can just close it afterwards because we have disabled the close browser I can close this one manually now go over to the variables in the qnc script you can see that our currencies error have now been scraped into a data table it looks very much like you know from Excel here we just have one column and then we have seven rows the only difference is that we refer to the first row as zero then one two three four so this is a zero indexed variable this data table more about that later now let's write out this data to our Excel sheet and the nice part is that these results that is in the same order our Excel sheet is designed in so whenever you create your Excel sheet like here make sure that you can make your life a little bit easier by having the same order let me show you the Excel sheet again so if I move in here open up the qnc data here we have it so you can see Euro US dollar GBP Swedish crones and so forth I want to have the rates in the rate column that is in the D column or the fourth column it's very very easy so let me just close this Excel sheet again and go back to Power automate for desktop then you find a right to Excel worksheet here drag it in after the extract data from web page so here again I'm writing into my Excel instance and I know I'm writing into the correct sheet because I specified it up here the value to write that is the currency scraped which we just created up here so I click this little X here and double click the Q and see scraped again double click this variable called currencies scraped this is a data table and this will override the entire row we have nothing there so we want to write unspecified cell and then as we talked about I want to write in the D column and since we have headers I want to start in Row 2 then I can click save so now we have scraped these currencies and we're writing it back to excel remember to save your flows because power automate for desktop will not auto save your flows let's try to run it again and meanwhile I can just close down these browsers with the Danish national bank so we don't have a lot open we will enable this close web browser in a little while like run up here that will launch our flow it would go we navigate to the currencies we scrape the data and we close Excel let's see if we get the currencies right here in our Excel sheet so I'll open it here there you go you have now scraped it these 100 those ones were there beforehand that is because Danish krona is the base currency 100 Danish Kronos cost 100 Kronas and you can see 100 cost 752 Danish Kronos and similar with all the other ones now let's just fill in the date that is the date that we can see up here so let me close this Excel sheet and then go back to Power automate for desktop while we wrote the rates in the D column we will have the dates in the C column so currencies and D's and the date in C again find and extract data from web page like this and drag it in right after the right to Excel worksheet again we have to open up a page with the results that we want to scrape so I want to open this go up here here you can see both span and T8 both of them will probably work but I like to take the inner one so make sure you take the span right click extract element value and just pick the text and then click finish again I want to change this so I can easily see what's going on just by looking at the variable name I will call this date scraped like this and then I can click save so I'm now saving it to the date script and let's try to run the flow again to inspect how the date scrape data will look like again we're just doing the exact same things except that we're now getting the date so if I move out here to this date scrape variable move over here and then double click on it now we have our data so this is again in a data table format it's only with run column and run row but it's still a data table so we'll need to refer it the data to buy that so the dimension is one by one and we can refer to this date in a little while by saying 0.0 so the first row and the First Column these are zero indexed again so 0.0 that will give us this one we will take advantage of that so let me close this one here let's just talk about the strategy that we want to use when we are writing the dates back so let me move into the Excel sheet again so here we're going to say for each one of these rows then we will write this scrape date in so that is one is here in the C column and we will start by row two so again in Excel sheet you say the first row is one and then two so we will start in row two and I can close these ones down so we're creating a helping variable and that is just to keep track of the row that we add in the Excel sheet while we're iterating room one by one it's a little bit more easy just to see what I'm doing and then I can explain while we do it again I really hope you do this exercise with me so find a set variable drag that in right after the extract data from web page here we will call this Excel row index so Excel row index and then we will give it the value 2. because now now this this will not do anything but we're using A4 each now to iterate through our data so I'll find a for each go down here right after the set variable here I will iterate through my original data that is the Excel data that one was the one with the current season so I'll go click this X and then I'll say Excel data this one will be stored into a item that is called q and item and this is we're entering through the rows in this data table called Excel data one by one and as we progresses we can refer to the Q and row by saying q and item I like to again I like to keep my naming convention cried direct so I can see what's going on this is also best practice so we do like this again we can refer to the row that we're entering through by saying qnc so now we're taking all these rows one by one nothing is really happening in here all actions need to go in between for each and end what I will do is to add 1 to this variable each time this for each runs because that will keep track of where we have so I find a set variable again drag it in in the for each up here I'll find the Excel row index that one is here and then I will add one to whatever value that will be that it might hold so click this little X L row index say plus one like this and then we can click save so now we're adding one to it and we can use this one when we want to write to each one of the Excel cells so again find a right to Excel worksheet and drag that in in the start of the for each so what value do we want to write in and right now you see we write to each one of the rows because we have it in this for each we want to get the date scraped so I click this x here that was our data but as we talked about we need to say we want this 0.0 coordinates that one was the cell because this was a data table so move in here hard brackets start say zero hard bracket sense this is the row another hard bracket zero hard bracket end this is the column so now we have the correct um Row one column that we're getting our data from we want to say unspecified cell then we say column that one is C so that one was the one where the date date should go in and the row that's why we have this Excel row index we can now use this supporting variable say Excel row index that's it then you can click save one thing I might want to change is this Excel data I might want to say Excel data currencies because we might have more data in a little while but what I'll do here is right click rename say Excel data currency do it out here because then when I click enter look what's going on right here then it will update here and here so automatically that's quite clever let's try to run the flow again and we are launching Excel set the active Excel worksheet and the lights so here and right now we can also enable the close web browser so we don't have that open let's do that first before we inspect the data so I'll right click enable action so I will place this one right after the first extract data from web page that is where we are not going to use the browser anymore now let me minimize this and I can close down some of these instances and clean a little bit up let's open up our data there you go you now have dates and rates yes yes now for the second part accounting before we inspect this sheet let me just show you our flow here we already have a lot of things going on you can see this will be a quite long flow best practice is to use subflows subflows are just containers that you can call here from the main and then we will perform those let me show you so I will go up here to subflows and I'll say new subflow and I'll call this any T allization here and then click save it's empty now so we want to put things in here and then called this initialization from me I want a set variable the terminate processes the launch Excel so hold Ctrl in and click all these the set active Excel worksheet the read from Excel worksheet then launch new Chrome and that's it so all the things that is preparing the data we're not clicking anything here we're just opening and reading things so these six here again press control and Mark this now you can either right click and cut or just press Ctrl X that one will cut them out you'll get a lot of Errors that's fine we will solve these so go to initialization in here press Ctrl troll V now you have your actions here instead so right now we're missing them because nothing calls them from the main so go in here and then say run subflow like this and drag it in right in the beginning so here I'll just run the subflow that I just created click this little drop down and then we're running the initialization I will not run it now but try run it there's really no difference so what's happening here is it that it will perform the main first then we'll just run the subflow and we all have all the same actions here these six then when there's no more actions in the subflow it will jump back here and then we'll perform the rest of the runs similarly let's so we just got all the currency rates let's create a subflow for that so click this little drop down new subflow say get shouldn't see rates like this and click save so what do I want here I want and let me just go to main I want everything from here and now I can just click this one here and then go down to the end before you click that press shift and then click the end so I want everything except this first run subflow and this close Excel now click Ctrl X again go to get currency rates and click Ctrl V to paste all these actions in and go to Main and we also need to run this subflow so say run subflow after the initialization and say get currency rates click save finally we also want an end process for this we will be having more actions in it so subflows new subflow and then I'll say and process like this and then click save go to main Mark the closed Excel Ctrl X go to end process again Ctrl V paste it in we also need to run the subflow from here so I'll drag this run subflow in right after the get currency rates and then I'll say in process like that so really clean main workflow now with these subflows in we also want to create a new subflow for the next task that is accounting so up here in shop flows I'll say new subflow and then I'll say accounting then I can click save so um over here in main I can run the subflow now you want it to be right before the end process because the end process that is where all the things happening when we close things down so here the run subflow and then I'll say accounting and click save so in the accounting we need to read two Excel sheets we need to read this to a currencies with the updated rates that those ones are the ones that we just got then we need to go to accounting and read whatever is in here and now the task is is that we want to write the exchange rate that we just got here from currency so basically just moving him over and then we but we we're not sure which one of these exchange rates will come in here we can see Euros Japanese Yen Euros Euros DKK US Dollars Switz Swiss Francs so we want to write the correct exchange rate in here based on whatever is in here when we have done that we're going to multiply the currency amount with the exchange rate to get the amount in Danish Kronas so first we will read this sheet and then we will read this sheet again we're reading this sheet because we have just updated it so I go right click here rename Ctrl C just to grab the name again so in power automate for desktop in the accounting subflow find a read from Excel worksheet and drag that one in so here I'm reading from the Excel instance I still have the same sheet called qmc is activated we did that in in the start so that is fine I want to retrieve all the values from the worksheet and here I'm saying I want to override whatever is in the Excel data currencies again so I say Excel data currencies and instead of you can see right now I'm sitting here how do I spell qnc's and there's a reason there's a risk of me spelling wrong so I can click this little X here and then I can just choose it here and I can click save so now we have read this into Excel data currencies here in our accounting and remember this one comes after the get currency rates here in our main so now we can read from the accounting sheet so let me just go here wrap this name because now I'm going to set this as active so rename Ctrl C and then I can just close this one here so first I'll say set active Excel worksheet not sure what went wrong here my computer lagged a bit so set active Excel worksheet so in the Excel instance that is the entire book and I can just control V paste in this sheet name and click save again I can read from the Excel worksheet so I go up here so I'll say read from Excel worksheet like this and then I again want to say I want the all the values from the worksheet so here I'll say Excel data accounting and this is why I really want to rename my things well so I say Excel data accounting then I can click save one thing that we were missing here which is very important so open up the two reads go into advanced click this first line of range contains column names we always want to do that if we have headers in our Excel sheet of course so then we can click save now let's save our flow again just to have it backed up if something happens here let's run or flow just to see that everything works you can run it wherever you are even though if you are in initialization accounting everywhere it will start with the main and then perform the actions here so now you can see we actually close the browser now we are writing it in and we're closing so here what I want to show you is the Excel data currencies I can open this one here now you can see that this is updated with the Q and rates and the dates and again the Excel data accounting that one was our data from the accounting sheet so now we can start by saying we want to filling in the values for the exchange rate first and then we want to do the calculation so I want it for each again and now I will and remember to go to accounting I need to iterate to the Excel data accounting so I find it for each like this and then I drag this in for each so I will refer to the Excel data accounting and that I'll click here again double click the Excel data accounting again we'll have a reference name for storing the Q and row so instead of this q and item I will call this transaction like this and then click save so now I'm entering to each one of the rows of the Excel data accounting that was this one here so the strategy here is that for each one of these rows I want to take what's ever in the currency column and then look that up in the actual currency sheet to get the updated exchange rate but then I can click close I need a support variable again you remember the counting variable because we are going to write back and do the lookup so here I find a set variable again we just just do it the same thing as we did before so I'm saying set variable right before the for each here I'll say Excel row index so again just we just use the one from before so Excel row index and we just remember to reset it to 2 because that one is the first row again in the accounting sheet click save similarly dragons set variable in the end of the for each click this little X here and then we'll find the Excel row index here we just add one to it so click the X cell row index and to add one to it again go inside here and then say plus one this will add one to it so now we can do our lookup we look at the guarantee in the accounting sheet and then we get the information about what row that is stored in the currency sheet go up in actions and then find a find or replace like this data table and drag that in here for each so the data table that we want to look in that is the Excel data currencies that one was the first one where we the data scrape the data tool so click here search mode is fine and now what text do we want to to use and that is what text do we want to look for well we want to say in the current row that is a transaction we can refer to that by saying this Alexia let me do that first and then I want to say I also want whatever was in the and I forgot the name so we can just open it again in the end the accounting I want to look in the qng column that one is this one so I want to say in the Q and row Euro Japanese Yen look up that in the currency sheet so let me just close this one here and in transaction made a hard bracket single quotation mark security single quotation mark and then a heart bracket go down to search by we can just limit it to a column here and that column in the first sheet and again let us open that it's called ISO so we're looking in the ISO and then like yeah and this one we can just say ISO remember just to recap this we are looking in the data table currencies and the value that we're looking for we find that in the in the current row that when we're entering through the Excel data accounting and this one will have a data table of matches so in here I can just say ISO and again we could argue that we actually could only find zero sorry one match instead of all matches but we could actually do a check later on to see if we have an error here but for now we just take all matches we will only find one but that is fine we will get used to work with data tables and that is also the scope of this lesson now let's just click run again and see that we can actually get the matching row so here we are launching and we are iterating through this one is where we are screaming the data now we are taking the each transaction and do a lookup and let's just inspect the data table of matches ISO so if I double click here it says that for our last transaction then the result in the currencies table is in row five and let me just show you the transactions so the last transaction that is purchase server from Swiss it that is Swiss franc so it says that Swiss franc is in the fifth position of the qnc's data table so I go back here and I find the Excel data currencies go in here and yes in number five we have Swiss Francs so now we just get now we have the row number we can get the data back and write it into the correct a place in the Excel sheet so and again let me open up data table of matches ISO we want to go into 0.0 to get this value that is the same for each one of the results I click close I go to accounting so now I have found this data table of matches ISO then I can just go up here I need to convert it to because what I'm going to do here is that I'm taking this row number and converting it into a number this looks weird because it was a number but since it is from the excel in the data table it is an object so I'll find a convert text number and one was here and drag it in so I want to say I want to go to the data table matches ISO and again I will say 0.0 and this exercise is so good I hope you do it because you'll learn a lot by referring to data tables so here's a 0.0 the variables produced let's just rename that to something meaningful qnc row like this and then click save so now we have the currency row in in a number format now we use that row number here so find in another right to Excel worksheet and drag that one in below here so this is the Excel instance what value do we want to write well we want to say I want to look something up in the Excel data qncs so I'll say click this x Excel data currencies then I just need to specify well I need a row like this and then I need a column like this so something needs to go in here and I know that it will give us an arrow when we click save here that's fine go over to the Excel data currencies because what I want to do here is that I'll say for the row I will use the currency's row remember that was the fifth here so this one is the row I'm going to look in and then I want to look in the fifth fourth column and again it's zero indexed so I'll say 0 1 2 3. so it will look like this again open the right to Excel worksheet so in the first one I will say whatever I called it up here q and c row so I'll say q0 and in here I'll say three that's it so where do I want to write it back well that was and again I just click save so we are completely sure where we're going to write it back to I want to write it back into Excel data accounting let me open this and here I want to write it back into here so that is column A B C D E and we can refer to them by letters because I'm writing back to the Excel and not this data table here so write to Excel worksheet I'll say x column e and then I will use this Excel row index again that we created up here so I clicked this x here and I'll say acceler index and then just click save so now we're writing the correct currency rates over to the accounting sheet let me just launch it here here we are opening it and we are performing the actions closing the browsers and it's quite satisfying just to see the robot Works while we sit here so no errors at least the robot don't stop not yet and that's it let's go inspect the data uh we actually doing what we want there you go we now have the exchange rate and we still have the correct data over here so now we just need to do some calculation so here I just close and I go to accounting again so after here I will take the values from the Excel sheet and then I want to do the calculation so here again I'll need to find two convert text to numbers so convert text to numbers drag that one in here the first one I'm going to convert to that is the amount so I will call this one here currency amount like this and then I just need to say I want to look in the transaction that is the current row so I can find that one here and then I can say hard bracket start quotation marks and then I'll have my column name which I'll find in a few seconds then another quotation mark and then hard bracket end let's go find whatever the column name is that is where I want to take the value from and that one is here the qnc amount so I'll just do this Ctrl C just to make sure I don't misspell it so then I go in here in the single quotation marks I'll paste in the qnt amount and then I can click save so again I just need to have the exchange rate out and I just calculated up here so let me just move here let me graph this expression like this copy then I can just click save or cancel it doesn't matter drag in another convert text or number paste it in here and here we can call this exchange rate because that's what is this exchange rate like this and click save finally we can do the calculation and that is this one here times this one that will give us the amount so again before we close it let's just just delete every data to see that everything works when we start the robot so here I'll just save it and I will clear it so what we do now is just write the data back to Excel for the last column so here write to Excel worksheet and drag that one in so what value do we want to write here I need to do a calculation I'll say currency amount times exchange rate take the X here qnc amount here and inside these ones here I can make a space that doesn't really matter I say multiply by exchange rate that was the name of the variable here then I just need to divide it by 100. so where do I want to write it in well that has to be the column F that I give me again open it here so in the accounting that will be the column F here and then in the Excel row index that is for each one of these rows close go here so the column that was F then I'll say Excel row index like this then I can click save now just save the entire project just to make sure that we have the backup and now we just run the robot to see everything actually works that we have created all the right actions and that we can write it back to Excel and then we're going to use uh to create some exciting Four Eyes checks and validations but more about that in one minute when we just need to see that this robot actually works go into the Excel sheet open the currency data there you go now we have our data so that's it that's how easy it is to do accounting here in power automate for desktop of course we could have made it much more advanced but for now this is quite Advanced and I hope you learned a lot but let's do the checks we want to send an email each time and amount is above 100 000 so it's actually only this one here then we want to send an email to compliance so each time that this these two times together each time those ones are above 100 then we want to send an email so let's create that just close it here so I need to initialization I will launch an Outlook instance to send my email Dragon the launch Outlook you can just move it here for example this one will create an Outlook instance then click save similarly in the end process we will have a close Outlook like this and then I'll in the end process I'll drag that one in that will close it so now now we can start to send um this email I want to create a subflow for the check so new subflow and then I'll say Four Eyes check like this and then I'll say save here I want to ask a question and I want to call this subflow from accounting just here so actually we can do that first so I can say run subflow so this one will call the for ice check I'll do that for each one of the rows then I will go in here and then come back here so in the four eyes check I will find an if not sure what happened there it disappeared so here and then I'm dragging you so now I'll just say again I'll do the same expression as before I will say a currency amount then I'll say times ex change rate and then I'll say divided by 100. if that is choose the operator here if that is greater than one two three one hundred thousand then we'll do something so this one just evaluates is this one greater than one of a thousand then do something and we know it's only the last row but it could be more so find a send email message to Outlook take this one here drag it in so now we can do it the account that is the account that you're using in outlook.data file name minus this one here and who do I want to send it to well I want to send it to the compliance department but since it's only me and my wife in this company I think it's nice just to send it to myself and don't spam her at least subject well I'll say large amount like this and then we'll have a body plz hello compliance and let's call it her Cali like this I found a large transaction in there and then I'll say 2 a accounting sheet that one was the name of um all the transactions at row index and we have the row index that was the Excel row index so I just find it here and I'll use it here in my email then I'll say kind regards your RPA robot and I can click save now let's try to run it again and just see that we can actually send this compliance email so I will fast forward to the end so you don't have to see this once more and not quite to the end if you like these kind of videos please give the video a thumbs up that will really help me a lot and actually since we sended the email and then we closed the Outlook I didn't get to see the email so let's just right click here disable action and let's just try to run it again so we make sure that we don't close the Outlook but we will do that in robot when it's actually in production means that we will clean up what the robot has done so everything looks fine on either the computer the server or wherever we run these IPA processes so that's it and here I'm just dragging in here here we have our email so far so good we can do all sorts of error handling so let's do one check to see if a description is empty and instead of sending all these emails each time that we see something is wrong then just add them all to a lock and then send that log to one mail only one email that is best practice and we'll send that email when the robot ends in the end process so that was a lot of thing first let's just create a mistake here so what I want to do I just want to delete one of these let's take this one here you can pick whatever you want so I want to do a check if this one is missing this value you can do all sorts of check this is just to show you how you can do checks so let me just save the data and then close it here so I go back here the first thing I want to do is to create the log variable and to that log variable I'll just add these error messages each time they occur so in initialization I want to use the Q date and time and Deadlock so let's go grab those first so find a get current date and time here and drag this one in I want to drag them in right after the set variable here so get q and date and time this will just give us this q and system time do that then we will have a convert date time to text like this I will need to find it here convert date time to text so this will take this daytime convert it to text so that we can use it the date time to convert that is the Q and date and time from here I'll do this and here I need to pick a format for my date time I will just take full date time on time it will look like this it will get stored into the formatted date time so I can use that now let's create a variable for the lock message so find a set variable like this and then drag it in here so I want to create a new variable called I can call it log and then in the value I can say the robot ran at and then I'll use the time up here I'll just formatted daytime like this then I can say with the following results like this and a colon in a complete save so now I've started my log variable and let's fill something into it so go into the four eyes check so the email I want to move that to the end process so control X go to the end process click here and then click Ctrl B now move in here and let me just copy this one out here so this was the error message whenever we have a large transaction I'll copy that one here and in the four eyes check I'll now add to my log variable instead so find a set variable add to the log variable so I will say I will add whatever's in the log variable first you remember we had this date and that the robot ran so I'll say whatever is in the log and it can also be a previous error messages that we have here then I'll say whatever is in the lock then I'll move down here let's have a limiter here so then I will say this I'll just add I found a large transaction this is the exact same log message as before then I can click save so now we're doing the check to see if a description is empty find an if yeah drag that if in here so now we're going to say and because let me just click save and get the arrow here because we're calling this for a four eyes check from this for each here in the accounting we can refer to the transaction item and then say take the current row and then look in the description to see if that is empty so in Four Eyes check open up the if again so take the transaction like this and then in hard bracket quotation mark say transaction single quotation marks hard bracket end then we want to say is this one empty so oh that's one called empty is that empty then do something and again we will just set a variable and we will set the log variable so hopefully we have created it right so we'll find a set variable here drag that one in here here I'll find the log message like this here and then I'll say whatever is in the the log variable so again I'll use that one here and then I want to say in the next line to the script description is empty in the to a was it accounting sheet at row index and then we will just use the Excel rowing so again here and then I can click save so now we have we're doing these two things now we just need to write it out in the email in the end so in the end process here move into this email so instead of this fixed error message deleted and then use the lock click save now let's try to run the robot and let's just save it this is the final run so hopefully everything works otherwise we have work to do again if you want to see a specific use case solving you a problem then let me know in the comment below and I might pick yours to solve next week so uh oh isn't it just beautiful to look at and here yeah we have the arrow we said transaction transaction this should actually be description so again even me I make these mistakes and I choose to include them in the video because I like to show that every developer makes these mistakes during development phase you just need to know how to fix them so no problem if you do this you can see I also do these things and let's just try to run it again so we are launching Excel setting the active sheet blah blah blah and you'll have to hear me talk but again know how to solve your development errors and absolutely no problem in making these errors you can see that we all do it so um now we close the Excel sheet and we just need to see that we have created a log message so let me just grab in my email from the other screen let me open it here to you there you go we have now a lot a nice lock to the compliance Department with the dates here then we have the description is mtn2a accounting sheet at row index 5 and then we found a large transaction at the index 8. great job this can be done much faster and more robustly watch this video up here about rest API calls and XML passing in power automate for desktop

Channel: Anders Jensen

Views: 17,286

Keywords: power automate desktop, power automate desktop use case, advanced power automate desktop, anders jensen, microsoft power automate desktop, rpa for accountants, power automate desktop accounting, advanced excel automation, how to use excel in power automate desktop, accountant use case in power automate desktop, use case for accountants, how to do accounting with power automate desktop, power automate desktop advanced

Id: ZvbnuQp7x5g

Length: 60min 51sec (3651 seconds)

Published: Mon Sep 26 2022