Microsoft Office Tutorials and References
In Depth Information
Set FS = Application.FileSearch
.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
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.
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
.filename = fname
.LookIn = path
If .FoundFiles.Count = 1 Then
FileExists = True
FileExists = False
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.