Using the File Search object

The FileSearch object is a member of the Microsoft Office object library. This object essentially gives your VBA code all the functionality of the Windows Find File dialog box. For example, you can use this object to locate files that match a file specification (such as *.xls) and even search for files that contain specific text. You can use this object with Excel 97 and later versions.

Table 27-2 summarizes some of the key methods and properties of the FileSearch object. For a complete list and details, consult the online help.

Table 27-2 PROPERTIES AND METHODS OF THE FILESEARCH OBJECT

Property or Method

FileName

FoundFiles LookIn

SearchSubfolders

Execute

NewSearch

What It Does

The name of the file to be located. (Wildcard characters are acceptable.)

Returns an object that contains the names of the files found.

The directory to be searched.

True if subdirectories are to be searched.

Performs the search.

Resets the FileSearch object.

The remainder of this section consists of examples that demonstrate use of the

FileSearch object.

USING FILESEARCH TO DETERMINE WHETHER A FILE EXISTS

The following function takes two arguments (the path and the filename) and returns True if the file exists in the specified directory. After the Execute method is run, the Count property of the FoundFiles object will be 1 if the file is found.

Function Fi'leExists2(path, fname) As Boolean With Application.FileSearch .NewSearch .Filename = fname .LookIn = path .Execute

FileExists2 = .FoundFiles.Count = 1 End With End Function

As far as I can tell,you cannot use the FileSearch object to determine whether a path exists.

USING FILESEARCH TO DISPLAY A LIST OF FILES IN A DIRECTORY

The following procedure in Listing 27-1 displays (in the active worksheet) a list of files contained in a particular directory, along with the file size and date:

Listing 27-1: Using FileSearch to Display a List of Files in a Directory

Sub ListFiles2()

Directory = "c:\windows\desktop\" ' Insert headers r = 1

Cells(r, 1) = "FileName" Cells(r, 2) = "Size" Cells(r, 3) = "Date/Time" Range("A1:C1").Font.Bold = True r = r + 1

With Application.FileSearch .NewSearch .LookIn = Directory .Filename = "*.*" .SearchSubFolders = False .Execute

For i = 1 To .FoundFiles.Count Cells(r, 1) = .FoundFiles(i) Cells(r, 2) = FileLen(.FoundFiles(i)) Cells(r, 3) = FileDateTime(.FoundFiles(i)) r = r + 1 Next i End With End Sub

The FileSearch object essentially ignores all Windows shortcut files (files with a *.lnk extension).

The companion CD-ROM contains a more sophisticated version of this procedure that allows you to select a directory.

0 0

Post a comment