Custom Forms: File Chooser

If you are developing solutions using Excel and VBA, you will probably come across the situation where your user must navigate to a folder and select a file from the OS to feed some process.  One example might be a report generator that imports data from an existing workbook to produce a new report.  You can use this technique to provide a file picker for your users.

On a user form, place a text box (call it "txtFile") and a command button (call it "cmdPickFile").  Make sure the text box is large enough to display the directory path and file name your user will select, and put the command button "inside" the right edge of the text box, like the Import File field on this dialog:

Set the command button's caption to be an elipse (3 dots) which is a standard and intuitive Windows construct indicating that clicking the button will allow you to select a file.

In the button's click event, put the following code:


Private Sub cmdPickFile_Click()
  Dim strFile As String

  On Error Resume Next

 'set default dir
  ChDir$ ThisWorkbook.Path

 'show file open dialog
  strFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls", 1, "Select File", , False)

  If strFile = "False" Then Exit Sub

 'drop name in text box
  txtFile.Text = strFile

End Sub

This routine leverages Excel's GetOpenFilename method, which displays a standard File Open dialog, allows you to navigate through the directory tree, and select a specific file.  This doesn't actually open the file, it simply captures the selected file's name and path.

If the user clicks the Cancel button on the standard dialog, the GetOpenFilename method returns "False", so we test for that before capturing the selected file's info and putting it in our text box.

The advantage of this approach is that the selection is visible to the user, and could in fact be typed in, or pre-populated by our macros, depending on the situation.  The line that sets the default directory could be adjusted to make a pre-determined path the default.  Also, you can change the file filter ("Excel Files (*.xls),*.xls") to show other types of files, in case we need to process data that isn't in Workbook format.

In this example, the user must click the OK button on our user form to start the import process.  Our code simply needs to look in the txtFile control to get the full path and file name of the file we want.  This file could then be opened using code like this:


Dim WKBK as Workbook
Set WKBK = Workbooks.Open(txtFile.text) 

It may seem so simple, but unless you've done it, these sorts of details can be time-consuming to figure out.  It's worth doing, because intuitive, user-friendly controls such as this make your applications work the way user's expect, and this get you hired back.