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.


  1. Andrew,

    This is a nice tool. Thank you for sharing.

    A suggestion for developers using multiple screens, the following code edit will place the userform over the active workbook so it pops up on the screen you expect it to.
    (sorry for not using code tags, the html tag for code appears to not be allowed)

    Option Explicit

    Public Const sFilePickerName As String = "File Picker"

    Public sPickedFilePath As String

    Private Sub FilePickerExample()

    sPickedFilePath = vbNullString

    With ufFilePicker
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With

    If Not sPickedFilePath = vbNullString Then

    MsgBox "Selected File: " & sPickedFilePath, , sFilePickerName


    MsgBox "No file was selected.", , sFilePickerName

    End If

    End Sub

    The edit consists of replacing the "ufFilePicker.Show" with:

    With ufFilePicker
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    End With

    If you want to get creative with where the userform appears, you can adjust the "0.5" values and see how it responds.



  2. Dave,

    Glad you like them.

    I've added the code and uploaded the new files.

    (I set StartUpPosition = 0 on the form properties, but use your code to set the Left and Top, as Application.Width and .Height will be case by case)