Page 324 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

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.

Chapter 13
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
output.txt file.

Sub FilterFile()
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
End If
Loop
Close
End Sub
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.

298
Part 4: Advanced VBA