Easily Rename All Files In Folders (NOOB vs PRO) & rename files based on an Excel table with PAD

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
Did you see the new order from the headquarters?  They said they want to have our office location   added to all our file names, and they want it done  by the end of today. Oh, that's a lot of files,   but wait, I know how to do that.  Let me get started, get cracking. One hour later... "Wait, you're doing it manually? How else would I  do that? So many ways, but not manual. You already   have a great tool for that on your computer.  Get out of the way, let me show you. Okay,   so pay attention. The tool you're gonna  use is called Power Automate for Desktop.   Great news is that if you have Windows  11, it's already pre-installed for you.   If you don't have Windows 11, but you have Office  365, you just go to your office.com account,   go to Power Automate, and install it from there.  With Power Automate for Desktop, you get to build   simple to complex robots. This isn't a tool just  for programmers, it's a tool for noobs and pros,   so yeah, you can use it too. Once you  have it installed, just open it up, and   If you have any existing automations created,  you're going to see it here. You can give your   automations cool names, like this one, which  reminds me, there is a button below this video   that you can click to make sure you don't miss out  on any cool Office tips. Obviously, learning is   a journey, and every journey begins with the  first step, so subscribe and stay in touch. Now, if you've never created automations  before, you're probably going to see a   button here that you can click to create a  new flow. In my case, the new flow is up here,   so I'm going to click that. Now, I can give  my flow a name. I'm going to call it "Rename   System Files." Power Automate is going to start to  get things ready for me, and we come to this view.   On the left here, I have so many different  actions I can choose from. Under "File," for   example, I get the ability to rename files. But  because my files could be in multiple folders,   I could go ahead and get the files in  the folder first, and then rename these. Before we start with our automation, let's take  a look at what we need to do. So, you were trying   to rename all of these files manually, and you  want to add '"Chicago_" in front of each one. So,   let's go ahead and first grab the files  in the folder. I'm going to click on this,   drag and drop it here, and I'm going to get a new  pop-up where I can specify the folder. So, let's   select it. We have a mapped SharePoint folder and  we also have a mapped OneDrive folder. So, your   files are here, in "Noob", "Work Stuff,' and "System  Daily Data," and click on "OK." Then, we can also   apply filters to include only specific files. Now,  since in this case we want to rename all the files   in the folder, there's no need to add anything for  filter. Will it also work on files and subfolders?   Duh, if you turn it on, of course it will.  And actually, I've realized that you do have a   subfolder here called "Old Data," and we have to  rename all of these files as well. So, yeah, we   are going to turn on "Include Subfolders." Notice  a variable here was produced called "Files," and   this was done automatically for you.This is just  the output of this step, and it's going to become   relevant in our next step. So, we are going to  save and close this and move on to the next step.   The next one is to rename the files. So, I'm going to  click on this, drag and drop it here. What file do   I want to rename? Well, it's not a specific file;  it's the output of my previous step. So, notice   that last icon here: it says "Select variable."  I'm going to click on that, and I'm going to see   Files. So, let's click it and select it. What  type of renaming scheme do we want to apply?   Do we want to add text, remove text, replace  text, change the extension, add date/time,   or add sequential numbers? Well, in this case, we  want to add text. What do we want to add? We want   to add "Chicago_" Do we want it after the name  or before the name? In this case, before name.   If the file with that name exists, what do we  want to happen? Do we want to overwrite the file   or do nothing? Well, I'm going to be on the safe  side and do nothing. Now, notice a variable or an   output of this step was also produced, and if you  are going to have more steps, you can reuse this.   In this case, that's our last step.  We don't really need this. I'm just   going to deactivate this and click on save. And  that's it! All we had to do was drag and drop,   make a bunch of selections, and we get to rename  everything by clicking this one button here. Now let me just bring up my Explorer so we can  see things happening in real time. I'm just going   to snap it here, and now let's run this. We can  see all the names updated super fast, and if I go   to "All Data," all of these have been updated as  well. Imagine having to do this manually! No, no,   don't imagine that. Now, if this is an automation  that you're planning to reuse, you can save it. You know, HR asked me to rename the contracts.  We have to include the employee name instead of   the ID. Do you think you can use your magic tool  for this as well? But each change is different,   so probably not. Of course you can! You can make  dynamic replacements too. We just need a template,   and we can use the HR master file for that.  Let's go over to your desk, let's go over to task   quickly, so I make sure I understand properly.  Here we have the different contracts. They end   with the employee ID. We need to replace that  ID with the employee's name or their initials.   To get their initials, we have an Excel file down  here which gives us the employee ID, their name,   their first initial and the last name, the current  file name of their contract, their manager, and so   on. The information that we need is this one here,  so we need to get this ID and replace it with this   name here. If we were going to do this manually,  we're going to open this file, we're going to take   a look at the Explorer, we're going to go to  "126," scroll down to find "126,"   and then grab this, copy it, go here, and paste  it in, right? So, that's what we're going to do,   and then we have to repeat that for all of these  files. No, no, no, we're not going to do that. We   are going to use Power Automates. I'm just going  to press Ctrl+Z here. Let's set this up really   quickly. Open up Power Automate and create a new  Flow. Give this a name and click on "Create."   Now, how do we start? What do we need to drag  and drop? Well, the way you give instructions   to your robot is the same way you would explain  the steps to a newbie. First thing they need   to do is to open up your Excel Master File so  that they can see what the old ID is and what   the name is. So we're going to go to the Excel  section here and launch Excel. We don't want   a blank document. We want to open an existing  document. That document is sitting right here.   It's in the "Contracts" folder all the way down.  That's what we need. Click on "Open" and save. Next step is to go through this file row by row.  We have data starting from row 2 all the way to   row 60 and we need to go through this row by row.  That's exactly what our robot needs to do as well.   And check this out: there is a loop section  here. We need to loop through the rows. So   let's drag and drop "Loop" in our flow.  We want to start from the second row,   we're going to stop on row 60, and we want to go  one row at a time. The variable that's created   is "Loop Index." This is going to always tell us  where inside the loop we're at, and that's the way   you can reference each single row. We're going  to click on "Save," and we get this visual loop   added here. Whatever we want to happen for each  single row is going to be added inside this loop. Well, what would a person need to  do to be able to make these changes?   They need three different information. They need to know what   is the old ID , they need to know what they  should replace it with (which is this one),   and they need to know the file name. All of this  information we have here in the different columns.   We need to read this information. That's what our  robot needs to do. Under Excel here, we can read   from the Excel file. So we're going to drag and  drop this inside the loop. Which Excel file? Well,   the same Excel file that we opened right here,  which is reflected with this variable. We want to   read the value of a single cell. The start column  is "A" (you can use letters or numbers here).   The start row? That's not a specific row,  but that's the row inside our loop. So we're   going to go and read each single row. We're  going to use the "Loop Index" variable here. Now, if I don't change these variables that are  produced, I'm just going to get dummy variables   called "Excel Data," "Excel Data 1," "Excel  Data 2," and so on. And I'm not going to know   what exactly I'm reading from each file. So  you can change these names yourself. You can   just click and change. So column "A" has the old  ID. Let's rename that and save. Now, I'm doing   this because it's going to make things easier  later on, and you're going to see that in a bit. Next thing we need to read is the new name that  we want to add, and that was I believe in column   "D" (because column "B" seems to be hidden).  There again, the start row is the "Loop Index"   and save. But, no, not too fast. I should  change the variable name so that it's easier   to reference later on. Let's go ahead and edit  this step and call this "NewName" and save.   Now we're going to read the existing  file name that was in column "E,"   Loop Index, and let's change this to  "FileName" and click on save. Now that   we have all the information we need,  we can go ahead and rename our files.   So, that's inside the "File" section here, we have  "Rename Files", we're going to drag and drop it   inside our loop. What file do we want to rename?  Well, it's all our Contracts, so let's go ahead   and select the folder we have these contracts  in, which is right here, click on 'OK',   and now we want to add on the name of the file for  each single row, right? So, we're inside the loop,   we want to add this to that folder path, so  we're going to add a Backslash, and now use   our variable, which was "Filename". What is  our rename scheme? We want to replace text,   what text do we want to replace? Again,  we're going to go with our variable   because we gave them good names, it's easier to  identify, we want to replace the "OldID" with   the "NewName". If the file exists, we could  overwrite it, but I'm just going to do nothing,   and in case we run into an error, we can either  throw the error or continue the flow run. Now it   could be that I have file names in the master  file that don't exist in my file explorer, so   I just want to continue the Flow, Run, and click on  Save. OK, so now that I have everything set up,   I'm launching Excel. Let's also close Excel in  the end, so let's just scroll down to Excel   and close it, the same Excel instance, and we  don't want to save the document. If everything   is set up correctly, it should work. Let's test  it out, click on Play. We can see it's running,   and we should be able to see it loop here. We can  see it's going through each single row, we can   see the result of the variables here, and if you  take a look at our explorer, we should see these   change in real time, and we can see the  numbers are being replaced by the names.   Imagine if we had to do this manually. Thank you  for watching, subscribe, and wish me luck, bye.
Info
Channel: Leila Gharani
Views: 544,726
Rating: undefined out of 5
Keywords: Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Xelplus, power automate desktop, pa, rename files in windows explorer, bulk rename files, rename files in folders, automate rename files no vba, power automate, rename multiple files at once, renaming files, rename multiple files, batch rename file extensions, noob vs pro office, pad, PA desktop
Id: NtFOhvWPlIE
Channel Id: undefined
Length: 12min 27sec (747 seconds)
Published: Thu Jun 30 2022
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.