Thursday, 11 June 2015

Folder Picker and File Picker

There's more than one way to pick folders and files using VBA. For various reasons, I've found them wanting so I'm reinventing the wheel.

With my versions, you can't add a new folder, either with a button or a right-click menu, or anything else for that matter. There are no "Tools" or anything else that a user might potentially click and possibly create a problem. Things are locked down tight. Users can pick a folder or file, and the path is recorded as a string. Or they cancel. I'll assume your code is set to handle the rest.

Here's a pic of the folder picker.

And a selected folder's path.

The file picker.

(Hmm, the file picker picking an image of itself, that's narcissism and shades of Inception at the same time!)

And a selected file's path.

About the code - basically it's a combination of Dir, FileSystemObject and Windows Script Host. FileSystemObject has some great methods and properties, but is slower than Dir when working with network drives, and I use Windows Script Host to get My Documents, the default folder to start with on my pickers, and also to get network paths (Universal Naming Convention) as opposed to mapped paths, (hope I got the terminology right, let me know if not!).

The code also uses SaveSetting to record the last used top folder, which means you can easily navigate to the last location. If the path has been changed, such as a folder being renamed or deleted, the pickers use the default (My Documents) instead. If you prefer not to use the registry, feel free to rewrite the code to your own requirements.

Download the example files here.

Folder Picker

File Picker

See you next time.