Working with Files and Folders in Office 2003 Editions

Frank Rice
Microsoft Corporation

July 2004

Applies to:
    Microsoft Office 2003 Editions

Summary: Frank Rice provides tips on how to use the Office object model to enhance your custom solutions. The FileSearch and FileDialog objects provide a convenient and consistent way for users to manipulate files and folders. Learn how these features can work in your own solutions. (6 printed pages)

Contents

Introduction
The FileSearch Object
The FileDialog Object
Using the FileSearch Object
Using the FileDialog Object
Conclusion

Introduction

Each application in Microsoft Office 2003 Editions has an associated object model designed to provide access to the features and functions of the program. One of these object models, or type libraries, is the Office object model, which contains, among other things, two objects that allow you to search for files and to open and save files; the FileSearch object and the FileDialog object, respectively.

Providing a consistent interface to features for the users of your application is an important attribute of your solution. The FileSearch object and the FileDialog object provide just such an interface for searching and, opening and saving files, respectively. This increases the professionalism of your application and reduces the amount of training needed to get your users up-to-speed.

The FileSearch Object

The FileSearch object allows you to search for files with a wide range of search criteria such as file type, file size, file location, and date of last modification. FileSearch places the names of the files it finds in the FoundFiles collection.

You can use FileSearch instead of the older Dir function for a wide range of file-related operations.

Note   While useful, the Dir function has limited functionality. It accepts a path as an argument, and returns the name of any file at that location. By default, Dir returns the first file from the path you specify. In order to return the next file from the same location, you have to call it without specifying a path. For more information, see Help in the Microsoft Visual Basic Editor.

FileSearch is also useful for the maintenance of files. For example, you can retrieve data from a number of related files. Or you might want to locate files of a certain age and move them to a central directory or even delete them. You might want to find all of the files with a certain extension, consolidate then, and then store the information in them to a summary file.

The FileDialog Object

The FileDialog object provides File dialog box functionality similar to the functionality of the standard Open and Save dialog boxes, as well as a subdirectory browser, found in Office programs. With these dialog boxes, users of your solutions can easily specify the files and folders that your solution should use. One advantage of the FileDialog object from earlier objects is that it allows you to display just a list of folders rather than files and folders. It is also available to all Office client applications.

Using the FileSearch Object

The FileSearch object exposes an interface with all of the functionality of the Office File Open dialog box. The objects, methods, and properties of the FileSearch object allow you to search for files, or collections of files based on the criteria you supply.

The following code demonstrates a simple file-search routine that displays a message box containing the names of all files in the "c:\" directory that match the file specifications provided in the strFileSpec argument. The strFileSpec argument can contain one or more file specifications in a semicolon-delimited list. For example, the following strFileSpec argument returns all files in the "c:\" that contain these extensions: "*.exe;*.xml;*.xsd".

Figure 1. The results from a file search

Because the FileSearch object is shared among all Office programs applications, this code works without modification from any Office program:

Function FindFile(strFileSpec As String)
    Dim fsoFileSearch As FileSearch
    Dim varFile As Variant
    Dim strFileList As String

    On Error GoTo Problem

    ' If the input in valid, then process the file search.
    If Len(strFileSpec) >= 3 And InStr(strFileSpec, "*.") > 0 Then
        Set fsoFileSearch = Application.FileSearch
        With fsoFileSearch
            .NewSearch
            .LookIn = "c:\"
            .FileName = strFileSpec
            .SearchSubFolders = False
            If .Execute() > 0 Then
                For Each varFile In .FoundFiles
                    strFileList = strFileList & varFile & vbCrLf
                Next varFile
            End If
        End With
        MsgBox strFileList
    Else
        MsgBox strFileSpec & " is not a valid file specification."
        Exit Function
    End If
    Exit Function
Problem:
    Msgbox Err.Number & ": " & Err.Description
    
End Function

Briefly looking at the procedure, the FileDialog property of the Application object returns a reference to the Office FileDialogs object. This object uses the following msoFileDialogType constants to specify the type of dialog:

Table 1. msoFileDialogType constants

The NewSearch method is used to clear any previous search criteria. All property values are retained after each search is run, and by using the NewSearch method you can selectively set properties for the next file search without manually resetting previous property values.

The Execute method of the FileSearch object carries out the search and adds the object representing each file to the FoundFiles collection. Execute also returns a value indicating the number of files found.

Using the FileDialog Object

The FileDialog object allows you to provide common interfaces to your users that simulate the File Open and File Save dialog boxes. This allows you to provide a consistent way for users to manipulate files and folders.

The following example creates and displays a File Picker dialog box, prompts the user for all .jpg files and loads the image into an Image control.

Figure 2. The FileDialog File Open dialog box (Click to view larger image)

This procedure assumes that you already have an Image control defined:

Sub CommandButton1_Click()
    Dim FD As FileDialog
    Dim FFs As FileDialogFilters
    Dim stFileName As String

    On Error GoTo Problem

    'Set up the Open dialog.
    Set FD = Application.FileDialog(msoFileDialogOpen)
    With FD
        'Clear default filters and create the picture filter.
        Set FFs = .Filters
        With FFs
            .Clear
            .Add "Images", "*.jpg"
        End With
    'Allow only one file selection.
    .AllowMultiSelect = False

    'Show the dialog. Exit if Cancel is pressed.
    If .Show = False Then Exit Sub

    'Load selected file into an Image control. Image control
    'must exist on a worksheet and this code reside behind the
    'worksheet.
    Image1.Picture = LoadPicture(.SelectedItems(1))
  End With
    Exit Sub

Problem:
    MsgBox "That was not a valid picture."
End Sub

In this procedure, the Filters property of the FileDialog object returns a reference to the FileDialogFilters collection for the FileDialog. Filters control the types of files to display. The Clear method removes the preset filters and you can add your own filter to show only files meeting your criteria as demonstrated with the Add method.

The Show method doesn't actually open the file but instead places the path and file name into a FileDialogSelectedItems collection. You can then display a file (or multiple files) or whatever operation you define in the procedure. In the example, 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.

Conclusion

The FileSearch object and the FileDialog object provide useful methods for writing file handling code. This increases the professionalism of your applications and reduces the amount of training needed to get your users up-to-speed. And because these objects are part of the Office object model, they have the benefit of being available to all Office Visual Basic for Applications client applications.

In this article, you saw how to use the FileSearch object to locate files with common characteristics so that you can process those files in subsequent code. You also saw how to use the FileDialog object to display the File Open and File Save dialog boxes as well as a directory browser.

Making use of these objects in your applications will provide a level of consistency and familiarity that will decrease the amount of time and effort it takes for your users to become productive in your solutions.

© Microsoft Corporation. All rights reserved.