Microsoft Office Tutorials and References
In Depth Information
Note The procedure is a starting point. It doesn’t take into account how to handle data
that might contain commas or a quote character. You’ll also notice that if a date is
imported, number signs appear around the date.
Searching a File for a Value
There will be times when you do not require the entire text file contents to be copied. You can
specify your search to determine which text you are looking for. When the text is found, you
can then determine which actions to take.
The following procedure uses two text files at the same time. The FilterFile procedure reads
the text from textfile.txt and copies only rows that contain a specific text string to a second
Open “c:\textfile.txt” For Input As #1
Open “c:\output.txt” For Output As #2
TextToFind = “January"
Do Until EOF(1)
Line Input #1, Data
If InStr(1, Data, TextToFind) Then
Print #2, Data
The FileSearch and FileDialog objects provide useful tools when programming VBA proce
dures. Because these objects are part of the Office object model, they have the advantage of
being available to all Office VBA applications.
The FileSearch object is used to locate files with common characteristics, such as file names or
similar locations, so that they can be processed in subsequent code. The FileDialog object is
used to display the File Open and File Save As dialog boxes to allow the user to browse the
folders. It provides a more powerful tool than the GetOpenFileName and GetSaveAsFileName
functions used in previous versions of Excel.
The file search capabilities reviewed in this chapter can enhance the projects that you work
with. Limit your searches using the criteria discussed, such as a specific file extension or a
value within the file. These search techniques can be incorporated in your future projects to
enhance your results. Once the desired files have been located, remember that you can read
the contents of the file and then write the contents into new files when appropriate.