In today's Excel VBA video, I'm going to show you how
you can allow the user to select a file from
Excel's Open dialog box. Now I'm going to show this to you with the GetOpenFileName method. But why would you want to do this? It's for flexibility. So you want to give the user, basically the user of
your macro or your file, the flexibility to pick the file on which your macro should run on. And once you get the path of the file, you can do anything you
need to do on the file. You can open the file, you can import certain tabs, look for specific information, basically anything that has a logic behind that can be programmed. The advantage of handing
control over to the user here is that you don't have to fix the path of the file in the VBA code. You don't even have to
type it in Excel cells, you let them freely select it. But the challenge is to get your macro to figure out what they selected. Let's take a look. (upbeat energetic music) What we want to do is to
bring up that Open dialog box, we want them to select a file, and then we want to open
that file in the background and copy a specific range. So in our example we're just going to do a small range of sheet one
and paste it right here, starting from cell A10. Right, so this is just
a very simple example. Later we're actually going
to use multi-selection here. So we're going to open multiple
files in the background, and copy specific ranges
and import them in our file. But here, we're just going to start simple and work with one file. Let's bring up Visual Basic, Alt + F11, and create a new sub-procedure for this. First off, let's just take a look at what this GetOpenFilename method means. Well first of all, this method is a method of the application object. Which means all I have to do is type in application.GetOpenFilename. That's it. I'm going to run this to see what I get. I get Excel's Open dialog box here. You can see the title is Open, it defaults to All Files here, but these are things that
you can control in the code. If you don't put any of the arguments in that's the default that you get. Now if I press Cancel nothing happens. Let's just now select something. If I click this and say
Open, nothing happens. Right, so that file isn't actually opened. Let's see what this does
by going to Microsoft help. So I'm just going to
click here and press F1. The GetOpenFilename method displays the standard Open
dialog box, which we saw earlier, it gets a file name from the user without actually opening any files. So all it does when
the user clicks on okay is that it gets a file name. These are the arguments that it requires, now all of them are optional. So you can define a file filter if you don't want it to
default to all files, you can define it, but
you need to define it in a specific format that's
described here in the remarks. So for example, for Text
files, Add-In files and so on, or Visual Basic files, you
have to define it like this. I'm going to show you
how you need to define it for Excel files. Now you can also overwrite the title, the button text, you can
only if you have a Mac, and you can also use MultiSelect. Let's back to our VBA code and see how we can expand on this. We saw that all we get
is a file name, right? So it makes sense to
declare that as a variable. I'm going to dim FileToOpen. Now we're used to declaring
these as string, right? So let's declare it As
String in this case as well. Then we're going to save filetoopen equals Application.GetOpenFilename. Let's define the title for it as well. Now if you use arguments like this you don't have to follow
any specific order. That's going to be our title. For FileFilter we need
to put it in this way if want it to filter only for Excel files. That's it. Now, let's see what value
we get in this variable. The Watch Window is here, let me activate that by
going to View, WatchWindow. And I'm going to drag this down here. And keep an eye on this value. I'm going to add a break point. Let's run this. Okay, let's select a file, I'm going to go with Test_data
here, and click on Open. Look at the value it got. It's the entire path, plus the file name. Now check this out. Let's run this again, this time I'm going to click on Cancel. The value that it gets is False. But notice that it's put
it in quotation marks, so it thinks it's text. Because I've dimmed FileToOpen As String what it's then going to do is
try to open a file called False and that's not what we want it to do. So if we want to check whether a user actually clicked a valid file or not, we can check if FileToOpen
is False or not. But we shouldn't dim it As
String, but instead Variant because Variant can be Boolean as well. Right, so let's reset this. And now let's click on Cancel, and we see False but False
is not in quotation marks. Right, so it's not recognized as text. This is something that
you need to keep in mind when you're defining your variable for your GetOpenFilename method. So right here we can check if filetoopen does not equal to false it means that an actual file was selected, then, let's do an End If here, we want to open that workbook. So for that it also makes
sense to declare a variable, right for the workbook
that we want to open. Let's call it dim OpenBook As Workbook. Since that's an object we
need to use the set keyword. So, set openbook equals that's
application.Workbooks.Open. This requires the entire
path, which is what? FileToOpen. Now we want to copy data from this. I've specified here copy
range A1, E20 of sheet 1. Let's do openbook.Sheets one, right. So first is the workbook object, then it's the worksheet object, and then it's the range object. So range A1 to E20 .Copy. And we're going to paste it right here in thisworkbook.Worksheets. Now we can work either with
the codename of the sheet or just use the sheet name. The name of this sheet is
called SelectFile, dot, we said we want to paste it in range A10, .pastespecial, let's
paste it as values only with xlPasteValues. When we're opening a
workbook in the background we should also close it after we're done. And we can do that simply
by selecting the workbook, which we've called openbook, .Close. An optional argument for this
is if you want to save changes. We haven't changed anything, I'm just going to put False in here. Now just to optimize this, because we're opening other
workbooks in the background our screen is going to flicker, we can add application.ScreenUpdating
equals False. And then we can set it back
to True right at the end. Let's just test this here. I'm going to insert a button, assign it to our new macro, and just call this Import Data. Okay, so let's click on this. We get prompted to select a file. Now you can go through all
your normal files and folders. I'm going to go here, I have some data in this file that we can test and import here. If you're interested to get
access to the bonus files, or to the PDF handout, or if you'd like to learn
VBA from the ground up in a structured way, please check out the complete course and see if it's something for you. Link is in the description below, it's also in the YouTube parts provided, and you can also find it on
my site xelplus.com/courses. Thank you for watching. See you in the next video,
or see you inside the course. (upbeat music) ♪ Oh, oh, oh, oh, oh-o-o-oh-oh ♪
