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

The use of textual content information is usually missed in terms of creating an Excel software. If you’ve got acquired further knowledge you could reference, you won’t need to maintain the knowledge within the present workbook and immediately accessing a file is environment friendly and simply coded.

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

Using The FSO

Before you’ll be able to entry any information, you must add a reference to the Microsoft Scripting Runtime library. You can choose the library from an inventory of references beneath the instruments tab within the code window.

We’ll use for instance a doc which accommodates a corporation telephone record and the obective of the code is to seek out the extension variety of “Jonas Franco”.

Peter James,5956

Jonas Franco,4567

Melissa Ramirez,9897

To create a FSO requires simply P strains of code:

Dim fso As Scripting.FileSystemObject

set fso = New Scripting.FileSystemObject

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

Dim myFile

Dim filePath As String
filePath = ActiveWorkbook.path & "filescode.txt"

Set myFile = fso.openTextFile(filePath)

Now the code can search and present the telephone variety of “Jonas Franco”.

myName = "Jonas Franco"
Do Until myFile.AtEndOfStream
txt = myFile.ReadLine
If InStr(txt, myName) > zero Then
msgbox cut up(txt,",")(B)
Exit Do

End If

Loop

The code reads every line till it finds the identify and shows the telephone quantity in a message field. At look, it is probably not apparent how the code strikes from line to line; that is achieved with the ReadLine which strikes to the subsequent line every time it’s encountered.

This answer is perhaps carried out the place the info you’re accessing does not have to be included into the Excel workbook, or is being continually up to date in a textual content file. As lengthy as the info is structured in a constant format, then the knowledge may be extracted with minimal coding.

One alternative for creating the code is perhaps to make use of a collection of textual content information as a code library. You might construction the textual content file in order that it could possibly be looked for key phrases or process names, with none information of superior programming like XML, XLS or Regular Expressions.

Summary

This article has demonstrated the best way to open a textual content file and seek for a selected textual content string inside the file. Although most issues in Excel are solved by importing knowledge right into a workbook, typically it is simpler to work instantly with a textual content file and this code snippet exhibits the flexibility of VBA in a enterprise surroundings.