After you’ve worked with VBA for a while, you’re going to want to prompt the user to open a file. Maybe they need to go and select the latest download from their database, or you need to import the contents of the latest sales data. Whatever the scenario, you’re going to need to access the Windows file dialogue.
Over on the forum part of our site, we’ve detailed a method you can use regarding the msoFileDialogFilePicker method. It involved creating a custom user form and reading in the selected filename.
Here’s an alternative: use the built-in system dialogue box.
workbooks.Open(Application.GetOpenFilename())
The Workbooks.Open() command is straightforward: in the brackets you can input a filename. It’s common to put a variable in there. In this case, we’re using the Application.GetOpenFilename() command which executes the standard File Open dialogue box for Excel. The workbook selected will be opened.
There’s also a number of arguments for the GetOpenFilename property, filtering by types, allowing multiple files to be selected… here’s a full description of the GetOpenFilename arguments.
Want to find out more VBA tricks and tips? Attend a Best STL VBA training course.