Page 319 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Files

The Filters property of the FileDialog object returns a reference to the FileDialogFilters collec­
tion for the FileDialog. The filters control the types of files that are displayed. By default, there
are 24 preset filters that the user can select from the drop-down list at the bottom of the Chapter 13
File Open dialog box. The Clear method of the FileDialogFilters collection removes the preset
filters, and we add our own filter that shows only .jpg files.
The Show method of the FileDialog object displays the dialog box. When the user clicks the
Open button, the Show method returns a value of True. If the user clicks the Cancel button,
the Show method returns False and you’ll exit the procedure.
The Show method does not actually open the selected file, but instead places the file name
and path into the FileDialogSelectedItems collection. It’s possible to set this property to allow
users to select multiple files. By default, the name of the file is returned from the first item in
the FileDialogSelectedItems collection, which is referred to by the SelectedItems property of
the FileDialog object.
There are few differences between the four possible dialog types apart from the heading on
the title bar. The file picker and folder picker types show Browse in the title bar, and the others
show File Open and File Save As, as appropriate. All the dialogs show the folders and files
except the folder picker dialog, which shows only folders.
As you have seen, the Show method displays the FileDialog, and the items chosen are placed
in the FileDialogSelectedItems object without any attempt to open or save any files. The fol­
lowing example shows how you can use the Execute method with the File Open and Save As
dialogs to carry out the required Open or Save As operations immediately when the user
clicks the Open or Save button.

With Application.FileDialog(xlDialogOpen)
If .Show Then .Execute
End With
Consider the following example. The cmdShowProductImage_Click procedure has been mod­
ified to allow the user to select multiple files by holding down the Shift or Ctrl keys while
clicking on the file names. The file names are then loaded into the List box named lstFileList,
allowing the user to display the files by selecting the file name.
Private Sub cmdShowProductImage_Click()
Dim FD As FileDialog
Dim FFs As FileDialogFilters
Dim strFileName As String
Dim vaItem
Dim intCounter As Integer
On Error GoTo Problem

Set FD = Application.FileDialog(msoFileDialogOpen)
With FD
Set FFs = .Filters

293
Part 4: Advanced VBA