Tuesday, November 20, 2012

Using VBA To Search And Retrieve Data From A Text File Containing A Code Library

Sometimes it's easier to save information in a text file, rather than adding another sheet to a possibly already cluttered Excel application. In some instances, the use of a text file might be the only option in a particular situation.
This article will explain how you can organize a text file to make it easy to search for, and retrieve information.
Organizing The File
We'll use the example of using a text file containing a code library, where you can store individual procedures for later use.
One way to search the file is to place your own tags around the code, so that your own VBA code can find the block of text and do something with it. In this case, the "tags" are already in place with the sub and end sub lines.
Perhaps your text file looks something like the text below and you want to retrieve the changeMe procedure which changes the color, bold and font type of the selected cell.
Sub firstCodeSnippet()

' other subs

end subSub changeMe(cellAdress)

Range(cellAddress).Select

With Selection.Font

.Name = "Tahoma"

.Bold = True

.ColorIndex = 3

End With

End sub

Sub otherCodeSnippets()

' other subs

end sub

Now, you just need to write some VBA code to retrieve the data.

First, you need to open the file using the file system object. Just replace your own file and folder names to the code below: 
Dim fso As Scripting.FileSystemObject

Set fso = New Scripting.FileSystemObject

filePath = ActiveWorkbook.path & "\files\code.txt"

Set myFile = fso.openTextFile(filePath)
Our VBA code will read the file line by line, and only save data contained between the lines sub changeMe and end sub.
Next, we can set up a true or false trigger which will tell the code to start saving the data after it encounters the name of the procedure. 
myProcedure = LCase("sub changeMe")

startData = False

Now, we read the data in the text file line by line. 
Do Until myFile.AtEndOfStream

txt = myFile.ReadLine
When the code encounters sub changeMe in the text it begins to save the code to a string and adds a new line character until the line end sub. . Note the use of the lower case command to ignore capitals in the search. 
If InStr(LCase(txt), myProcedure) > 0 Then startData = True

If startData Then myCode = myCode & txt & vbCrLf

If InStr(LCase(txt), "end sub") > 0 And startData = True Then Exit Do' continue reading the file

Loop
With the code saved, we can display the text in a message box with the following line: 
Msgbox myCode

Issues To ConsiderSome practical issues to consider might be how to organize the search and display so the results are usable. Here are some ideas:
  • Use an input box to type in the code procedure you want to retrieve.
  • Print and laminate a sheet which lists all the entries in the library, so you can search for the appropriate code.
  • Instead of using a message box for the results, print the code to an immediate window or to a safe place in a worksheet.

SummaryOrganizing code so it can be retrieved and used again is a challenging issue for most VBA developers. In just a few lines of code, this article has shown how you can save and search for code without the knowledge of more advanced concepts like user forms or plug-ins.
at