One of the most powerful uses of VBA is to iterate through workbooks. So it's really important to know how to open different types of workbooks. And in this screencast, I'm going to show you how to open static files. We're going to do that using just the simple Workbook.Open FileName. Then, I'm going to show you how to open multiple static files. When I say static file, I mean that you know the directory, the file path already. You're not having the users select the file path. To open multiple static files, were just going to have a FileNames as a vector. You can also open single users selected files. So, you can have the user navigate using this GetOpenFileName method, where it opens up a box and they can navigate to where the file that they'd like to open is. You can also use the same method for multiple-user selected files. The difference here is that the file names here is a vector and we've got this optional argument here, MultiSelect equals true at the end of our GetOpenFileName method. So let's go through a couple examples. Going to first show you how you can open up a Static File. Now, to get a file path, you can navigate to the folder and in this screencast, I'm going to be using these, file one through file five, that are posted in a sample files done zip folder on the course website. So, if you want to get the file path in the folder, you can right click and do copy address as text. And then, I can paste that up here. So, I'm going to Dim FileName as a string. FileName then, it's going to be equal to, I'm paste that text that I just copied. So, that's going to be the string. But at the very end, because this is the folder, I'm going to do a backslash and then the File_1.xlsx as an example and then, I put a quotation mark there. I also need to put a quotation at the very beginning of this because that's going to be a string. And then, I'm just going to use the Workbooks.Open FileName command to open up that file. So, when I run this using F8, we open up the workbook. And you see then now, I have File1 that's open and available for me to use. Now, if I want to open up multiple static files, then I'm going to Dim file names here as a vector. So, this is a Vector of strings and I'm just going to have two elements in there. I'm going to put Option Based 1 here, because I'm working with the vector. I've also dimmed i, as an integer were going to be iterating over these two files. And then, I put two static files in here, file names. So, the first element of filenames is 1 and 2, and you could have a bunch of these. Now, we're just going to simply iterate through those files. We're going to open up all of those files in our FileNames vector. And when we go through this, then we set those to Filenames in our file names vector and then, we iterate through and we open up the first workbook and then, the second workbook. And you see that both of those are available for me to use. Now, I'm going to show you how we can have the users select a single file. I'm going to dim FileName as a String. FileName we're going to get using this GetOpenFilename method. And here, I have a file filter that's only going to enable them to select Excel files. You can have a couple of optional things here, title and actually look for this first one. Let's set this to false. We don't want them to be able to select multiple files because we only have a single output here for filename. And then, we use Workbooks.Open to open that file name. So, when we run through this, we bring up this open file application, get open file name and then the user can select which file they want. And I can't select multiple files using shift or control. So, that's the multiselect is equal to false. And so we open up this, and that is available, File 4 is available for us to use. Now, if we wanted to open up multiple files using this method, I'm going to Dim FileNames and it's going to be a variant because we don't know the size yet. And I'm diming it as a variant because it could also just be a single file. So I'm going to put FileNames, we're going to use this method and here at the end, I'm going to put true. We're going to be iterating. So, I'm going to dim i as Integer. And then I've added in this for loop for i equals to 1 to Upper Bound of filenames, Workbooks Open FileNames of i. So when I run this using F5, it allows the user to select which files in its multi select, so I can hold down the shift key or you can hold down the control key to select three of the five rows, and I'm going to select all of them, click open. And then, one at a time, it's going to open up those five files. And then, we have those five files available to work with. A lot of times you want to use VBA to open all files in a directory and to do this, we can use the following code and I'll show you this in an example. We're going to set a folder. The folder is going to be defined as a String. You can use a different variable if you want. So here's just an example, we have our file path. So, I'm going to have my folders sample files. We then set FileName, this is going to be also dimmed as a string equal to using this Dir function. The folder, it will find the first Excel file in that folder and it'll neglect other files. They're going to enter into a do loop. We are going to open up the folder, we are going to concatenate the folder with the FileName and that's going to be opened. The next thing we're going to do is you just do Dir, it just goes to the next Excel file up here. So, it'll then go loop and it'll open that second one, the third one, the fourth one, and it's looping until it runs out of filenames. And when it's done, then you can move along. So, I'm going to create a sub here OpenAllFilesInFolder in a dim folder as String and FileName as String. To get the folder string, I'm going to navigate to my folder here, that has my files in it, and I'm going to right click here, and do copy address as text, and then I'm just going to paste that. We have to make sure that we put that in quotations and then we're going to set the file name equal using the Dir function, it's going to be folder and the first Excel file we find in that folder. Then, we enter into a do loop. The first thing we do inside the loop is to open up that we concatenate folder with the file name and we open up that file. Next, we set the file name to the next Excell file, and then we're going to loop until, I forget to put a loop, until we run out of file names in that folder. So, I'm going to run this now. I'm just going to press F5 to run this all, and we go through, and it opens up all files in that folder. So, I've got file one through file five shown down here that we can now work with. The last thing I want to show you is how we can only open files that meet a certain criteria in their name. And so let me show you an example of this. What I've done for this example is I've just created copies of the file one through file five files but change the name to data. So Data_1 is just a copy of File_1, Data_2 is exactly File_2, but they start with different names here. So, I'm going to still use this sample file as my directory but I'm going to only open up the files that start with a capital F. I'm going to use the file, I was just working on it as a Base. But the only difference now is this Workbooks.Open is placed inside of a one way if then. So, if the left of the file name equals capital F, then we're going to only open that workbook. Otherwise, we're just going to move on. So, when we go through this, even though our folder has 10 files, when I run this, it's only going to open up the files that start with a capital F. So then, we look down here and we've got only the ones that started with F. Similarly, I could change this to Capital D. And I could run this, and we're only opening up the files that start with a capital D. So, that's it for all about opening workbooks.
