VBA, Modify Existing Text File

Sep 14, 2015 by azurous in File and Folder Dialog

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

Jump To:

Example 1, Overwrite Existing File With New Data
Example 2, Modify Values in Text File
Example 3, Adding Open and Save File Dialogs

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.

Contents

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

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

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:

Opens the text file and reads all the lines
Creates a new string variable with the modified data.
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:

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 a nd 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$”):

arrStrings(0) = “1”
arrStrings(1) = “John”
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 a nd 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 a nd 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 a nd 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:

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:

'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.

VBA and VB.Net Tutorials, Education and Programming Services