Selecting multiple files

If the MultiSelect argument for the GetOpenFilename method is True, the user can select multiple files in the dialog box. In this case, the GetOpenFilename method returns an array of filenames. Your code must loop through the array to identify each selected filename, as the following example demonstrates:



Dim FileNames As Variant

Dim Msg As String

Dim I As Integer

FileNames =


If IsArray(FileNames) Then


Display full path and name of the files

Msg = "You selected:" & vbCrLf

For I = LBound(FileNames) To UBound(FileNames)

Msg = Msg & FileNames(i) & vbCrLf

Next i

MsgBox Msg



Cancel button clicked

MsgBox "No files were selected."

End If



Select a File to Import


Look In

Q PRN files

¡V @ - J> 1 lift X Lj H " «

My Recent Documents

^JfileOl.prn g;f,le02.prn [É)file03.prri ^]file04.prn


è My Documents

My Computer

My Network Places

File name:

¡v: | Qpen

Files of type: | All Files

j V ^Jicd

Figure 15-7 shows the result of running this procedure. The message box displays the filenames that were selected.

Figure 15-7:

Select multiple filenames using the GetOpenFile name method.

Figure 15-7:

Select multiple filenames using the GetOpenFile name method.

Notice that I used a named argument for the GetOpenFilename method. I set the MultiSelect argument to True. The other arguments are omitted, so they take on their default values. Using named arguments eliminates the need to specify arguments that aren't used.

The FileNames variable is defined as a variant data type. I use the IsArray function to determine whether FileName contains an array. If so, the code uses the VBA UBound function to determine the array's upper bound and build a message that consists of each array element. If FileNames is not an array, the user clicked the Cancel button. Remember that the FileNames variable contains an array even if only one file is selected.

The GetSaveAsFilename Method

The Excel GetSaveAsFilename method works just like the GetOpenFilename method, but it displays the Excel Save As dialog box instead of its Open dialog box. The GetSaveAsFilename method gets a path and filename from the user but doesn't do anything with it.

The syntax for this method follows:

object.GetSaveAsFilename([initialFilename], [fileFilter], [filterlndex], [title], [buttonText])

The GetSaveAsFilename method takes Table 15-6's arguments, all of which are optional.

Table 15-6

GetSaveAsFilename Method Arguments


What It Does


Specifies a default filename that appears in the File Name



Determines the types of files Excel displays in the dialog

box (for example, *.TXT). You can specify several different

filters from which the user can choose.


Determines which of the file filters Excel displays by



Defines a caption for the dialog box's title bar.

0 0

Post a comment