The VBA Guide To Interacting With Text (.Txt) Files

VBA Create Delete Modify Text Files

Why Text Files?

Text files can be a very fast and simple way to read and store information. I like to use them to save settings for my VBA add-ins and I have seen situations where databases have exported large amounts of data into .txt files instead of Excel files (especially back in the days with Excel 2003). Below are the main techniques I use to create, modify, extract data, and delete text files.

Some Terminology

When we are working with text files, there will be some terminology that you probably haven't seen or used before when writing VBA code.  Let's walk through some of the pieces you will see throughout the code in this guide.

For Output - When you are opening the text file with this command, you are wanting to create or modify the text file. You will not be able to pull anything from the text file while opening with this mode.

For Input - When you are opening the text file with this command, you are wanting to extract information from the text file. You will not be able to modify the text file while opening it with this mode.

FreeFile - Is used to supply a file number that is not already in use. This is similar to referencing Workbook(1) vs. Workbook(2). By using FreeFile, the function will automatically return the next available reference number for your text file. 

Write - This writes a line of text to the file surrounding it with quotations

Print - This writes a line of text to the file without quotations


VBA For Creating A Text File

Sub TextFile_Create()
'PURPOSE: Create A New Text File
'SOURCE: www.TheSpreadsheetGuru.com

Dim TextFile As Integer
Dim FilePath As String

'What is the file path and name for the new text file?
  FilePath = "C:\Users\chris\Desktop\MyFile.txt"

'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile

'Open the text file
  Open FilePath For Output As TextFile

'Write some lines of text
  Print #TextFile, "Hello Everyone!"
  Print #TextFile, "I created this file with VBA."
  Print #TextFile, "Goodbye"
  
'Save & Close Text File
  Close TextFile

End Sub

VBA For Extracting All The Text From A Text File

Sub TextFile_PullData()
'PURPOSE: Send All Data From Text File To A String Variable
'SOURCE: www.TheSpreadsheetGuru.com

Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String

'File Path of Text File
  FilePath = "C:\Users\chris\Desktop\MyFile.txt"

'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile

'Open the text file
  Open FilePath For Input As TextFile

'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Report Out Text File Contents
  MsgBox FileContent

'Close Text File
  Close TextFile

End Sub

VBA For Modifying A Text File (With Find/Replace)

Sub TextFile_FindReplace()
'PURPOSE: Modify Contents of a text file using Find/Replace
'SOURCE: www.TheSpreadsheetGuru.com

Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String

'File Path of Text File
  FilePath = "C:\Users\chris\Desktop\MyFile.txt"

'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile

'Open the text file in a Read State
  Open FilePath For Input As TextFile

'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Clost Text File
  Close TextFile
  
'Find/Replace
  FileContent = Replace(FileContent, "Goodbye", "Cheers")

'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile

'Open the text file in a Write State
  Open FilePath For Output As TextFile
  
'Write New Text data to file
  Print #TextFile, FileContent

'Close Text File
  Close TextFile

End Sub

VBA For Deleting A Text File

Sub TextFile_Delete()
'PURPOSE: Delete a Text File from your computer
'SOURCE: www.TheSpreadsheetGuru.com

Dim FilePath As String

'File Path of Text File
  FilePath = "C:\Users\chris\Desktop\MyFile.txt"

'Delete File
  Kill FilePath

End Sub

VBA For Fill Array With Delimited Data From Text File

Sub DelimitedTextFileToArray()
'PURPOSE: Load an Array variable with data from a delimited text file
'SOURCE: www.TheSpreadsheetGuru.com

Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As String
Dim TempArray() As String
Dim rw As Long, col As Long

'Inputs
  Delimiter = ";"
  FilePath = "C:\Users\chris\Desktop\MyFile.txt"
  rw = 0
  
'Open the text file in a Read State
  TextFile = FreeFile
  Open FilePath For Input As TextFile
  
'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
  Close TextFile
  
'Separate Out lines of data
  LineArray() = Split(FileContent, vbCrLf)

'Read Data into an Array Variable
  For x = LBound(LineArray) To UBound(LineArray)
    If Len(Trim(LineArray(x))) <> 0 Then
      'Split up line of text by delimiter
        TempArray = Split(LineArray(x), Delimiter)
      
      'Determine how many columns are needed
        col = UBound(TempArray)
      
      'Re-Adjust Array boundaries
        ReDim Preserve DataArray(col, rw)
      
      'Load line of data into Array variable
        For y = LBound(TempArray) To UBound(TempArray)
          DataArray(y, rw) = TempArray(y)
        Next y
    End If
    
    'Next line
      rw = rw + 1
    
  Next x

End Sub

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)