Page 308 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

You can use the FileSearch object instead of the VBA Dir function for a range of file operations.
FileSearch is useful for maintenance of files. For example, you can locate files of a certain age
and delete them or move them to an archive directory. The FileSearch object is also useful
when you need to retrieve data from a number of related files. For example, you can find all
the Excel files in a certain directory that pertain to a new marketing initiative for The Garden
Chapter 13
Supply Company, before you consolidate the information into a summary file.
The FileDialog object was introduced in Office XP and enhanced with Office 2003. You are
able to display the File Open and File Save As dialog boxes as well as a subdirectory browser.
FileDialog is a more powerful version of the GetOpenFileName and GetSaveAsFileName
methods of the Excel Application object, which are available in previous versions of Excel, but
have not been available to other Office applications. FileDialog, being an Office object, is
available to all Office applications.

Returning All Files

The FileSearch property is used to located file names based on your search criteria. It places
the file names returned from the search in the FoundFiles collection. This object gives your
code the functionality of the File Search feature available in the Excel application. For exam­
ple, you can search for all Excel files by the file extension or search for files containing specific
text. Table 13-1 lists some of the properties and methods used in the FileSearch object.
Table 13-1. List of Properties and Methods of the FileSearch Object

Property or Method Result
FileName Searches for the name of the file specified. Wildcards can be
used in the search criteria.
FoundFiles Returns an object that contains the names of the files found.
LookIn Specifies the directory to be searched.
SearchSubFolders Sets the search to look in subfolders if True, or to ignore
subfolders if set to False.
Execute Initiates the search.
NewSearch Clears previous results in the FileSearch object from
previous searches.

Consider the following example, in which the object variable FS is declared as part of the
Office.FileSearch object. The Office prefix is not required, but this prefix makes it clear that
the FileSearch object is an object in the Office library. In the code, the FileSearch property of
the Excel Application object returns a reference to the FileSearch object and assigns it to the
FS variable. Values are then assigned to a number of FileSearch properties. The LookIn
property tells FileSearch which subdirectory to search. NewSearch is a method that clears all
the FileSearch properties except LookIn. Because these properties are retained while Excel is
open, it’s a good idea to execute NewSearch each time you use the FileSearch method. The
SearchSubFolders property controls whether you look in subdirectories below the LookIn
subdirectory.

282
Part 4: Advanced VBA