File Open and File Save As Dialog Boxes

A new and quite powerful object in Office XP is FileDialog. This object allows you to display the File Open and File Save As dialog boxes from your VBA procedures. Because the FileDialog object is a part of the Microsoft Office 10.0 Object Library, it is available to all Office XP Applications. Programmers in all previous versions of Excel have used two special methods for displaying File Open and File Save As dialog boxes. These methods (GetOpenFilename and GetSaveAsFilename) are explained later in this section.

To display the File Open dialog box from your VBA procedure using the new FileDialog object, enter the following statement:

Application.FileDialog(msoFileDialogOpen).Show

To display the File Save As dialog box, use the following statement:

Application.FileDialog(msoFileDialogSaveAs).Show

For now, take a quick look at the File Open and File Save As dialog boxes by typing the above statements in the Immediate window.

In addition to File Open and File Save As dialog boxes, the FileDialog object is capable of displaying a Browse dialog box with a list of files and folders (Figure 10-4) or a list of folders (Figure 10-5):

' browse the list of files and folders Application.FileDialog(msoFileDialogFilePicker).Show

' browse the list of files and folders Application.FileDialog(msoFileDialogFilePicker).Show

Figure 10-4:

The File Picker dialog box lets users select one or more files. This dialog box displays a list of files and folders and shows Browse in the title bar.

' browse the list of folders

Application.FileDialog(msoFileDialogFolderPicker).Show

Figure 10-4:

The File Picker dialog box lets users select one or more files. This dialog box displays a list of files and folders and shows Browse in the title bar.

' browse the list of folders

Application.FileDialog(msoFileDialogFolderPicker).Show

Figure 10-5:

The Folder Picker dialog box lets users select a path. This dialog box displays a list of directories and shows Browse in the title bar.

The constants that the FileDialog object uses are listed in the table below. The "mso" prefix denotes that the constant is a part of the Microsoft Office

Object Model.

msoFileDialog Constants Value msoFileDialogOpen 1

msoFileDialogSaveAs 2

msoFileDialogFilePicker 3

msoFileDialogFolderPicker 4

To control the types of files that are displayed, use the FileDialog Filters property. If you open the Files of type drop-down list box at the bottom of the File Open dialog box, you will see quite a selection of file filters to choose from. While there are 24 preset file filters, you can also add your own filters to this list.

Enter the following in the Immediate window to find out the default number of filters:

set f = Application.FileDialog(msoFileDialogOpen).Filters ?f.count

Filters are stored in the FileDialogFilters collection for the FileDialog object. Let's create a simple procedure that returns the list of default file filters to an Excel worksheet:

1. Insert a new module into the current VBA project and rename it DialogBoxes.

2. In the DialogBoxes Code window, enter the ListFilters procedure, as shown below:

Sub ListFilters()

Dim fdfs As FileDialogFilters Dim filt As FileDialogFilter

Browse

sim]

Look in:

□ ExceLEN_2002

d ! *B

a H, " Toc{5~

[¡£1 History

ChapterFilesQId _ll ChsptersNew

1

My Documents

Desktop

My Network Places

Folder name*

Cancel

Set fdfs = Application.FileDialog(msoFileDialogOpen).Filters Sheets(3).Cells(1, 1).Select Selection.Formula = "List of Default Filters" With fdfs c = .Count

For Each filt In fdfs

Selection.Offset(1, 0).Formula = filt.Description &_

": " & filt.Extensions Selection.Offset(1, 0).Select

Next

MsgBox c & " filters were written to Sheet3." End With End Sub

This procedure declares two object variables. The fdfs variable returns a reference to the FileDialogFilters collection of the FileDialog object, and the filt object variable stores a reference to the FileDialogFilter object. The Count property of the FileDialogFilters collection returns the total number of filters. Next, the procedure iterates through the collection and retrieves the description and extension of each defined filter.

Using the Add method of the FileDialogFilters collection, you can easily add your own filter to the default filters. The following modified ListFilters2 procedure demonstrates how to add a filter to filter out temporary files (*.tmp). The last statement in this procedure will open the File Open dialog box so that you can check for yourself that the custom filter Temporary files (*.tmp) has indeed been added to the Files of type drop-down list.

Sub ListFilters2()

Dim fdfs As FileDialogFilters Dim filt As FileDialogFilter Dim c As Integer

Set fdfs = Application.FileDialog(msoFileDialogOpen).Filters Sheets(3).Cells(1, 1).Select Selection.Formula = "List of Default Filters" With fdfs c = .Count

For Each filt In fdfs

Selection.Offset(1, 0).Formula = filt.Description &_

": " & filt.Extensions Selection.Offset(1, 0).Select

Next

MsgBox c & " filters were written to Sheet3." .Add "Temporary Files", "*.tmp", 1 c = .Count

MsgBox "There are now " & c & " filters." & vbCrLf _

& "Check for yourself." Application.FileDialog(msoFileDialogOpen).Show End With End Sub

You can remove all the preset filters using the Clear method of the FileDialogFilters collection. Modify the above procedure to clear the built-in filters prior to adding the custom filter Temporary files (*.tmp).

When you select a file in the Open File dialog box, the selected file name and path is placed in the FileDialogSelectedItems collection. Use the SelectedItems property to return the FileDialogSelectedItems collection. By setting the AllowMultiSelect property of the FileDialog object to True, a user can select one or more files by holding down the Shift or Control keys while clicking filenames.

The following procedure demonstrates how to use the above-mentioned properties. The procedure opens a new workbook and inserts a list box control. The user is allowed to select more than one file. The selected files are then loaded into the list box control, and the first filename is highlighted.

Sub ListSelectedFiles() Dim fd As FileDialog Dim myFile As Variant Dim lbox As Object

Set fd = Application.FileDialog(msoFileDialogOpen) With fd

.AllowMultiSelect = True If .Show Then Workbooks.Add

Set lbox = Worksheets(1).Shapes. _ AddFormControl(xlListBox, _ Left:=20, Top:=60, Height:=40, Width:=300) lbox.ControlFormat.MultiSelect = xlNone For Each myFile In .SelectedItems lbox.ControlFormat.AddItem myFile

Next

"You've selected the following " & _ lbox.ControlFormat.ListCount & " files:" lbox.ControlFormat.ListIndex = 1 End If End With End Sub

HBoofc

E

xj

A

1

t

D

E

F

G'

1

I I

[

2

3

4

YouVe selected the following 4 files:

5

6

■C:\JKJ C:\JK f GipK [

iOQKS JiLL\Excel _EN_2002\ChaptersNew\Chapter_l 0\Fig 10-02. ipg jooks ALLtExcel EN 2002lChaMersNeiMlChaDter JOlFio 10-03 iH

_J

7

r

3

500K5_ALUExcel_EN_2002\Chapter5NeiV;Chacter_!

0\Flgl0-01,jpy

9

10

-

\< <

► H \Sheetl_^ 5heet2 X 5heet3 /

M

-

J

►II

User-selected files are loaded into a list box control placed in a worksheet by the ListSelect-edFiles procedure (shown above).

Notice that the Show method does not open the files selected by the user. It merely displays the File Open dialog box. When the user clicks the Open button, the names of the files are retrieved from the SelectedItems collection via the SelectedItems property. If you'd like to immediately carry out the file open operation when the user clicks the Open button, you should use the Execute method of the FileDialog object.

The following procedure demonstrates how to open the user-selected files right away.

Sub OpenRightAway()

Dim fd As FileDialog Dim myFile As Variant

Set fd = Application.FileDialog(msoFileDialogOpen) With fd

.AllowMultiSelect = True If .Show Then

For Each myFile In .SelectedItems .Execute

Next End If End With End Sub

0 -1

Responses

  • Tanja
    How to add file dialog filters vba xp?
    8 years ago

Post a comment