Page 317 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Files

Set FS = Application.FileSearch
Chapter 13
With FS
.NewSearch
.LookIn = “c:\"
.SearchSubFolders = True
.Filename = “*.xls"
.LastModified = msoLastModifiedAnyTime
iCount = .Execute
strMessage = Format(iCount, “0 “"Files Found""“)
For Each vaFileName In .FoundFiles
strMessage = strMessage & vbCr & vaFileName
Next vaFileName
MsgBox strMessage
End With
End Sub
The Search_SearchFolders collection sets the LookIn property of FileSearch to the C drive to
ensure that it doesn’t contain any directory references from previous FileSearch operations.

Inside Out

Determining if a File Exists Using FileSearch
There might be times when your procedure won’t run successfully without a particular file.
You can simply use a function to determine if that file exists. For example, the following
function was written to verify whether a file and its path are valid. The function will return
True if the file exists and False if it was not found.

Function FileExists(path, fname) As Boolean
With Application.FileSearch
.NewSearch
.filename = fname
.LookIn = path
.Execute
If .FoundFiles.Count = 1 Then
FileExists = True
Else
FileExists = False
End If
End With
End Function

Finding Files with the FileDialog Dialog Box

The FileDialog object allows you to display the Open and Save As dialog boxes using VBA
code. The GetOpenFileName and GetSaveAsFileName methods of the Application object
achieve similar results and can be used for backward compatibility. However, the FileDialog
object is available to all Office applications and is a familiar interface to the users.

291
Part 4: Advanced VBA