Table Dialog Types Used with the File Dialog Object

Dialog Type VBA Constant (FileDialogType)

Open msoFileDialogOpen

Save msoFileDialogSaveAs

File Picker msoFileDialogFilePicker

Folder Picker msoFileDialogFolderPicker

The Execute() method allows the user to carry out the specified action of the dialog box for files that are compatible with the Excel application (for example, files of type .xls, .xlt, .csv, and so on). For example, the Open dialog box allows the user to select one or more files to open when the Execute() method of the FileDialog object is invoked. When the following statement follows the Show() method for the Open dialog, the item(s) selected by the user are opened in Excel.


Be careful to set the properties of the FileDialog object appropriately for the desired action. For example, you cannot set the FilterIndex property of the FileDialog object when showing the Folder Picker dialog box because this dialog box shows only folders and does not allow file extension filters.

The FileDialogFilters and FileDialogSelectedItems Collection Objects

The FileDialog object has two subordinate collection objects—the FileDialogFilters and the FileDialogSelectedItems collection objects. The FileDialogFilters collection object contains a collection of FileDialogFilter objects that represent the file extensions used to filter what files are displayed in the dialog box (used with the Open and Save As dialog boxes). Use the

Filters property of the FileDialog object to return the FileDialogFilters collection and the Item property of the FileDialogFilters collection object to return a FileDialogFilter object. The Description and Extensions properties of the FileDialogFilter object return the description (for example, All Files) and the file extension used to filter the displayed files (for example, *.*).

I wrote the CheckFileFilters() sub procedure to generate a list of all possible file filters and their descriptions, then output the lists via message boxes. The procedure simply loops through each FileDialogFilter object in the FileDialogFilters collection and concatenates their Description and Extensions properties to separate string variables. Add the following procedure to any code module then run the program to generate message boxes similar to those shown in Figures 7.12 and 7.13.

Public Sub CheckFileFilters()

Dim fileFilters As FileDialogFilters Dim fileFilter As FileDialogFilter Dim I As Integer Dim descrs As String Dim xtns As String

Set fileFilters = Application.FileDialog(msoFileDialogOpen).Filters

'Loop through collection and build strings of 'all extensions and descriptions.

For I = 1 To fileFilters.Count

Set fileFilter = fileFilters.Item(I)

descrs = descrs & fileFilter.Description & vbCrLf 'Add carriage return/line feed to strings.

xtns = xtns & fileFilter.Extensions & vbCrLf Next I

MsgBox descrs MsgBox xtns End Sub

The FileDialogSelectedItems collection object contains the paths (as strings) to the files or folders selected by the user. Use the SelectedItems property of the FileDialog object to return the FileDialogSelectedItems collection. The GetSelectedItem() sub procedure first shows the Open dialog then loops through all items selected by the user in order to build a string containing their file paths. The file paths are then output in a message box. Note that the Item property of the FileDialogSelectedItems object returns a string.

File filter descriptions for Excel.

Microsoft Excel [Xj

At Fifes

At Microsoft Office Escet Piles Microsaft Office Excd Fibs At Web Pages XPL PJe; Text Files At Dato Soirees Access Databases Query Riss Lotus 1-2-3 Fites Quattro Pro/DOS FJes Microsoft Werts 2.0 Fies dBase Ries

Microsoft Excel 4.0 Macros

Microsoft Excel 4.0 Chart*

Microsoft Excel 4.0 Workbook







Data Interchange Format 5a(kup Files

File filer extensions for Excel.

Public Sub GetSelectedItem()

Dim selltems As FileDialogSelectedltems Dim I As Integer Dim paths As String

'Build a list of file paths to all files selected by user from Open dialog.


Set selltems = Application.FileDialog(msoFileDialogOpen).SelectedItems For I = 1 To selltems.Count paths = paths & selltems.Item(I) & vbCrLf Next I

MsgBox paths End Sub

You can use the Add() method of the FileDialogFilters collection object to create your own list of filters. The LoadImage() sub procedure shows the File Picker dialog box after clearing the FileDialogFilters collection and adding two new filters (*.*, and *.bmp). The Add() method requires a description and extension. An optional Position argument indicates the position of the added filter in the list.

The Show() method is called to display of the Open dialog after its properties are set. The Show() method of the FileDialog object returns -1 if the user presses the action button (Open in this example) and 0 if the action is cancelled. The FilterIndex property sets which filter is selected when the dialog is shown—essentially creating a default file filter. With the AllowMultiSelect property of the FileDialog object set to false, the user can only select one file. The path to this file is returned by the SelectedItems property of the FileDialog object which is used to load the selected image into an Image control named imgTest. You can test this procedure by adding it to the code module of a Worksheet object. Be sure to place an Image control on the worksheet and set its Name property before running the program.

Public Sub LoadImage()

Dim fileDiag As FileDialog Dim imagePath As String

Set fileDiag = Application.FileDialog(msoFileDialogFilePicker) With fileDiag

.AllowMultiSelect = False .Filters.Clear

.Filters.Add Description:="All files", Extensions:="*.*" .Filters.Add Description:="Image", Extensions:="*.bmp", Position:=1 .FilterIndex = 1 .InitialFileName = "" .Title = "Select BMP file"

If .Show = -1 Then 'User pressed action button imagePath = .SelectedItems(1) imgTest.Picture = LoadPicture(imagePath) End If End With End Sub

The path to the file selected by the user is returned from the FileDialogSelectedltems collection and stored in the string variable imagePath. If the Execute() method of the FileDialog object is omitted in the program, your program will need this path. Do not use the Execute() method of the FileDialog object when selecting files that are not compatible with Excel— doing so will either result in a runtime error or open a workbook containing incomprehensible data.

If the AllowMultiSelect property of the FileDialog object is true, the FileDialogSelectedltems collection will hold more than one file path. The ShowFileDialog() sub procedure loads the Open dialog box and allows the user to select multiple files. If the user clicks the Open button then the Execute() method attempts to open all selected files.

Public Sub ShowFileDialog() Dim fileDiag As FileDialog Const EXCELFILES = 2

'Configure and show the open dialog. 'Open all files selected by the user.

Set fileDiag = Application.FileDialog(msoFileDialogOpen) With fileDiag 'Configure dialog box .AllowMultiSelect = True .FilterIndex = EXCELFILES .Title = "Select Excel File(s)" .InitialFileName = "" If .Show = -1 Then 'User clicked Open

.Execute 'Open selected files End If End With End Sub

The dialog box resulting from the ShowFileDialog() sub procedure is shown in Figure 7.14. The FileSystem Object

The FileSystem object is a collection of methods that you can use to set and obtain information about files, directories, and drives. You can find the members of the FileSystem object listed in the Object Browser and in Table 7.2. You can use them as though they were just another group of VBA built-in functions. That is, you do not need to qualify the object when using these methods in your program.

Title property

AllowMultiSelect property

The Open dialog box of the

FileDialog object.

Title property

AllowMultiSelect property

The Open dialog box of the

FileDialog object.

InitialFileName property FilterIndex property

The Open Statement

The Open statement is used to read or write data to a file. Table 7.3 summarizes the type of access, and modes or functions available for reading and writing data to a file with VBA.

There is also a Binary access type for reading and writing to any byte position in a file as might be done with an image; however, this technique is beyond the scope of this book.

The Open statement requires several arguments, including a string that designates the path to a specified file. If the file does not exist, then one will be created. The Open statement also requires an access mode (Append, Binary, Input, Output, or Random) and a file number. Optional parameters include an access parameter (Read, Write, or Read Write), lock (used to restrict operations on the file from other programs), and record length (specifies the length of the buffer or record).

Open "C:\Data\Test.txt" For Input As #1

The preceding line opens a file named Test.txt found at the designated path for input, and assigns the file to the file number 1. If the file is not found, then one will be created at the designated location with the name Test.txt.

You can open multiple files in your VBA programs, but they must be assigned unique file numbers.

Was this article helpful?

0 0
Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment