Save and Retrieve Information With Text Files

Introduction

There are many techniques one can use to write information from a running Microsoft Office program. You can save data in a workbook, but this is unwieldy for saving small items, such as program settings. You can use ini files or the registry, but both can get complicated. Using the registry is more invasive than you may want to be, and it’s very hard to transfer registry settings among computers. One of the easiest methods to save and retrieve data is by using simple IO commands with text files. Such files are small, they are processed rapidly, they can be read with a simple text editor, and they can be easily copied from computer to computer.

Visual Basic I/O

Visual Basic contains some commands which are useful for fast and simple text file input and output. Since the Visual Basic for Applications (VBA) used to program Microsoft Office is based on VB, Excel and the other Office applications can use these commands to read and write text files. I frequently use this technique to save application settings or to store debugging information.

The VBA help files contain detailed information about the various IO commands. Rather than beat the topic to death, I’ll present a couple simple examples that write and read text from a text file, then I’ll show some practical examples. The reader is directed to the help files for further details.

The subroutine TextIODemoWrite, shown below, opens a specified text file, puts a simple text string into the file, then closes the file. Files are opened using integers starting with 1; the FreeFile function assigns the next available integer to the file, to prevent accidentally assigning the same integer to two files. When you Open the file, a buffer is allocated to it, and the access mode (e.g., Output) is assigned. If the file does not already exist, it is automatically created. Subsequent commands (Write and Close) refer to the file using its integer. Text can be written using Write or Print; refer to the help files for specifics. Closing a file deallocates the buffer and disassociates the file from the file number.

Sub TextIODemoWrite()
  Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "C:\test\textio.txt"
  sText = "Testing 1 2 3."

  iFileNum = FreeFile
  Open sFile For Output As iFileNum
  Write #iFileNum, sText
  Close #iFileNum
End Sub

The subroutine TextIODemoRead below shows the reverse operation, opening a text file, retrieving some text, closing the text file, then displaying the text in a message box.

Sub TextIODemoRead()
  Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "C:\test\textio.txt"

  iFileNum = FreeFile
  Open sFile For Input As iFileNum
  Input #iFileNum, sText
  Close #iFileNum
  MsgBox sText
End Sub

Save and Retrieve Settings

The VBA procedures above can be expanded to allow us to save settings for later retrieval. The simplistic approach used in function SaveSetting reads variable names and values from a file, and writes them to a temporary file. If the named variable already exists, its new value is written to the temporary file in place of the existing value. When all variables have been read and written, the original file is replaced by the new file.

SaveSetting uses some additional functions, IsFullName, FullNameToPath, FullNameToFileName, and FileExists, which are shown at the end of this article.

Function SaveSetting(sFileName As String, sName As String, _
      Optional sValue As String) As Boolean
  
  Dim iFileNumA As Long
  Dim iFileNumB As Long
  Dim sFile As String
  Dim sXFile As String
  Dim sVarName As String
  Dim sVarValue As String
  Dim lErrLast As Long
  
  ' assume false unless variable is successfully saved
  SaveSetting = False
  
  ' add this workbook's path if not specified
  If Not IsFullName(sFileName) Then
    sFile = ThisWorkbook.Path & "\" & sFileName
    sXFile = ThisWorkbook.Path & "\X" & sFileName
  Else
    sFile = sFileName
    sXFile = FullNameToPath(sFileName) & "\X" & FullNameToFileName(sFileName)
  End If
  
  ' open text file to read settings
  If FileExists(sFile) Then
    'replace existing settings file
    iFileNumA = FreeFile
    Open sFile For Input As iFileNumA
    iFileNumB = FreeFile
    Open sXFile For Output As iFileNumB
      Do While Not EOF(iFileNumA)
        Input #iFileNumA, sVarName, sVarValue
        If sVarName <> sName Then
          Write #iFileNumB, sVarName, sVarValue
        End If
      Loop
      Write #iFileNumB, sName, sValue
      SaveSetting = True
    Close #iFileNumA
    Close #iFileNumB
    FileCopy sXFile, sFile
    Kill sXFile
  Else
    ' make new file
    iFileNumB = FreeFile
    Open sFile For Output As iFileNumB
      Write #iFileNumB, sName, sValue
      SaveSetting = True
    Close #iFileNumB
  End If
  
End Function

The function is called using this syntax (bTest is declared as a Boolean). The filename (with or without path), the variable name, and the value are all passed to the function as strings. If a path is not included as part of the file name, then the workbook’s own path is used. The value of the function is true unless an error is encountered.

bTest = SaveSetting("C:\test\settings.txt", "test variable", "test value")

Subroutine GetSetting enumerates the variables in the file until the named variable is found, then it extracts the value of this variable.

Function GetSetting(sFile As String, sName As String, _
      Optional sValue As String) As Boolean
  
  Dim iFileNum As Long
  Dim sVarName As String
  Dim sVarValue As String
  Dim lErrLast As Long
  
  ' assume false unless variable is found
  GetSetting = False
  
  ' add this workbook's path if not specified
  If Not IsFullName(sFile) Then
    sFile = ThisWorkbook.Path & "\" & sFile
  End If
  
  ' open text file to read settings
  If FileExists(sFile) Then
    iFileNum = FreeFile
    Open sFile For Input As iFileNum
      Do While Not EOF(iFileNum)
        Input #iFileNum, sVarName, sVarValue
        If sVarName = sName Then
          sValue = sVarValue
          GetSetting = True
          Exit Do
        End If
      Loop
    Close #iFileNum
  End If
  
End Function

The function is called using this syntax. The filename (with or without path) and the variable name are passed to the function as strings. The value of the function is true unless an error is encountered (i.e., the file or the variable is not found), and the value of the variable is passed back by the function.

If GetSetting("C:\test\settings.txt", "test variable", sValue) Then
  MsgBox sValue
End If

Save Debugging Information

During development or debugging of a program, it’s useful to save information during its execution. Subroutine DebugLog saves information in a text file with a name like debuglog051225.txt in the parent workbook’s directory. It saves the date and time along with the debug message, so the timing of the messages can be followed. You can log any string value, including markers indicating how far program execution has progressed, what the value of a key variable is, what an error may have occurred, etc.

Public Sub DebugLog(sLogEntry As String)
  ' write debug information to a log file

  Dim iFile As Integer
  Dim sDirectory As String
  
  sDirectory = ThisWorkbook.Path & "\debuglog" & Format$(Now, "YYMMDD") & ".txt"

  iFile = FreeFile

  Open sFileName For Append As iFile
  Print #iFile, Now; " "; sLogEntry
  Close iFile

End Sub

The following are a few examples of how to use DebugLog.

DebugLog "Starting Execution"

DebugLog "Variable MyVar = " & MyVar

DebugLog "Error " & Err.Number & ": " & Err.Description

The following is an excerpt from an actual debuglog file, debuglog051223.txt created by one of my old projects.

12/23/2005 8:00:33 AM  Excel Version 9.0, Build 8924
12/23/2005 8:00:33 AM  Program Starting
12/23/2005 8:00:33 AM  - File Name: ABC Engineering 2005-12-23-0.doc
12/23/2005 8:00:33 AM  - Order Number: ABC Engineering 2005-12-23-0
12/23/2005 8:00:38 AM  - saved as C:\Orders\ABC Engineering 2005-12-23-0.doc
12/23/2005 8:00:38 AM  - file exists: True
12/23/2005 8:00:38 AM  - no backup directory specified
12/23/2005 8:01:25 AM  - Checking Row 17: 3chars
12/23/2005 8:01:26 AM  - Checking Row 16: 3chars
12/23/2005 8:01:26 AM  - Checking Row 15: 3chars
12/23/2005 8:01:26 AM  - Checking Row 14: 3chars
12/23/2005 8:01:26 AM  - Checking Row 13: 3chars
12/23/2005 8:01:27 AM  - Checking Row 12: 3chars
12/23/2005 8:01:27 AM  - Checking Row 11: 3chars
12/23/2005 8:01:27 AM  - Checking Row 10: 3chars
12/23/2005 8:01:27 AM  - Checking Row 9: 15chars
12/23/2005 8:01:30 AM  - removing extra empty paragraphs
12/23/2005 8:01:56 AM  Program Finished

Supplementary Functions

Function IsFullName(sFile As String) As Boolean
  ' if sFile includes path, it contains path separator "\"
  IsFullName = InStr(sFile, "\") > 0
End Function

Function FullNameToPath(sFullName As String) As String
  ''' does not include trailing backslash
  Dim k As Integer
  For k = Len(sFullName) To 1 Step -1
    If Mid(sFullName, k, 1) = "\" Then Exit For
  Next k
  If k < 1 Then
    FullNameToPath = ""
  Else
    FullNameToPath = Mid(sFullName, 1, k - 1)
  End If
End Function

Function FullNameToFileName(sFullName As String) As String
  Dim k As Integer
  Dim sTest As String
  If InStr(1, sFullName, "[") > 0 Then
    k = InStr(1, sFullName, "[")
    sTest = Mid(sFullName, k + 1, InStr(1, sFullName, "]") - k - 1)
  Else
    For k = Len(sFullName) To 1 Step -1
      If Mid(sFullName, k, 1) = "\" Then Exit For
    Next k
    sTest = Mid(sFullName, k + 1, Len(sFullName) - k)
  End If
  FullNameToFileName = sTest
End Function

Function FileExists(ByVal FileSpec As String) As Boolean
   ' by Karl Peterson MS MVP VB
   Dim Attr As Long
   ' Guard against bad FileSpec by ignoring errors
   ' retrieving its attributes.
   On Error Resume Next
   Attr = GetAttr(FileSpec)
   If Err.Number = 0 Then
      ' No error, so something was found.
      ' If Directory attribute set, then not a file.
      FileExists = Not ((Attr And vbDirectory) = vbDirectory)
   End If
End Function

Final Words

This article was originally posted on an old site which no longer exists. I was able to retrieve my work from this defunct site, so I am reposting it here as is. There are newer techniques for working with text files in Windows, but sometimes the old ways are as simple and effective as the new.

 

Peltier Tech Charts for Excel

Comments

  1. Great post Jon! I have always been one to save user information (ie preferences/settings) to a registry instead of a text file. Typically this is because it is harder for the user to accidentally delete but you did bring up a very good point in that it is more difficult to transfer to another computer. I’ll definitely be digging into how to incorporate the great ideas and information from this post into my workflow!

  2. Mind. Blown. I’ve been using cell locations on a worksheet tab to store variables that I want to stay persistent for years, with giant headers that say “do not touch!” Storing the variable to a text file makes so much more sense.

  3. This is a great resource! Thanks Jon! You could also put the text file on a server and point the application to the server location so all users have the same settings.

  4. @Jon Acampora Love that idea!

  5. If users have Personal Macro Workbooks, I wonder if there’s any downside to saving app settings as named constants in the PMW itself? e.g.
    Workbooks(“Personal”).Names.Add name:=”appSomeApp_SomeSetting”, refersto:=”SomeValue”

    ? [PERSONAL.XLSB!appSomeApp_SomeSetting]
    SomeValue

  6. Jon, unless I’m misunderstanding something, I think you’ve put the wrong code example after the GetSetting and SaveSetting routines. After the GetSetting routine, your example calls SaveSetting. Whereas I was expecting to see something like this there:

    Sub test()
    Dim sValue As String
    Dim sVariable As String

    sVariable = "test variable"
    If GetSetting("C:\test\settings.txt", sVariable, sValue) Then
    MsgBox sValue
    End If
    End Sub

  7. Jeff –

    1. If people use their personal macro workbook, then saving a setting in this workbook is fine. In fact, I have parallel routines for saving information in a separate worksheet.

    I don’t use a personal macro workbook, I put everything into add-ins, and I don’t like saving settings in add-ins. Usually I use the registry, but these techniques are great for outputting debug information.

    2. I think the only mistake, which I’ve fixed, was calling SaveSetting after listing the GetSetting function. Or am I being dense this morning?

  8. Jon, that’s fixed it. So you’re being no more dense than usual for this time of the morning ;-)

  9. Why don’t you like saving settings in addins?

  10. The major issue I have with saving settings in add-ins is when the user opens two or more instances of Excel. This will cause a read-only version of the add-in to be opened with each new instance. If your add-in file tries to save on the workbook_close event, then an error will be thrown. You can bypass it, but then the users settings won’t be saved. So an external text file is a great workaround to this issue.

    I would love to know if there is away around this issue with multiple instances opening multiple versions of the add-ins.

    And thanks Chris!

  11. Jeff –

    Separation of code and data. If the add-in gets updated, the settings data is lost.

    Jon’s conflicting instances is another good reason.

  12. I think these lines are the wrong way round in the SaveSetting function:

    sFile = ThisWorkbook.Path & “\” & sFile
    sXFile = ThisWorkbook.Path & “\X” & sFile

  13. No, it’s correct. sFile is the original file, which we are reading from. sXFile is the new file, which we are writing to. When we have finished we overwrite sFile with sXFile.

  14. I understand that. What I meant was, the way it is written, sFile gets a thisworkbook.path, and sXFile gets a thisworkbook.path, and then sFile’s thisworkbook.path, causing an error as the path is wrong. So sXFile is constructed as

    Thisworkbook.path & “\”X” & thisworkbook.path & “\” & sFile

    where sFile is the original sFile. If you swap the lines around, this is fixed as sFile isn’t messed with for the construction of sXFile.

    I hope this is a bit clearer.

  15. D’oh! How embarrassing. I rechecked a workbook where I use this, and realized I used a different variable name for the passed-in file name and for the existing data file. I’ve incorporated the changes above.

  16. Great discussion,
    Please also see this post if a setting type requirement is need
    http://www.blog.methodsinexcel.co.uk/2012/06/07/a-universal-application-settings-control-for-excel-vba-programes/

    Thanks

    Ross

  17. Hi Jon, my website http://www.Engineers-Excel.com has a collection of Excel tools for engineers, including pendulum waves, process animation, and various simulators.

    Best wishes,
    Nitin

  18. Sorry posted to wrong page…will repost to the engineering page.

  19. Hi Jon,
    I am a beginner to VB6. I have a very lengthy text only data MS Word File divided into paragraphs. I intend to write a program in VB6 wherein if Condition ‘A’ is met then certain paragraphs are retried from the above MS Word File and are saved in a New MS Word File likewsie if Condition ‘B’ is met the same procedure is followed and this information also goes in the same above mentioned MS Word File (In all, there are 256 conditions). Ultimately all the information is saved in one single MS Word File which can be edited, displayed and a print out can be taken Pl enlighter whether it is possible in VB6. If yes please explain the entire procedure. I shall be grateful. Please also tell if there is any other method with which can be done.
    Regards
    Vijay Nanda

  20. You could certainly do this in VB6 (or VB.Net or C# or whatever), but I think I’d do it directly in Word VBA. After a couple stiff drinks, because Word VBA will do that to a programmer.

    Although I’ve done some programming in Word VBA, and of Word from Excel VBA, this is not the appropriate place to learn about the Word Object Model. Go back to your favorite search engine and look for resources that cover Word VBA Object Model or Word VBA Programming. Warning: sources are not nearly as common as for Excel VBA, and the object model was written by developers on peyote.

  21. Hi, Jon. I’ve pasted all of your code into a blank workbook to test. I get a run-time error 75 (Path/file access error) at “Open sFile For Output As iFileNumB”. I tested using bTest = SaveSetting(“C:\test\settings.txt”, “test variable”, “test value”)

    I do have access to this location and even tried creating the file before hand and get the same error at the same line as above. I’m probably doing something stupid (noob here). Any pointers? Thanks..

  22. Seems to work now after making a slight tweak:

    ‘ add this workbook’s path if not specified
    If Not IsFullName(sFileName) Then
    sFile = ThisWorkbook.Path & “\” & sFileName
    sXFile = ThisWorkbook.Path & “\X” & sFileName
    Else
    sFile = sFileName
    sXFile = FullNameToPath(sFileName) & “\X” & FullNameToFileName(sFileName)
    End If

  23. Oh, good catch. I rarely use this with files in a directory other than where ThisWorkbook is located. So I guess I hadn’t tested both options. I have adjusted the original code.

Trackbacks

  1. […] Save and Retrieve Information With Text Files | Peltier Tech Blog […]

  2. […] Save and Retrieve Information With Text Files | Peltier Tech Blog […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0