Excel VBA Programming For Dummies
Book image
Explore Book Buy On Amazon
If your VBA procedure needs to ask the user for a filename, you could use the InputBox function and let the Excel user do some typing. An input box usually isn’t the best tool for this job, however, because most users find it difficult to remember paths, backslashes, filenames, and file extensions. In other words, it’s far too easy to screw up when typing a filename.

For a better solution to this problem, use the GetOpenFilename method of the Application object, which ensures that your code gets its hands on a valid filename, including its complete path. The GetOpenFilename method displays the familiar Open dialog box (a dead ringer for the dialog box Excel displays when you choose File  →  Open  →  Browse).

The GetOpenFilename method doesn’t actually open the specified file. This method simply returns the user-selected filename as a string. Then you can write code to do whatever you want with the filename.

The syntax for the GetOpenFilename method

The official syntax of the GetOpenFilename method is as follows:
object.GetOpenFilename ([fileFilter], [filterIndex],
  [title],[buttonText], [multiSelect])
GetOpenFilename method takes the optional arguments below.
Argument What It Does
FileFilter Determines the types of files that appear in the dialog box (for example, *.TXT). You can specify several filters for the user to choose from.
FilterIndex Determines which of the file filters the dialog box displays by default.
Title Specifies the caption for the dialog box’s title bar.
ButtonText Ignored (used only for the Macintosh version of Excel).
MultiSelect If True, the user can select multiple files.

A GetOpenFilename example

The fileFilter argument determines what appears in the dialog box’s Files of Type drop-down list. This argument consists of pairs of file filter strings followed by the wildcard file filter specification, with commas separating each part and pair. If omitted, this argument defaults to the following:
All Files (*.*), *.*
Notice that this string consists of two parts, separated by a comma:
All Files (*.*)
and
*.*
The first part of this string is the text displayed in the Files of Type drop-down list. The second part determines which files the dialog box displays. For example, *.* means all files.

The code in the following example opens a dialog box that asks the user for a filename. The procedure defines five file filters. Notice that the VBA line-continuation sequence is used to set up the Filter variable; doing so helps simplify this rather complicated argument.

Sub GetImportFileName ()
  Dim Finfo As String
  Dim FilterIndex As Long
  Dim Title As String
  Dim FileName As Variant
‘  Set up list of file filters
  FInfo = "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 filename
  FileName = Application.GetOpenFilename (FInfo, _
    FilterIndex, Title)
‘  Handle return info from dialog box
  If FileName = False Then
    MsgBox "No file was selected."
  Else
    MsgBox "You selected " & FileName
  End If
End Sub
Here is the dialog box Excel displays when you execute this procedure. The appearance may vary, depending on the version of Windows you use and the display options you’ve set.

The GetOpen Filename method displays a customizable dialog box and returns the selected file’

The GetOpen Filename method displays a customizable dialog box and returns the selected file’s path and name. It does not open the file.
In a real application, you would do something more meaningful with the filename. For example, you might want to open it by using a statement such as this:
Workbooks.Open FileName

Notice that the FileName variable is declared as a Variant data type. If the user clicks Cancel, that variable contains a Boolean value (False). Otherwise, FileName is a string. Therefore, using a Variant data type handles both possibilities.

About This Article

This article is from the book:

About the book author:

Dick Kusleika has been helping users get the most out of Microsoft Office products for more than 25 years through online forums, blogging, books, and conferences.

This article can be found in the category: