×

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

By Chris Newman •  Updated: 06/19/15 •  13 min read
VBA Create Delete Modify Text Files

Automating Text Files With VBA Useful?

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 or credentials for my macros that need to log into something.

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.


VBA Commands For Text File

When you are working with text files, there will be some terms used that you probably haven't seen or used before when writing VBA code.  Let's walk through these commands as you will see them throughout the VBA macros 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.
  • For Append - Add new text to the bottom of your text file content.
  • 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 quotes

Create/Save A Text File With VBA Code

In this section, you will learn how to generate a new Text file and write data into it.

The Open function in combination with the For Output command creates a new text file automatically if the provided file path yields no result. The For Output command lets you enter an editing state instead of a read-only state.

After the new text file has been opened, you can utilize the Print function to write data to the text file. Each Print command adds a new line to the file.

Once you are finished writing data into the text file, you can simply Close it to save your changes.

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

Create Text File From Spreadsheet Data

If you would like to write data into a new Text file from your spreadsheet, you can tweak the prior VBA macro code to loop through a range of cells and Print the cell data to the file.

Here’s an example of what you could create:

Sub TextFile_Create()
'PURPOSE: Create A New Text File And Write Spreadsheet Data to File
'SOURCE: www.TheSpreadsheetGuru.com

Dim TextFile As Integer
Dim FilePath As String
Dim cell As Range

'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 Spreadsheet Data To File (exclude blank cells)
  For Each cell In Range("A1:A20")
    If cell.Value <> "" Then Print #TextFile, cell.Value
  Next cell
  
'Save & Close Text File
  Close TextFile

End Sub

Extracting All Data From A Text File With VBA Code

If you are just wanting to pull the entirety of a text file’s contents and work with it in your VBA code, you can simply dump all the data into a string variable and then proceed to work with it.

The below VBA macro code will open a specific text file and store all of its contents into a string-based variable called FileContent. It’s then up to you to decide what you want to do next with that information 🙂

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

Edit/Modify A Text File With VBA Code (With Find/Replace)

If you need to edit the contents of a text file, you can open the file up in the For Input state. This will allow you to change things around and save your modifications.

For the example code in this article, you’ll learn how to use a Find and Replace functionality to replace all instances of the word “Goodbye” with “Cheers”.

We will also talk about adding data to the very end of a text file in a later section of this article.

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

Append Data To Your Text File With VBA Code

If you need to add data to the end of an existing text file, you can perform an Append action to the file.

The following VBA macro code shows you how to:

  1. Open a text file
  2. Write 3 lines of new data to the very bottom
  3. Save and close the file
Sub TextFile_Create()
'PURPOSE: Add More Text To The End Of A 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 Append As TextFile

'Write some lines of text
  Print #TextFile, "Sincerely,"
  Print #TextFile, ""
  Print #TextFile, "Chris"
  
'Save & Close Text File
  Close TextFile

End Sub

Deleting A Text File Macro Code

If you would like to automate deleting a text file, you can utilize the Kill command.

Delete A Single Text File With VBA

The following VBA macro code deletes a specific text file from the user’s Desktop.

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

Delete All Text Files In A Folder

If you are looking to perform file deletions on a larger scale you can write a looping macro that deletes all .txt files within a specified folder.

Sub DeleteTextFilesInFolder()
'PURPOSE: To loop through and delete all Text files in a folder
'SOURCE: www.TheSpreadsheetGuru.com

Dim FolderPath As String
Dim TextFile As String
Dim FileExtension As String
Dim Counter As Long

'Determine Folder Path and File Extension Type
  FolderPath = "C:\Users\chris\Desktop\"
  FileExtension = "*.txt"

'Target Path with Ending Extention
  TextFile = Dir(FolderPath & FileExtension)

'Loop through each Excel file in folder
  Do While TextFile <> ""
    Kill FolderPath & TextFile 'Delete file
    TextFile = Dir 'Get next file name
    Counter = Counter + 1
  Loop

'Message Box when tasks are completed
  MsgBox "Text Files Delete: " & Counter

End Sub

For more information/ideas on looping through all files within a specified folder, you can check out my article: Loop Through All Excel Files In A Given Folder with VBA.


VBA To Read A Text File Line By Line

This section will show you how to extract each line or row of data from your text file. This may be helpful if you need to pass the data through some sort of logic test or if you are searching for certain data points within the text file.

Write Each Text File Line To The Spreadsheet

The following VBA macro code reads through each line of a designated text file and writes the entire line to a cell in your spreadsheet (you determine the starting point with the variable StartCell).

Sub AddTextFileToSpreadsheet()
'PURPOSE: Loop through each row in Text File & Write to Excel
'SOURCE: www.TheSpreadsheetGuru.com

Dim FilePath As String
Dim TxtLine As String
Dim StartCell As Range
Dim x As Long

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

'Determine first cell you want data in
  Set StartCell = ActiveSheet.Range("A1")

'Open the text file in a Read State
  Open FilePath For Input As FreeFile
  
'Loop through each row in Text File and Write to Spreadsheet
  Do Until EOF(1)
    Line Input #1, TxtLine
    StartCell.Offset(x) = TxtLine
    x = x + 1
  Loop
    
'Close Text File
  Close FreeFile
  
End Sub

Write Each Text File Line To An Array Variable

The following VBA macro code reads through each line of a designated text file and writes the entire line to an array variable name LineArray. You can reference any line by writing the row number you are targeting and subtracting 1 (since array values start at 0 instead of 1).

For example, to retrieve the data stored in Row 5 you would write LineArray(5-1) or simply LineArray(4).

Sub TextFileLinesToArray()
'PURPOSE: Load an Array variable with each line of data text file
'SOURCE: www.TheSpreadsheetGuru.com

Dim TextFile As Integer
Dim FilePath As String
Dim FileContent As String
Dim LineArray() As String

'Text File Path
  FilePath = "C:\Users\chris\Desktop\MyFile.txt"
  
'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 (vbCrLf = Page Break)
  LineArray() = Split(FileContent, vbCrLf)
  
'Communicate How Many Lines are in Text File
  MsgBox "There were " & UBound(LineArray) + 1 & " lines of data found in the Text File"

End Sub

Fill Array With Delimited Text File Data With VBA Code

This VBA macro code can read a Text file from a provided path and populate an array variable by splitting the data based on a delimiter (separator).

The Delimiter variable is what determines how to split the data. In the below example a semi-colon is being used. The most common delimiter in text files is a comma character.

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

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can use VBA macro coding to manipulate and interact with Text Files for automated solutions. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.