The VBA Guide To Interacting With Text (.Txt) 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 SubCreate 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 SubExtracting 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 SubEdit/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 SubAppend 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:
- Open a text file
- Write 3 lines of new data to the very bottom
- 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 SubDeleting 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 SubDelete 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 SubFor 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 SubWrite 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 SubFill 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 SubI 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.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
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.