Automate Saving Files to a Specific Folder with Power Automate Desktop

hello today we're doing a walkthrough of browser automation actions in power automate desktop so I'm going to kick this off so you can watch it while I talk I tried to fit everything on the screen we'll see how successful I was so what this is doing is it is generating reports from a website based off of kind of like a Hit List in Excel so it's got a list of the things that it's going through and pulling and it's using some information in that spreadsheet in the form when it submits the um the settings to generate right so based on a ID or selecting something from a dropdown that kind of thing and something you want to think about before you build something like this is whether or not you have direct access to the data that these reports are based on because it's always better to say um connect to a database Source in powerbi and make a report off of it there and just schedule Refresh on it and it is to do automation that goes through and clicks all of the buttons it needs in order to export the thing from the browser however sometimes you don't have that so um you did maybe notice that there was a error message on the browser window that's fine it is still exporting their report it's it's functioning um the automation browser is a little bit wonky sometimes so um we're going to go through exactly how to build this step by step and you can see that it is both saving the file to I'm pointing at my screen like you can see what I'm pointing up but it's downloading a file to a folder that folder is syncing to SharePoint and it is then emailing the file uh using Outlook and all of these actions are included for essentially for free this tool comes with Windows the thing that is costly when you start using this is scheduling so if you wanted to schedule these activities versus pushing the play button and having them run um those are two different licensing categories basically so let's go through how to build this so to start with I've got an Excel file here this is essentially going to be the list of things we're going to iterate over when we're generating exports in the browser so I have a few Fields here you probably don't need all of these but I use them in the flow so that's why they're included so we have an ID column so it's like the client ID you can use this when you're filling out the form to say search for something by ID have the client name so these aren't real clients by the way this is a public website that I'm running this on just as an example um the name I use in the file name so you can insert that into the file name when it downloads so that it is named systematically and the report column here is used to select a report from a drop- down menu to choose which report we're exporting and then the email to is what it uses when it sends the email and you can separate multiple email addresses with a semicolon and that way it will um send to each of the people in that list and you want to insert a table around this data so you just select it and then go to the insert menu and then click on table here and it'll add a table all right so in our new flow the first thing we need to do is to open Excel so we're going to search for the word excel we get a bunch of excel actions here so launch Excel and when we launch Excel we want to open the following document and then choose the file we have that has the list of actions we want to perform and click on Save and now we need to get the data out of the Excel file so that is read from Excel worksheet so read from Excel worksheet what you can do with that is set it to retrieve value of named cells will let you use the name of your table that you inserted around your data and call only that data so that would be if you haven't changed the name of it it'll always default to table one and it saves that data into a variable called Excel data so we're going to save that and now we need to open a browser so for the browser actions in this case we are going to exclusively use the launch new Internet Explorer and bear with me for a second I know it's Internet Explorer it's old news whatever the reason we need to use Internet Explorer is because it has this launch mode of launch automation browser and that is important because it lets us use certain actions that we can't use with the other browsers and we're going to need that for the downloading the file because we want to perform actions on the download so we need that and then give it the URL and for the windows state for Internet Explorer specifically it opens in a teeny tiny window that you can't see anything in so I like to set it to maximized and this would be the point where if you need to log into the site that you're using you can record actions for that so uh mine doesn't mine is a public website but you can use the same technique that I show in the later part of this video to fill out form fields and it does have a special action for filling in passwords that's encrypted so that's cool um you'll notice I just played the flow and that is to open the automation browser so we want to be able to refer to things in the automation browser so we need to open it in order to do that so I just ran it to get it to open and I'm just going to close the Excel file zoom in a little bit okay so now we need to add a loop so we are going to for each Loop so that means for each of the rows in our Excel spreadsheet we are going to perform actions so for the value to iterate we just select our data that we pulled from Excel so that is going to be Excel data here and it's going to automatically that into current item so what that does is it takes each row in the Excel spreadsheet and does actions on each row and it stores all of that information about that row so all of the rest of the columns in current item which means you can refer to those columns for that specific Row in your actions which is cool all right so we've got our report type Dro down here our flow is going to need to select something from this menu for each of the rows so that was that third column in the Excel spreadsheet if you recall so this one here is telling us what value to select in that drop down menu so to refer to that what we do is under the browser automation category here I open this up there is a whole section for web form filling and it's got a set drop-down list value on a web page so that's going to set a drop- down list option and for the UI element um this is asking what do you want it to select from so we need to tell it to use that drop down menu so to do that we just click on the and that's going to open up this UI element picker menu so you hold down control and click on the thing you want it to select from and then it stores it into the UI element Dro down here so instead of clear all options we want it to select something right so we're going to tell it select options by name you can also do by index so if something is always in the same position you can give it the position instead of the actual name of the option so for option name we're going to get that from our Excel data so we're going to refer to the variable that our Excel data is stored in so I'm going to do percent sign current item which is our current item and then I'm going to give it the column position that that data was in so it was in the third column that's actually position two because we're using computer speak here and computers start at zero and we're going to put that in square brackets and then close our percent sign so variables in power automate desktop always are referred to by the percent signs on each end of them it's how it knows that you're talking about a variable so this is going to take whatever is in that column and put it in the drop- down menu so we can save that so I'm going to actually do that over here so that we can get the actions we need to report perform next so one of the two reports I was going to use is this one so caller report so since I decided to make this more interesting and pull from a couple of different reports for our actions we need a either a if or a switch so those are under conditionals here we're going to do case and switch so we're going to do switch first and the value to check is going to be that same column in the Excel spreadsheet so we're going to say current item position two or actually three if you're human and then we need to add our cases so we're going to say if it is this then do these things if it is that then do those other things so we're going to say is equal to and then our value was call report and save so if the report type is call report we need to fill in some Fields so that would be this unique identifier and that's going to be from our Excel file also and then we need to select some dates because that's a required field so for the report date if I open up the drop down menu you'll notice that the dates are always sorted so the newest dates are always on top so if I'm doing this on some periodic basis what I probably want is to refer to these by the position so like say this particular report doesn't seem to populate until a couple of them down so I'm going to do like say this one here so position three it's number four but position three because we're talking to a computer so I want position three so we're going to say select an item from a dropdown so that's set drop- down value uui elements add UI elements and we're going to control click on that drop down menu and it created it but it didn't Auto Select it this time because it's buggy you'll notice that it says the date in here we're going to go and look at that UI element to make sure that it's selecting the right thing because this dat is going to change right and we want it to select options by index and give it position say position three okay so if I want to go look at this UI element to see what it's doing I can select the UI elements from the sidebar over here and they're all listed out so you can rename these I highly recommend renaming them especially if they have kind of bogus names so this one I could call report select date and you can see what these actually are if you go into the edit menu so you get a preview of the selector that it's using here so this looks okay and then you can also see the tree of items that it is under here so I'm just going to close this and now we need to set the unique identifier field in the form so we're going to use this populate text field on a web page and for UI element we can do and I actually want this text box right here so I'm going to control click it and it didn't select it again so let's go ahead and select that this one here is I was going to add it last that's how you know which one it is and for text we're going to give it our ID column so that was percent current item and that was the first column so that's position zero click okay and now we need one more action to click the button so we want this click link on web page because the button that we're clicking isn't the download file button we're going to get to that one in a little bit all right so for our UI element click on ADD UI element again and it really likes to minimize everything when it does this super awesome so we're going to control click this generate button up here and it didn't Auto Select it so I'm going to select menu and click save now if I go and click that over here I should probably put an ID in the box that it doesn't fail let's see let pick one of these so I'm just moving this along on generate so this website is really weird in the automation browser it does this thing where it tries to download the web page when I click on the generate button which it's really not the file that we want it's the aspx page not the file we want the buttons up here to download the file so what we need to do is close this window and the easiest way to do that um since you'll see the cancel button is highlighted here this is one of these like weird power automate desktop tricks that you kind of run into occasionally sending Keys is a really good way to work around any kind of issues you're having when you're not sure how to get power automate desktop to select the thing you want so what we're going to do is since this cancel is already selected here we're just going to send keys to the keyboard and use the enter button to close this window so I'm going to search for send keys in the menu over here and just drop it right here and we're going to go to the miscellaneous menu and it's send the enter button to the foreground window and the thing with all of these actions is we need to start adding some pauses and some weights in here because if these fire right away when the page loads they're going to fail because the thing that they're trying to say click on doesn't exist on the page yet because it's still loading so anytime you are opening a new page in a browser you need to add a weight action before the first thing that you do with stuff on that page so knowing what I know about how this FL goes I'm going to move this launch Internet Explorer action into the for each Loop and we're going to open and close the browser each time this is only because I had a really hard time getting the flow to recognize the button actions in the loop so when it would use the back button to go back in the browser suddenly it couldn't find any of the components anymore so um I'm just going to close and reopen the browser on the loops to try and avoid that so we're going to launch the browser there and then we're going to wait so we going to wait for page content we're going to wait for the drop down that we're going to click on to exist on the page that's pretty straightforward right so we just select our UI element that we want to wait for so that's this select report type and if you want to you can have it failed with a timeout error if it times out but I don't think that's going to happen here so usually that is more of a thing for janky websites that sometimes never load you want a timeout error for that so there's our wait for that one when we select the report type it opens another new page so we need to add a weight action before it is setting the drop- down items here so we can add a wait for page content and then wait for it to contain the date selector before we select the date and we don't need to add a wait for the rest of these because by that point the page will have loaded and now we need to add one more wait for the send Keys action here because that um this clicking the generate button also opens a new window so for this one we can't necessarily wait for that random weird popup window because that's not actually part of the web page so I'm just going to give it a time so there's a weight action that for some reason doesn't show up until you open this particular menu the rest of them expanded by default this one didn't so flow control wait and you just give it a number of second so I'm going to say maybe 3 seconds just in case it's probably more than we need but whatever so that's going to close this little popup window and then what we need is the download file so click a download link on a web page so this is the one where um it's possible to download the file by just clicking the link however if you do that it's going to go into your downloads folder and then you can't do anything else with it so we're going to use this click download link on the web page because that will store the information about the file in a variable and we can refer to it later so for the UI element we just add our UI element so I'm going to do the PDF for funsies here so we're going to control click that and for the destination folder we need to give it a path that we wanted to put the file in and what I've done is synced a document library from SharePoint and we can use that as our destination folder so that anything that we put in that folder gets synced to SharePoint so if you're not familiar essentially in any SharePoint Library there is a sync button right here if you sync it it'll make a folder on your computer under whatever your tenant name is and then this will be the site name and then you can grab the pth path so if I open up this folder click in the address bar up here I can copy this and use this for the path and actually what I'm going to do instead of pasting this in directly is I'm going to put it in a variable because we're going to refer to it more than once and that way we can change it in one place and it'll be updated everywhere we're using it down the road so I'm going to save this and it's kind of angry with me that's okay so I'm going to do a set variable and I'm going to put this at the beginning of the flow we're going to call this file download F and for the value I'm just going to paste in that path that I copied earlier and now we can refer to that down here in this step so we just go to the little X icon and select our file download path click on Save and then we're going to close the browser and again you don't always have to close the browser every time I just found that that worked better in this particular case and we don't have to do any configuration here just save it and now if I want to rename that file if I search for the word rename in the actions there's a rename files action and I'm actually going to put that outside of the case statement cuz I want to do this for both kinds of files for files who rename just select from the variables your downloaded file variable so that's the one that was generated by The Click download link on a web page action and you've got some options for how you rename it in this case we're just going to set a new name and for the new file name I'm going to use the name of our client so that was percent current item that was the second column so that's position position one so that's our client name and then I could do like a space or a hyphen or something like that or maybe an underscore I'll do an underscore and then our report type would go nicely here so that is percent current item and then I think that was the third column was that the third column yeah so that's two so position two so what this is going to do is it's going to do client name uncore report name as the file name you could put the date in here too if you want if you search actions there's an action for the date but the date is going to be in the created date for the file in SharePoint so I feel like that's kind of unnecessary you can choose whether or not you keep the extension we want to keep the extension and then that'll make a new variable here called renamed files so next I'm just going to add our second case statement here for our other type of report that we're generating so this one was called was it pure group average report I'm just going to copy this all right so I'm going to run this just so we can get the automation browser open and make sure everything is working it's a good idea to save these frequently by the way because it doesn't seem to have an autosave functionality and I like to add a break point so maybe like right here so the breako is going to stop it from continuing the loop it's just going to Loop one time for our testing because we don't want it to keep going all right so it's coming up with an error but that's because we haven't added the actions for the peer group average report yet that's fine let's do those just trying to rename a file that doesn't exist so for peer group average report I actually don't need to set either of these drop downs I can just click on generate um you could select in the same way that we did before uh a different date frame for this using the set dropdown list value on web page action so all I need to do here is click on generate so by the way you can add UI elements from the UI elements pane over here you don't have to do it through the action so I'm going to do that just as an example and I'm going to rename this so we know which one this is okay so if I click this what does this do opens this page okay so we just want to click click a link so click a link on a web page don't use the click a UI element in a window we want the link on a web page select our UI element that was this one and we also need to add a pause in here because selecting that type of report loaded a new page so I'm going to add a wait for web page content and I'm going to wait for that UI element that we are clicking on to load all right so this new window that opened has a download button here so we need to add an action to click on that and that one is something that'll actually download the file so we want the download option here the download link on a web page and this one is also going to need to pause so we're going to add that in a second for UI element add it the same way control click that download button and destination folder just give it the variable we created and for the variables produced I don't want this to make a second variable I want to reuse our existing variable because it's only ever going to set one of these for every Loop so we're just going to take that two out of there we need another weight so we're going to wait for the download button this one okay all right so for Outlook we have two groups of actions we have this one which is the desktop version of outlook on your computer and we have the Microsoft 365 Outlook actions here these ones are going to be premium come with additional licensing cost so I'm going to use this one I'm going to put this at the top of the flow right under the launch Excel doesn't require any kind of settings it's the email that we have to configure so for the email we're going to use a send email message through Outlook and we're going to put that right below the rename file step and this one requires you to enter an account it's got to be something that you are logged into in Outlook right and if you want to you can opt to send it from another mailbox for the two we're going to going to get this from our Excel data so percent sign current item position three I think it was and for the subject you can put whatever you want in here this is able to reference your Excel data so you could put the report name in here so we call this quarterly this is going to be the report name here same thing for the email body you can refer to the columns near Excel spreadsheet here too it's my very creative lengthy email body and for attachments this is the important part so you need to to go to the select variable here and select your renamed file so that's this one because that's the version we want to send right all right so click on Save let's see how we did I'm going to turn off this break point I'm going to move this Clos web browser step to outside of the switch so that it works on both and let's go ahead and run this it is completely normal for your flows to have errors the first time that you run them by the way so please don't get discouraged by that if it happens troubleshooting is part of the process for sure you'll notice that it's typing out the path in the file name field in order to save it to a specific location find that kind of strange but that's how it works all right and that navigation to web page cancelled is fine that's from closing that little popup it's still downloading the files and you can see the emails coming through too here all right so this looks successful

Channel: Christine Payton

Views: 3,493

Keywords: tutorial, microsoft 365, power platform, Power Automate Desktop, PAD

Id: VDV4gS-C-NY

Length: 22min 23sec (1343 seconds)

Published: Wed Nov 15 2023