L qj Find A File

You can use VBA to create a procedure to find specific files on your computer. By creating this type of procedure, you can ensure that a specific file exists before attempting to reference it. This type of coding is useful for avoiding errrors because it verifies that a file exists, as well as the file's exact location.

In order to search for a file on your system, you use the FileSearch object. This object essentially opens the Excel Open dialog box and attempts to locate the file based upon specified methods and property values. The .Filename property indicates the name of the file for which you want to search. You can search for one, or a series of files using a wildcard character. You use an asterisk (*) to represent multiple characters or a question mark symbol (?) to specify a single character. For example, you can find all text files with the *.txt specification.

You stipulate the location where Excel starts the search with the .LookIn property. You can use the SearchSubFolders property to indicate whether you want Excel to look in the subfolders of the location specified by the .LookIn property.

If Excel locates your file, VBA returns a FoundFile object containing the matching filenames. You reference the individual filenames in the FoundFile object using an index value. VBA adds the filenames to the FoundFile object in the order that Excel locates them. You can determine the number of file names in the FoundFile object using the Count method. Using a For Next looping statement enables you to cycle through all of the matches that Excel finds using your search criteria. For example, you can write your code to open each file that matches the specified criteria.



□ Create a new subroutine.

□ Create a new subroutine.

-0 Type Dim Pathlnfo As String, replacing Pathlnfo with the file location variable.

'-0 Type With Application.FileSearch.

'—0 Type .FileName = "Book.xls", replacing Book.xls with the name of the file(s) to locate.

□ Type .LookIn = "C:\", replacing C:\ with the path to search.

■ If you do not want to search subfolders, you can specify a value of False for SearchSubFolders.

Was this article helpful?

0 0

Post a comment