Utilizing VBA To Search And Retrieve Knowledge From A Textual content File Containing A Code Library

[ad_1]

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 () 
  'different subs 
  finish sub 
 Sub changeMe (cellAdress) 
 Vary (cellAddress) .Choose 
 With Choice.Font 
 .Title = "Tahoma" 
 .Daring = True 
 .ColorIndex = 3 
 Finish With 
 Finish sub 
 Sub otherCodeSnippets () 
 'different subs 
 finish sub 

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 
  Loop 

With the code saved, we are able to show the textual content in a message field with the next line:

  Msgbox myCode 

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.

Abstract

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.

[ad_2]
Supply by Andy L Gibson