Danny's VBA Scripts

Obtaining Filenames from a User

• Project Management Template Kit

You can use the GetOpenFilename method to gather existing filenames from a user. This method displays the common Open file dialog box your users are used to seeing in nearly all Windows applications. The GetOpenFilename method gathers filenames without opening the files. Here is its syntax:

Application.GetOpenFilename([FileFilter], [FilterIndex], [Title], _ [ButtonText], [MultiSelect])

FileFilter This parameter is an optional variant parameter in the format of a string that instructs the Open dialog box to display only files that match the filter supplied. The default is All Files (*.*),*.*.

FilterIndex This is an optional variant that you can use when multiple filters are supplied as a file filter. The FilterIndex should be a number that specifies which of the parameters is the default filter. By default, the first filter is used.

Title This is an optional variant that should be a string. Whatever you pass as the Title will be displayed in the title bar of the dialog box. By default, the text Open is displayed.

ButtonText This optional parameter is applicable to Macintosh computers only.

MultiSelect This optional parameter specifies whether the user should be able to select multiple files or a single file only. By default, only a single file can be chosen.

Listing 5.5 demonstrates how you might go about obtaining the name of an existing Excel workbook from the end user. The first procedure in the listing, TestGetFile, demonstrates how to use the second procedure, a function named GetExcelFile, and check the result before using it.

You could use the GetOpenFilename without all of this supporting code. However, you'll find that most of the time you need to perform all of these actions together, and that nine times out of ten, you'll want to filter the file list for Excel files only. Wrapping the GetOpenFilename method in a function simplifies your work because it means you only need to specify one parameter—the title of the dialog box. Granted, you really don't need to specify any parameters to GetOpenFilename, as they are all optional. However, using GetOpenFilename isn't nearly as user friendly if you don't specify any parameters, and it is needlessly repetitive to respecify the parameters every time you want to get an open filename. Thus, wrapping GetOpenFilename inside a procedure is a good idea.

Listing 5.5: Obtaining a Single Workbook from the User

Sub TestGetFi1e()

Dim nIndex As Integer Dim sFile As String

' Get a batch of Excel files sFile = GetExce1Fi1e("Testing GetExcelFile Function")

' Make sure dialog wasn't cancelled - in which case ' sFile would equal False. If sFile = "False" Then

Debug.Print "No file selected." Exit Sub End If

' OK - we have a valid file Debug.Print sFile End Sub

' Presents user with a GetOpenFileName dialog which allows ' single file selection. ' Returns a single of filename. Function GetExce1Fi1e(sTit1e As String) As String

Dim sFilter As String Dim bMultiSelect As Boolean sFilter = "Workbooks (*.x1s), *.x1s" bMultiSelect = False

GetExcelFile = App1ication.GetOpenFi1ename(Fi1eFi1ter:=sFi1ter, Tit1e:=sTit1e, Mu1tiSe1ect:=bMu1tiSe1ect) End Function

As you can see, the GetExcelFile function is pretty straightforward. It only uses one parameter to specify the title displayed by the Open file dialog box. You've hard coded the filter and clearly specified that you don't want to enable multiple file selection. By hard coiei I mean that you've assigned a literal value to a variable—sFilter in this case.

In the TestGetFile procedure, you can see that after you use the GetExcelFile function, you need to check to make sure the dialog box was not dismissed without choosing a file. For example, if you press Escape, click Cancel, or close the dialog box by clicking the X in the upper-right corner, GetExcelFile returns the value "False", which in turn is the value returned by the GetOpenFilename method (see Figure 5.2). If you find that GetExcelFile returns false, print a message to the Immediate window and exit the subroutine.

Figure 5.2

Your users will feel right at home selecting files when you use the GetOpenFilename method. This is the dialog box that is displayed by Listing 5.6.

What if you want the user to select one or more files? You may think that one simple way to enable this is to use another parameter in the GetExcelFile function, which you could then pass to the MultiSelect parameter of the GetOpenFilename method. However, a weird thing happens when you set MultiSelect to true. Instead of receiving a variant with a subtype of String, you receive a variant array. This creates subtle differences in how you need to check the value you receive. One way to handle this is to have a separate function that you use when you want to enable multiple file selection. Check out Listing 5.6 for an example.

Listing 5.6: Obtaining a Batch of Workbooks from the User

Sub TestGetFi1es()

Dim nIndex As Integer Dim vFiles As Variant

' Get a batch of Excel files vFiles = GetExce1Fi1es("Testing GetExcelFiles Function")

Testing GetExcelFiles Function				mm
Look in:		| Ö wf float	v <5= - JSJ 1 X ZJ El T Took -
	d	öapril tv [i^mavtv
	My Recent	L ¡ICbeckTrak Loader.xte
	Documents	TV Cccwerter.xls
	Desktop
	j My Documents
My Computer tJ *
	My Network Places	File name:	■1 1	Open
		Files of type: [workbooks C.xls)	za l_	Cancel

Make sure dialog wasn't cancelled

- in which case

'vntFiles would equal False and therefore not an array. If Not IsArray(vFiles) Then

Debug.Print "No files selected." Exit Sub End If

' OK - loop through the filenames For nIndex = 1 To UBound(vFiles)

Debug.Print vFiles(nIndex) Next nIndex End Sub

' Presents user with a GetOpenFileName dialog that allows ' multiple file selection. ' Returns an array of filenames.

Function GetExce1Fi1es(sTit1e As String) As Variant Dim sFilter As String Dim bMultiSelect As Boolean sFilter = "Workbooks (*.x1s), *.x1s" bMultiSelect = True

GetExcelFiles = App1ication.GetOpenFi1ename(Fi1eFi1ter:=sFi1ter, Tit1e:=sTit1e, Mu1tiSe1ect:=bMu1tiSe1ect) End Function

The first thing to notice other than the slight function name change from GetExcelFile (Listing 5.5) to GetExcelFiles (Listing 5.6) is that the return type of GetExcelFiles is a variant rather than a string. This is because GetOpenFilename will return a variant array when you enable multiple file selection. Also, note that you have set the bMultiSelect variable to true. Other than those minor modifications, this is largely the same as GetExcelFile. Figure 5.3 demonstrates the dialog box displayed by Listing 5.6.

Figure 5.3

Enabling multiple file selection allows you to select multiple files by holding down either the SHIFT key or the CTRL key.

Testing GetExcelFiles Function				mm
Look ¡n:		Ö wf float	* «- 1 Q X zj El t Tools -
	&	Ij^Japril tv i^ï may tv
	My Recent Documents
	Desktop
	j My Documents
	My Computer
	My Network Places	File name:	■1 1	Open
		Files of type: worfeboohsC.xls)	za l_	Cancel

Now for the differences between TestGetFile (Listing 5.5) and TestGetFiles (Listing 5.6). The first difference is that in Listing 5.6, instead of testing the result for the value "False"(as you did in Listing 5.5), you're testing to see whether or not the function returns an array. You can't check for the value "False", because if you use a statement like "If vFiles = False Then" and vFiles is an array, a Type Mismatch run-time error will occur. The second difference is that once you've assured yourself that you got an array (meaning one or more files were selected), you need to loop through the array to print out the full filename of each file selected.

The For.. .Next statement that loops through the files selected may appear erroneous considering the material concerning arrays that was presented in Chapter 3. Remember that by default, arrays in VBA are zero-based. The For.. .Next loop in Listing 5.6 starts at 1 rather than 0. What gives? This is one of those quirky programming things. Just when you think you get something, you stumble across something else who ignores convention and casts doubt on your knowledge. Don't worry, the sky isn't falling. The Microsoft programmer that implemented the GetOpenFilename function must have thought that it would be better to return the array as a one-based array instead of zero.

+1 0

Average user rating: 5 stars out of 1 votes