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:

Sub

GetImportFileName2()

Dim FileNames As Variant

Dim Msg As String

Dim I As Integer

FileNames =

Application.GetOpenFilename(MultiSelect:=True)

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

Else

'

Cancel button clicked

MsgBox "No files were selected."

End If

End

Sub

Select a File to Import

\jM

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

Desktop

è 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

Argument

What It Does

initialFilename

Specifies a default filename that appears in the File Name

box.

fileFilter

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.

filterIndex

Determines which of the file filters Excel displays by

default.

title

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

0 0

Post a comment