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”:

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 #1Print #1, "Changes to First Line of Text File"Close #1End 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:

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”:

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 #1strFinal = ""'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 #1Print #1, strFinal'overwrite the file with the new string Close #1End Sub

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:

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:

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:

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:

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

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:

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:

3 thoughts on“VBA, Modify Existing Text File”

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.

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: