A common requirement for VBA macros is to let the user choose a file, often the file will have some data that the macro is to work with, or perhaps the macro will update the data already in the file. Whatever, it is possible with VBA to show a file dialog and let the user choose a file.
Here's an example:
This is a self-contained Function that other code can call, for example like this
Some explanation of the code in the TryGetFile() Function:
It uses a 'try pattern' of returning a Boolean (with True for success and False for failure) and updating a 'ByRef' parameter when successful. This improves the clarity and simplicity of the calling code as it does not have to validate the file path or file type itself, just whether the returned value was True or False. When the return value is True then we know that the user chose a file and that the type of file was the type we wanted, your code can then do with that file whatever it needs to do ... in this case it would likely be to read some text or open the CSV file. When the return value is False then we know that no valid selection was made and your code can handle that appropriately.
ByRef (i.e. passing arguments 'by reference' as opposed to ByVal passing 'by value') is the default for VBA. However, as the argument is being updated by the Function then I always like to explicitly use ByRef and I always prefix an argument that will (or could be) updated with 'out' ... these are both just coding conventions I use and are not essential.
The various uses of Filters.Add allow the user to select either .txt or .csv files. If you wanted the user to select only one type of file then you could remove one of these lines ... you can also add more lines to allow for 3, 4 or more types of file ... conversely, if you were happy with the user selecting any type of file, you can remove both of the Filters.Add lines - the filter will then show "All files (*.*)". See the image below that displays the result of the two Filters.Add lines used in the above code.
The first argument of Filters.Add is the name in the filter, the second is one or multiple file extensions, for instance if you only wanted the user to load an image file but it could be multiple types of image file, you could use the following line
The Show method returns -1 when the user presses the 'Open' button to select a file (it returns 0 if the user presses 'Cancel'). You'd think with the filter applied then you would not need to check the type of file such the above code does using StrComp, however, this is necessary as, despite the filters, any type of file can still be returned when the user presses 'Open'. This is because the file dialog allows the user to select a shortcut to any type of file, not just those specified in the filter (thanks, Microsoft!)
It's really important to note that the FileDialog object retains the values of many of its properties from one instance of showing the file dialog to the next ... this means:
It is important to set values for all properties that you need to have a specific value eg, in the example above, the AllowMultiSelect property is set to False in case the previous time the dialog was shown this was set to True; for this same reason, you should always also call Filters.Clear
A benefit of this retention of property values is that the file dialog remembers the location that was last being looked at by the user ... in my experience, this is the behaviour that users expect with file dialogs. However, if you want the file dialog to open at a specific folder then you can do this using the InitialFileName property to set a specific folder, file or group of files (see the link to the MS Docs for FileDialog, below, to investigate this further)
And a note that the InitialView property has no effect ... without resorting to Windows API calls, I know of no way to set the initial view (eg to 'List', 'Details', 'Large Icons' etc) for the file dialog ... however, I think this is mostly a good thing: as with remembering the last folder location, this means that the view the user last used is also remembered - and the user knows best what view they prefer to use and we shouldn't go imposing something else on them
The resulting file dialog shown by the TryGetFile function (with the filter drop-down showing) looks like the following:
There are a number of other customisations you can make to this file dialog ... you can change the title text (I set it to "Select file" using the Title property), you can set the name on the button the user presses to select a file (I set to "Open" using the ButtonName property) and you can allow the user to select multiple files instead of just one (using the AllowMutliSelect property) ... but this blog is about choosing a single file, so I'll leave that as an exercise for the reader (or perhaps a future blog post).
Finally, it is also possible to use the FileDialog object to let the user select a folder (as opposed to a file) or to use it as a 'Save As' dialog - both of these controlled by using a different MsoFileDialogType enum member, but again that's outside the scope of this post.
For the MS Docs on FileDialog and various other code used in this post, see:
FileDialog object: https://docs.microsoft.com/en-us/office/vba/api/office.filedialog
MsoFileDialogType enum: https://docs.microsoft.com/en-us/office/vba/api/office.msofiledialogtype
StrComp function: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/strcomp-function
Right$ function: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/right-function (appending the $ makes the function return a String instead of a Variant holding a String, which is what we want in this case)
ByRef: https://docs.microsoft.com/en-gb/office/vba/language/glossary/vbe-glossary#by-reference
Σχόλια