How To Refresh An Excel File In Sharepoint With Power Automate Desktop

[Music] hello everyone it's jared moore back with another youtube video and in today's video i got a request in the chat from one of my last sessions from last week and i'm going to be creating a power automate desktop flow using an excel file in sharepoint so let's go ahead and open up that request here's the request that we found i believe through linkedin i believe his name is hesham sorry if i mispronounced your name basically he was saying hey brian which is brian julius one of our enterprise dna experts he was asking if there's a way to have a power automate open an excel sheet in a sharepoint folder refresh the power query then save the workbook and then the other part was to be done all online in the background without anyone opening the workbook with this flow that we're going to create today i can do all of this that hesham mentioned in the request the only thing is doing in the background when you schedule this flow to run or whenever this flow runs you can't do it in the background and the machine that you do this on will have to be awake so that it can perform the flow when you trigger it however you want to trigger to begin the flow so that's a little bit about the request today let's hop on over to power automate desktop and we'll set this one up now that i've clicked over here into power automate desktop what i'm going to do here is click new flow at the top left of the screen and then we'll just name this one sharepoint test for today we'll hit create file that'll take just a second here to load now that this screen has opened up what i'm going to do is get rid of this variables tab here so we can get a little bit bigger of a screen and i'll make this a little bit bigger too so when i open up some of the these actions on the left hand side you'll be able to to better see what i'm talking about what i really like about this request is in the past with all the videos i have done it's all been with either extracting data from the web or it's been web functions and i do get the question all the time is can you do this with excel word or any kind of other type of of documents besides just doing it through the web and the answer is yes so today what we're going to be do doing is opening an excel file in sharepoint so there are a bunch of actions for excel so i'm just going to type in excel here and this is very intuitive it's going to show you here of all the different things that you can do in excel that are that are actions from the the screen here in powder automate desktop first thing that we're going to do today is we're going to click on this launch excel so as i open that up now that this is open it automatically goes to with a blank document so if you were going to open up a blank document you could do this but in today's use case what i'm going to do is already use a file that i have saved in sharepoint to access so what we're going to do is from the drop down menu and open the following document and then we can click on this folder right here and this will bring up the path that we want to select i'm going to go over here to my sharepoint folder list here and scroll down and this is actually challenge one data from way back when we first started our challenges here at enterprise dna so if i click that file right there and hit open make an instance visible that's what we're going to have checked because we don't want it to be read only and then what we're going to have to do here is scroll down this list and click on the advanced tab as the advanced tab opens what you're going to do and in my case is or or in your case too if you have sharepoint you're going to have to enter the password to your sharepoint folder so that the file will open so give me just a second and i will enter that password here and hit save okay so now we have our first instance so we're going to launch an excel file so the next thing i'm going to do here is i'm actually going to open this file up so i can so i can explain what i'm going to do before i actually create the steps here in power automate desktop so let's go over here to my and click on the challenge one data it's going to open up the excel file here and as we can see here we have all the data from challenge one that we had in the enterprise dna and what i want to do from here in power automate desktop is click on this query tab so that's going to be the next step that we create and then i'm going to click on this right here to refresh power query then the third and final step would just be to save the existing file and then the last step actually would be to actually close the excel instance so not a tough one that we're doing today but this uh i just wanted to show you this real quick so we know what we're working with before we actually go in and do this once i've done completed this i'll run the the flow so you can see it in action so let me close this down right here we'll keep this open i'll just minimize it go back here the next step that we're going to do is within here i'm going to get rid of clear this out now that that's cleared out from here what you're going to do is there's a bunch of as you scroll down here it's called ui automation now these are not all the steps but these are a majority of the steps that you can use within a a word an excel file anything that's that's ui and not necessarily web-based so i'll open that menu up right here for this next step i'm going to hit select tab in window in order to do this i have to have that excel file open so i need to go back in and open that so i'll just pause the video for now and i will open that file back up okay so now that we've got that open again we're going to click on select tab in window that'll appear on the screen here and then the tab what it's going to do is it's going to ask you to pick a ui element so we're going to click on the add ui element button that will open this up here i'll move this over just a little bit so we can see and what i'm going to do is just move my mouse over the query button here and then you're going to hit just like the directions tell us to control left click and then what i always do is hover my mouse over the looks like three boxes there to make sure that it does it does have that um once you hover this over will tell you that you're you're clicking on that so i'm going to hit save so that's our second step we wanted to select the the tab in the in the window here for the to open up the query editor so the next step in our process is we're actually going to click on the refresh button now to refresh all of the data so what i'm going to use from this selection is called click ui element in window and the ui element once again we're going to have to add another ui element so i'll click this button and then i'll move this over so we can see what we're doing again and i will literally just go over here to the refresh and control left-click again and i'll hover over just to make sure we got that nice and clear we do and then i will hit save now that we have that now that this is done we've refreshed it now we just need to actually close and save the file so that the last and final step is going to be close excel which is not in ui automation so i would just click on excel again and this will be close excel when i click on close excel this is the instance that we have open here that we created from opening up the original file in the flow and then you have two options here to either do not save our three options do not save save the document or save as since this is already an existing document what we're going to do is just hit save document and now you can see in this final and fourth step here that it does say save the excel document and close the excel instance so we have our not only our save function but we also close the document in this case right here so now what we're going to do is actually test and see what we did to see if it works so what i'm going to do here first is click on the play button here and hit run it'll take just a second to launch that file up once it opens the file here we'll hit the click the query tab right there we'll click on the refresh button the data has been refreshed now it's saving and closing the file boom easy peasy what we'll do next here is go over to my sharepoint folder just to show you that the file did save right here so we do have challenge one data and today is sunday the the sixth of march and it is 11 56 a.m so the file was updated and saved thanks again for the comment in the last youtube video and the request for a session today and if you have any other future requests make sure you add them in the comments section below once again thanks for attending hey everyone thanks for tuning in to enterprise dna tv if you enjoyed the contents covered in this particular tutorial please throw the video a like it really helps us and we really appreciate it also don't forget to subscribe to the enterprise dna tv channel we have a huge amount of content coming out all the time from myself and a range of content creators uh all dedicated to improving the way that you use power bi and the power platform lastly check out enterprise dna's website plenty of resources and further learning that you can access very easily all the best take [Music] you

Channel: Enterprise DNA

Views: 67,004

Keywords: Power BI Tutorial, Power BI For Beginners, Power Automate, Power Automate Tutorial

Id: 4Qoh9t_ZvqI

Length: 11min 40sec (700 seconds)

Published: Fri Mar 11 2022