Microsoft Office XP FileDialog Object

Before Office XP we had to use API calls or additional ActiveX controls to provide users with file selection or file naming dialogs within our VBA applications. Since Office XP (Office Release 10) we have four new FileDialog objects available to us.

The outline of their use is documented in Microsoft Knowledge Base article 288543. This page has been produced to discuss in more detail the various options available and the ones that are actually useful.

We will start with the ‘FolderPicker’ and the ‘FilePicker’ options since these are the two that are new and no existing alternative.

‘ Declare a variable as a FileDialog Object

Dim fd As FileDialog

‘ Declare a variable to capture the selected item

Dim varSI as Variant

‘ Create a FileDialog object as a Folder Picker dialog box

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd

.Title = “Select folder…” ‘What ever text you want

.AllowMultiSelect = False ‘Does not make much sense to set to true on FolderPicker

.ButtonName = “Select” ‘What ever makes sense. Default is OK

.IntialFileName = “G:\mydir” ‘To guide your user part of the way to where you need them to be

‘ Show the dialog and jump out if user presses cancel

If .Show <> -1 Then GoTo EndDialog

varSI = .SelectedItems(1) ‘use first entry because there will be only one

End With

At the end of the above varSI contains the name of the directory selected without the final “\” so if we then want to use varSI as the “InitialFileName” for FilePicker dialog we have to add one. In real life I suspect that what one is likely to want to do is use the FolderPicker when you want to enable the user to select a number of files and use the FilePicker when you only want the user to select one file. In my example because I’m trying to demonstrate as much as possible in one example I am assuming that you want the user to locate the Folder and then select a number, but possibly not all, files from the folder selected using the FolderPicker.

‘ Add trailing \ to directory name

‘ varSI = varSI & “\”

‘ Declare an array for the file names

Dim varFN() as variant

‘ Create a FileDialog object as a Folder Picker dialog box

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

.Title = "Select the file"

.AllowMultiSelect = True ‘Allow user to select multiple files

.ButtonName = "Select"

.InitialFileName = varSI

‘ The next 4 lines serve as a long hand explanation of how to use .Filters and .FilterIndex since

‘ it is far from obvious using the vba help. This will limit the display to all *.xls files.

‘ NB Filter Index uses Option Base 1 by default so FilterIndex =2 is the appropriate index

.Filters.Add "Excel Template", "*.xlt", 1

.Filters.Add "Excel Files", "*.xls", 2

.Filters.Add "Excel Workbooks", "*.xlw", 3

.FilterIndex = 2

‘ Show the dialog and jump out if user presses cancel

If .Show <> -1 Then GoTo EndDialog

‘ Redim our array for holding file names.

ReDim varFN(.SelectedItems.Count)

‘ This could be done using a For each file In .SelectedItems depending on how you wanted to handle

‘ the list

For intFC = 1 To .SelectedItems.Count

varFN(intFC) = .SelectedItems(intFC)

Next

End With

EndDialog:

The remaining two ‘Open’ and ‘SaveAs’ work in a similar way but additionally have a ‘.Execute’ property. Clearly, options like ‘.AllowMultiSelect’ and ‘.SelectedItems’ whilst appropriate for the ‘Open’ will not be necessary for the ‘SaveAs’. In reality the only two properties that do anything real are the ‘.Show’ and ‘.Execute’, all the things like ‘.Title’ and ‘.ButtonName’ are the means by which you might guide your user.