 Hello, everyone. Welcome back to another video looking at Excel VBA. So in today's video, we're going to be looking at some script that allows you to look at a given folder and return in a list, all of the files that are available in that folder. One particular use for doing this and what I've used it for previously is say we've got a number of files within a folder and we want to combine the contents of all those files together into a master spreadsheet. This would or this script we're now going to look at would obviously give you the ability to identify each of those files and you can obviously build upon the script we're going to look at today to then extract data from each of those files in turn. So we'll jump straight into it. What you can see on the screen is I've got both my Excel sheet so you can see on the left here so you can see obviously the output of this code and on the left we've already inserted the module into our VBA panel so we can start doing our script. If it's the first VBA video you've watched and maybe you need to get familiar with like inserting a module or anything aspect around VBA, you can check back on the channel and you'll find a playlist with dedicated videos starting from the basics and building up to where we are today. So you might want to look at that now or check back on that at the end of this video. But either way, the quickest way to do it is simply to go all F11 to bring up your window and you can go insert and module to get to where we are today. So what we've got is a folder I've created and you can see I've got a number of different files in here and these are actually just different or previous videos that we've done on the channel and I just found a selection of them and dropped them into this folder so we could use it as an example. And you can see simply we've got this basic folder path here. What's going to be very key to what we're trying to do because this is the file path we're going to use or feed into our script about to pull back all of these files. So what we need to do simply is going to start coding. So we're going to do a sub routine here. So go call this sub and let's call it get or files. Again, the name is not important is just give it something obviously what's going to be meaningful to you and we're going to be using a couple of new objects in here. What again, you don't need to worry about too much about what they are and I won't go into much detail about them at all because ultimately it's just the code that you're after. If you want to obviously look into more you can by all means to have a Google and that'll give you as much information and text that you want to read as much as you like. So first we're going to define our variables. What we're going to do is dim O F S O as object. So this is these are objects that will enable us to like next to folders and obviously the folder itself going to call folder as folder or as object. Sorry, get myself confused as object. And the last one was a file as object. So again, without going into too much detail, but you can probably kind of see ignoring O F S O. We can see O folder as object and O file as object. Simply put, they are what they are. So a folder is going to be defining as an actual folder and then the file being an actual file. And you'll see that allows us to interact with the properties of the file as we go on and in turn obviously the folder as well. Next we're going to do dim I as integer. This simply just gives us the ability to create a loop so that we can list every file in turn on a separate row. So nice and familiar stuff for previously looked at. And the last variable we need is dim S file as string. So for this one S file path is simply just going to be a string or variable I'm going to store the file path. You could just hard code this string into the actual code. But I often I always like to do it as a separate variable just that way if anything changes or we need to make a dynamic path again it's just separate entity so we can obviously can manage that as we need. So first thing I'm going to do is go S file path so you can define what file path contains what is our file path. So I'm just going to go back to this explorer we had here and I'm going to click in the top part here and copy out this file path. Once I've done that I can get that out of the way again. And simply I'm just going to paste that in there within those two quotes because obviously it's a string. So we've now said obviously to our code this is the folder. So this is the path and we've included the actual folder name in there as well. Enter few rows. So the next thing we're going to do is define our objects that were set up in these variables at the top here. So the first one to do is set a FSO as a equals and create object. Open brackets and then script scripting dot file system object. So you're probably now being able to see what FSO stands for its file system object. So obviously just that small abbreviation should we call it. So this is where we need to now create an object so we're able to sort of the code can understand what we're talking about. And after going to this next one actually goes set folder equals FSO dot get folder open brackets S file path close brackets. And am I happy that I've done that correctly. So FSO dot get folder S file path. Yeah. So we've got the here. So this is where we're sort of defining our file system object. And this one here once we've got this aspect defined we can then say OK we want this to obviously get the file folder. So obviously it's defining the folder as an actual object. So S file path here is obviously going back and looking at our file path here. So this is where I mentioned earlier that you could hard code this file path into there but obviously I've kept it separate so we can obviously manage this separately if we need to. So once we've got those two aspects done next thing we're going to do is now look at our loop. And what we're going to do is you will actually not this not the loop we're going to do the integer. So the integer is obviously where we're going to define where we're going to store our values. So first thing we're going to go I equals two and that will become more clear in a minute while I'm starting it to rather than that one. And now this is where we're going to do our for loop. So we're going to go for each file. So basically for each file in a folder dot files and the next thing we need to do at the end here would be next file. So if I could spell my abbreviation correctly so we've got a file there. So what this is saying is for each file in folder then obviously we can now execute some code. So what this will do is it will go into our folder it will look at the very first file that it sees it will execute this code. Once it's completed it will then go on or it will execute the code we now put in here. Once it's done that you'll then go on to the next file and it will keep iterating for each file within that folder until there are no more files to go through at which point the code or the loop will end. And so what our code as it stands at the moment. So for each file in folders all we simply want to do is we want to go to sheet one. So if you refer to sheet one here and we're going to just put it into column A. So for each file we want to go to sheet one dot cells and we're going to go into cell I because obviously I what we defined up here is going to be our row number. What's going to change and one for the column number dot value equals file name hit enter and all we're going to do here is now that I equals I plus one and that we can look and our code. So what our code is basically saying for every file that is in this folder we want to put that file name in column A. So that will then list them in here. And once it's done that we then want to take our value of I and we're starting with the number two. We simply want to say I is now going to be equal to I plus one. What again simply all it means is it's going to post our file name here in row two. And then once it comes around to the next file is then going to be loaded ready to paste that into row three and so on it'll keep sequentially going down through the rows. So rather than obviously overwriting the file name continuously over the same cell. So I think we've got everything we need in here. So all I'm going to do is select within our sub routine and hit F5. And you can see that obviously it's now populated that with all of the files that are available in our folder. And we can now bring our file. I'll actually explore across and we can see yet we've got six files here and it's brought across the six files as desired. Obviously we left this one at the top here blank so we could just call this file name. And that way by obviously starting in row two every time we're not going to be overriding the file name either. So we could just tidy this up ever so slightly go go like that and then go view. Let me turn off grid lines as well. So if we now delete this you can now see if we now hit F5 over here. It's going to keep continuously populating all those files in that folder just to go one bit step further. We've obviously looked at this before but if you're new as well we can go to develop a tab. Go insert a button. Let's do that and we'll go okay we'll associate that macro or this button with the macro get all files. And then we could just edit the text as well. So let's call this update perfect and then let's just move that there so move here. And then yet every time we now go and push that update button obviously it's going to update those files for us as well. As you can see it's going to continuously do and the benefit of doing this through the code is obviously it may ensures that this list keeps updated. So if we were to go back to our file file Explorer I think I've still got it open or closed it yet there is. So rather than having our six in here let's say we remove the last two files. So we've now only got four files in there. Obviously if we now remove this this date from here and go update you can see that it's now only going to pull those four files. And alternatively if we were to add more files into here so let's say let's go and copy all of these and paste them into there and do it again. So we've got a lot more files now we should have 12 I believe. And now we can get rid of this run this one more time and you can see it's now going to pick up all those files. So just enable us enables us to have this dynamic solution. So we're always ensuring we're picking up every file that is available in that folder. So there we go and that is as simple as that and it's really useful code and as I said earlier you can now build upon this code. So if you wanted to execute something additional here so you now wanted to get some content from each of those files. It's in this space that you can now obviously define what you want to copy and store it here rather than just doing the name or in addition to the name as well. So I hope you enjoyed that video and found it useful if you did please don't forget to give the video a like. It's not only greatly appreciated by me because it shows me the content you'd like to see more of but it does help that all important YouTube algorithm to ensure that more people are able to find this video and other videos on our channel. And if you haven't already maybe like this is the first time that you found one of videos or you have I watched our videos in the past and you're still not subscribed to the channel please do also do me a favor. Hit that subscribe button and also hit that bell notification button as well that way you'll be notified of all of our new videos as and when they come out. So thank you very much for watching and we'll see you in the next video.