VBA to BROWSE & COPY Data from SELECTED File in Excel

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
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 ♪
Info
Channel: Leila Gharani
Views: 319,168
Rating: undefined out of 5
Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, Excel 2010, Excel VBA, Excel Macros, Excel macro to browse for file, GetOpenFileName, VBA copy data from one workbook to another, excel macro copy data from one file to another, excel vba tutorial, watches in vba, File dialogbox, vba get file, getopenfilename failed
Id: h_sC6Uwtwxk
Channel Id: undefined
Length: 9min 59sec (599 seconds)
Published: Thu Sep 26 2019
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.