VBA, Modify Existing Text File

In this article I will explain how you can modify an existing text file.

Jump To:

You can download the file and code related to this article here. Please note that although the code is written in an Excel file, it is applicable to all MS Office products.

Example 1, Overwrite Existing File With New Data:

When you open a text file and modify its content, although it may seem you are modifying its content but you are actually creating a new text file and overwriting the previous file. Lets say we have the following text file in the location “D:Temp” with the name “Test.txt”:

VBA, Modify Text File, Only one Line
The code below will open the text file and change the first line to “Changes to First Line of Text File”:

Sub Example1()
Open "D:TempTest.txt" For Output As #1
Print #1, "Changes to First Line of Text File"
Close #1
End Sub

Basically what happened in the code above was that a new text file was created and the previous text file was overwritten with new data:

VBA, Modify Text File, Only one Line, Result
For more information about writing to text files please see VBA Excel Writing to Text Files.


Example 2, Modify Values in Text File:

In this example the following data is in a text file located in the path “D:Temp with the name “Test.txt”:

VBA, Modify Text File
The code below adds 100$ to the 3rd column:

Sub Example2()
'Final string to print to file
Dim strFinal As String
'Line read from original text file
Dim strLine As String
Close #1
'open the text file for reading
Open "D:TempTest.txt" For Input As #1
strFinal = ""
'loop until the end of the text file is reached
While EOF(1) = False
'read the current line from the text file
Line Input #1, strLine
'concatenate the modified line to the rest of the string
strFinal = strFinal + ModifyColumn3(strLine)
Wend
strFinal = Left(strFinal, Len(strFinal) - 2)
'close the file
Close #1

'open the file for writing
Open "D:TempTest.txt" For Output As #1
Print #1, strFinal
'overwrite the file with the new string
Close #1
End Sub

'this function adds 100$ to the last column
Function ModifyColumn3(ByVal strInput As String) As String
Dim arrString() As String
Dim strOutput As String
'split the columns
arrString = Split(strInput, " ")
'concatenate the first 2 column as they are
strOutput = arrString(0) + " " + arrString(1) + " "
'add 100$ to column3
strOutput = strOutput + Strings.Trim(Str(CDbl(Left(arrString(2), _
Len(arrString(2)) - 1)) + 100)) + "$" + vbCrLf
ModifyColumn3 = strOutput
End Function

Basically the code above does 3 thing:

  1. Opens the text file and reads all the lines
  2. Creates a new string variable with the modified data.
  3. Overwrites the original file.

Although it seems we are modifying the original file but we are actually overwriting the original file with a new file.

The final result can be seen in the figure below:

VBA, Modify Text File Result

The line below opens the text file for reading:

Open "D:TempTest.txt" For Input As #1

The code below loops through the text file until the last line is reached:

While EOF(1) = False
'read the current line from the text file
Line Input #1, strLine
'concatenate the modified line to the rest of the string
strFinal = strFinal + ModifyColumn3(strLine)
Wend

The line below reads the current line of the text file and stores it in the variable strLine:

Line Input #1, strLine

The line below passes the string strLine to the function ModifyColumn3() . ModifyColumn3() adds 100$ dollars to the last part of the input string. For example for the first line strLine has the value “1 John 134$”. After passing it to the function ModifyColumn3() the output value will be “1 John 234$”. The return value from the function ModifyColumn3() is then concatenated with the string strFinal. For more information about concatenating strings please see VBA Excel String Processing and Manipulation:

strFinal = strFinal + ModifyColumn3(strLine)

The function ModifyColumn3() receives as input a line of text from the original text file. It adds 100$ to the last column and returns the result as output:

Function ModifyColumn3(ByVal strInput As String) As String

End Function

The line below splits the input string based on the space delimiter (” “). The result is an array with 3 strings. For example for the first line (“1 John 134$”):

  1. arrStrings(0) = “1”
  2. arrStrings(1) = “John”
  3. arrStrings(2) = “234$”

For more information about splitting strings please see Excel VBA String Processing and Manipulation, Splitting Strings:

arrString = Split(strInput, " ")

The next line concatenates the first 2 columns without making any changes. For more information about concatenating strings please see VBA Excel String Processing and Manipulation:

strOutput = arrString(0) + " " + arrString(1) + " "

The next line adds 100$ to the value in column 3. This is done in several steps:

Step 1: First the numeric part of the string is extracted. For example in the first line the last column is “134$”. The numeric part would be “134”. For more information about the Left function please see Excel VBA String Processing and Manipulation, Strings.Left():

Left(arrString(2), Len(arrString(2)) - 1)

Step 2: In the next step the numeric part is converted to a double value. Note we can’t add the value 100 to a string variable. That is the reason for this conversion. For more information about converting data types please see VBA Converting Data Types, Converting String to Double:

CDbl(Left(arrString(2), Len(arrString(2)) - 1))

Step 3: The next step adds 100 to the value in the 3rd column:

CDbl(Left(arrString(2), Len(arrString(2)) - 1)) + 100

Step 4: The new number is converted to a string again. For more information about converting numeric values to strings please see VBA Converting Data Types, Converting a Numeric Value to a String, Str():

Str(CDbl(Left(arrString(2), Len(arrString(2)) - 1)) + 100)

Step 5: The string variable is concatenated with the dollar sign ($). For more information about concatenating strings please see VBA Excel String Processing and Manipulation:

Str(CDbl(Left(arrString(2), Len(arrString(2)) - 1)) + 100) + "$"

Step 6: A line feed character is added to the end of the string:

Str(CDbl(Left(arrString(2), Len(arrString(2)) - 1)) + 100) + "$" + vbCrLf

Step 7: Column3 is concatenated with the rest of the columns. For more information about concatenating strings please see VBA Excel String Processing and Manipulation:

strOutput = strOutput + Str(CDbl(Left(arrString(2), _
Len(arrString(2)) - 1)) + 100) + "$" + vbCrLf

The line below removes the carriage return and line feed characters added to the last line:

strFinal = Left(strFinal, Len(strFinal) - 2)


Example 3, Adding Open and Save File Dialogs:

In the previous example the location of the input text file was predefined. It was also assumed that the new file overwrites the original file. In this example open and save file dialogs will be used. The open file dialog will request the user to select the location of the original text file. The save file dialog will prompt the user for a location to save the modified file. For more information about file and folder dialogs please see File and Folder Dialogs VBA:

Sub Example3()
'selected path for opening the text file
Dim strOpenPath As String
'selected path for saving the text file
Dim strSavePath As String
'open file dialog result
Dim intOpenResult As Integer
Dim strFinal As String
Dim strLine As String
Close #1
'remove all previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'apply the .txt file type filtering
Call Application.FileDialog(msoFileDialogOpen).Filters.Add("Text File", _
"*.txt")
'allow the user to select only one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'display the dialog
intOpenResult = Application.FileDialog(msoFileDialogOpen).Show
If intOpenResult <> 0 Then
'get file path
strOpenPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Open strOpenPath For Input As #1
strFinal = ""
While EOF(1) = False
Line Input #1, strLine
strFinal = strFinal + ModifyColumn3(strLine)
Wend
Close #1
'get save location
strSavePath = Application.GetSaveAsFilename(FileFilter:="Text File" & _
"(*.txt),*.txt", Title:="Save Location")
If strSavePath <> "False" Then
Open strSavePath For Output As #1
Print #1, strFinal
Close #1
End If
End If
End Sub

An open file dialog is displayed asking the user to select the original .txt file:

VBA, Open File Dialog
Once the program has read the file and created the output string another dialog is opened asking the user to select a location to save the file:

VBA, Save File Dialog
The code below sets the open file dialog’s properties and displays it to the user. For more information about the command Application.FileDialog(msoFileDialogOpen) please see Excel VBA Open File Dialog:

'remove all previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear
'apply the .txt file type filtering
Call Application.FileDialog(msoFileDialogOpen).Filters.Add("Text File", _
"*.txt")
'allow the user to select only one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'display the dialog
intOpenResult = Application.FileDialog(msoFileDialogOpen).Show

The If statement checks if the user has selected a file:

If intOpenResult <> 0 Then
...
End If

The line below returns the path the user has selected:

'get file path
strOpenPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

The line below displays a save file dialog with a custom title and custom filtering. For more information about the Application.GetSaveAsFilename() command please see Excel VBA Save File Dialog GetSaveAsFilename():

strSavePath = Application.GetSaveAsFilename(FileFilter:="Text File" & _
"(*.txt),*.txt", Title:="Save Location")

You can download the file and code related to this article here. Please note that although the code is written in an Excel file, it is applicable to all MS Office products.

See Also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

3 thoughts on “VBA, Modify Existing Text File”

  1. thetao2 says:

    Can you delete everything before or after a particuliar string if you do not know what dataline it is one or if it may not be the first string on the line? For instance I have a file that has a bunch of text before the opening tag of some XML data. I would like to delete everything before the opening tag but the tag is not always the first thing on the dataline.

    1. pedrumj says:

      Hello thetao2

      Yes it is possible. After reading the data in the text file, you would need to use the function, InStr() to find the location of the tag you are looking for. Then you could use the function Strings.Right() to trim the beginning of the string variable.

      Please take a look at the following article for more information about the InStr() and String.Right() functions:

      http://software-solutions-online.com/2014/02/11/2/

      Also if you would like someone to do this for you feel free to send me an email along with the file and project details:

      Pedrum.Jalali@gmail.com

  2. J says:

    Excellent, would you have an example of:

    with VBA XHR – how to send a second OPEN and SEND to post input data, for same open URL

    I posted on Stack Exchange: https://stackoverflow.com/questions/46993187/with-vba-xhr-how-to-send-a-second-open-and-send-to-post-input-data-for-same-o

Leave a Reply

Your email address will not be published. Required fields are marked *

privacy policy