The Excel GetOpenFilename Method

If your application needs to ask the user for a filename, you can use the InputBox function. But this approach is tedious and error-prone . A better approach is to use the GetOpenFilename method of the Application object, which ensures that your application gets a valid filename (as well as its complete path ).

This method displays the normal Open dialog box, but it does not actually open the file specified. Rather, the method returns a string that contains the path and filename selected by the user. Then you can write code to do whatever you want with the filename.

The syntax for the GetOpenFilename method is as follows (all arguments are optional):

 ApplicationGetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) 

• FileFilter : Optional. A string specifying file-filtering criteria.

• FilterIndex : Optional. The index number of the default file-filtering criteria.

• Title : Optional. The title of the dialog box. If omitted, the title is Open .

• ButtonText : For Macintosh only.

• MultiSelect : Optional. If True , multiple filenames can be selected. The default value is False .

The FileFilter argument determines what appears in the dialog box's Files of Type drop-down list. The argument consists of pairs of file filter strings followed by the wildcard file filter specification, with each part and each pair separated by commas. If omitted, this argument defaults to the following:

 "All Files (*.*),*.*" 

Notice that the first part of this string ( All Files (*.*) ) is the text displayed in the Files of Type drop-down list. The second part ( *.* ) actually determines which files are displayed.

The following instruction assigns a string to a variable named Filt . This string can then be used as a FileFilter argument for the GetOpenFilename method. In this case, the dialog box will allow the user to select from four different file types (plus an All Files option). Notice that I used VBA's line continuation sequence to set up the Filt variable; doing so makes it much easier to work with this rather complicated argument.

 Filt = "Text Files (*.txt),*.txt," & _        "Lotus Files (*.prn),*.prn," & _        "Comma Separated Files (*.csv),*.csv," & _        "ASCII Files (*.asc),*.asc," & _        "All Files (*.*),*.*" 

The FilterIndex argument specifies which FileFilter is the default, and the title argument is text that is displayed in the title bar. If the MultiSelect argument is True , the user can select multiple files, all of which are returned in an array.

The following example prompts the user for a filename. It defines five file filters.

 Sub GetImportFileName()     Dim Filt As String     Dim FilterIndex As Integer     Dim Title As String     Dim FileName As Variant '   Set up list of file filters     Filt = "Text Files (*.txt),*.txt," & _            "Lotus Files (*.prn),*.prn," & _            "Comma Separated Files (*.csv),*.csv," & _            "ASCII Files (*.asc),*.asc," & _            "All Files (*.*),*.*" '   Display *.* by default     FilterIndex = 5 '   Set the dialog box caption     Title = "Select a File to Import" '   Get the file name     FileName = Application.GetOpenFilename _         (FileFilter:=Filt, _         FilterIndex:=FilterIndex, _         Title:=Title) '   Exit if dialog box canceled     If FileName = False Then         MsgBox "No file was selected."         Exit Sub     End If '   Display full path and name of the file     MsgBox "You selected " & FileName End Sub 

Figure 12-8 shows the dialog box that appears when this procedure is executed and the user selects the Comma Separated Files filter.

Figure 12-8: The GetOpenFilename method displays a dialog box used to specify a file.

The following example is similar to the previous example. The difference is that the user can press Ctrl or Shift and select multiple files when the dialog box is displayed. Notice that I check for the Cancel button click by determining whether FileName is an array. If the user doesn't click Cancel, the result is an array that consists of at least one element. In this example, a list of the selected files is displayed in a message box.

 Sub GetImportFileName2()     Dim Filt As String     Dim FilterIndex As Integer     Dim FileName As Variant     Dim Title As String     Dim i As Integer     Dim Msg As String '   Set up list of file filters     Filt = "Text Files (*.txt),*.txt," & _            "Lotus Files (*.prn),*.prn," & _            "Comma Separated Files (*.csv),*.csv," & _            "ASCII Files (*.asc),*.asc," & _            "All Files (*.*),*.*" '   Display *.* by default     FilterIndex = 5 '   Set the dialog box caption     Title = "Select a File to Import" '   Get the file name     FileName = Application.GetOpenFilename _         (FileFilter:=Filt, _         FilterIndex:=FilterIndex, _         Title:=Title, _         MultiSelect:=True) '   Exit if dialog box canceled     If Not IsArray(FileName) Then         MsgBox "No file was selected."         Exit Sub End If '   Display full path and name of the files     For i = LBound(FileName) To UBound(FileName)         Msg = Msg & FileName(i) & vbCrLf     Next i     MsgBox "You selected:" & vbCrLf & Msg End Sub 

The FileName variable is defined as a variant (not a string, as in the previous examples). This is done because FileName can potentially hold an array rather than a single filename.