Using VBA And The File System Object To Search A Text File

The use of text files is often overlooked when it comes to developing an Excel application. If you've got additional data you need to reference, you might not want to keep the information in the current workbook and directly accessing a file is efficient and easily coded.

This article will explain how to use the File System Object(FSO) to open a file and search its contents.

Using The FSO

Before you can access any files, you need to add a reference to the Microsoft Scripting Runtime library. You can select the library from a list of references under the tools tab in the code window.

We'll use as an example a document which contains an organization phone list and the obective of the code is to find the extension number of "Jonas Franco".

Peter James,5956

Jonas Franco,4567

Melissa Ramirez,9897

To create a FSO requires just 2 lines of code:

Dim fso As Scripting.FileSystemObject

set fso = New Scripting.FileSystemObject

With access to the file system enabled, the code can now open the file.

Dim myFile

Dim filePath As String

filePath = ActiveWorkbook.path & "\files\code.txt"
Set myFile = fso.openTextFile(filePath)

Now the code can search and show the phone number of "Jonas Franco".

myName = "Jonas Franco"

Do Until myFile.AtEndOfStream

txt = myFile.ReadLine

If InStr(txt, myName) > 0 Then

msgbox split(txt,",")(1)

Exit Do
End If
Loop

The code reads each line until it finds the name and displays the phone number in a message box. At glance, it may not be obvious how the code moves from line to line; this is achieved with the ReadLine which moves to the next line each time it is encountered.

This solution might be implemented where the data you are accessing doesn't need to be incorporated into the Excel workbook, or is being constantly updated in a text file. As long as the data is structured in a consistent format, then the information can be extracted with minimum coding.

One opportunity for developing the code might be to use a series of text files as a code library. You could structure the text file so that it could be searched for key words or procedure names, without any knowledge of advanced programming like XML, XLS or Regular Expressions.

Summary

This article has demonstrated how to open a text file and search for a specific text string within the file. Although most problems in Excel are solved by importing data into a workbook, sometimes it's easier to work directly with a text file and this code snippet shows the versatility of VBA in a business environment.