Typically it's simpler to avoid wasting data in a textual content file, slightly than including one other sheet to a probably already cluttered Excel utility. In some cases, using a textual content file is perhaps the one possibility in a specific state of affairs.
This text will clarify how one can arrange a textual content file to make it straightforward to seek for, and retrieve data.
Organizing The File
We'll use the instance of utilizing a textual content file containing a code library, the place you may retailer particular person procedures for later use.
One approach to search the file is to position your individual tags across the code, in order that your individual VBA code can discover the block of textual content and do one thing with it. On this case, the "tags" are already in place with the sub and finish sub traces.
Maybe your textual content file seems to be one thing just like the textual content beneath and also you need to retrieve the changeMe process which adjustments the colour, daring and font kind of the chosen cell.
Sub firstCodeSnippet ()
Sub changeMe (cellAdress)
Vary (cellAddress) .Choose
.Title = "Tahoma"
.Daring = True
.ColorIndex = 3
Sub otherCodeSnippets ()
Now, you simply want to put in writing some VBA code to retrieve the info.
First, it’s essential open the file utilizing the file system object. Simply substitute your individual file and folder names to the code beneath:
Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
filePath = ActiveWorkbook.path & " recordsdata code.txt"
Set myFile = fso.openTextFile (filePath)
Our VBA code will learn the file line by line, and solely save knowledge contained between the traces sub changeMe and finish sub .
Subsequent, we are able to arrange a real or false set off which can inform the code to start out saving the info after it encounters the title of the process.
myProcedure = LCase ("sub changeMe")
startData = False
Now, we learn the info within the textual content file line by line.
Do Till myFile.AtEndOfStream
txt = myFile.ReadLine
When the code encounters sub changeMe within the textual content it begins to avoid wasting the code to a string and provides a brand new line character till the road finish sub. . Observe using the decrease case command to disregard capitals within the search.
If InStr (LCase (txt), myProcedure)> zero Then startData = True
If startData Then myCode = myCode & txt & vbCrLf
If InStr (LCase (txt), "finish sub")> zero And startData = True Then Exit Do
'Proceed studying the file
With the code saved, we are able to show the textual content in a message field with the next line:
Points To Think about
Some sensible points to contemplate is perhaps how one can arrange the search and show so the outcomes are usable. Listed below are some concepts:
- Use an enter field to kind within the code process you need to retrieve.
- Print and laminate a sheet which lists all of the entries within the library, so you may seek for the suitable code.
- As a substitute of utilizing a message field for the outcomes, print the code to a right away window or to a secure place in a worksheet.
Organizing code so it may be retrieved and used once more is a difficult situation for many VBA builders. In only a few traces of code, this text has proven how one can save and seek for code with out the information of extra superior ideas like person varieties or plug-ins.