Edit CSV and Text Files in Excel VBA Without Corrupting Data
Publication Date:
Updated:
INFORMATION > Edit CSV and Text Files in Excel VBA Without Corrupting Data
Bottom line: Treat a plain text file as lines only when its format truly is line-oriented. A CSV field can contain commas, quotes, and line breaks, so use a CSV-aware parser for general CSV data and write to a temporary file before replacing the original.
What you'll learn
- How VBA's file I/O modes differ for input, append, and output.
- How encoding, newline style, file locking, and error recovery affect a rewrite.
- Why simple string splitting is unsafe for arbitrary RFC-style CSV records.
Who this is for: Excel users maintaining small, controlled text-processing macros.
2026 context: The original macro demonstrates a specific local workflow and has not been generalized into a lossless CSV library. Back up representative files, test quoted and multiline values, and do not run a rewrite against the only production copy.
Overview
This article shows how to edit CSV and text files with VBA and covers these tasks:
- I want to add lines to an already existing csv or text file. I want to add text in the middle of a line.
- I want to delete lines from an already existing csv or text file.
Table of Contents
1. Programming Method
FileSystemObject does not provide an in-place operation for inserting text into the middle of a file or deleting one selected line. The usual approach is to read the content, modify it in memory, and rewrite the file safely.
The following is an example of programming reference.
The sample exposes the operation as a Function that can be called from a Sub procedure. Review its error handling, backup behavior, and file encoding before adapting it.
'Parameter Examples
'## filePath C:\Users\user\Desktop\test\test.txt
'## mode false:delete, true:write
'## targetRow 10
'## targetInput If in write mode, any character
'## lineBreakType false:vbLf, true:vbCrLf
Function editFile(filePath As String, mode As Boolean, targetRow As Long, targetInput As String, lineBreakType As Boolean)
'File existence check
If Dir(filePath) = "" Then
MsgBox "File does not exist!"
Exit Function
End If
Dim tempText As String
Dim tempTextBefore As String
Dim tempTextNew As String
Dim tempTextAfter As String
Dim resultText As String
Dim i As Long
'File read
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Open
.LoadFromFile filePath
tempText = .ReadText
.Close
End With
Dim tempTextBeforeRow As Long
tempTextBeforeRow = 0
For i = 1 To targetRow - 1
If lineBreakType Then
tempTextBeforeRow = InStr(tempTextBeforeRow + 1, tempText, vbCrLf)
Else
tempTextBeforeRow = InStr(tempTextBeforeRow + 1, tempText, vbLf)
End If
Next i
If lineBreakType Then
tempTextBeforeRow = tempTextBeforeRow + 1
End If
tempTextBefore = Left(tempText, tempTextBeforeRow)
'write
If mode Then
tempTextAfter = Mid(tempText, tempTextBeforeRow + 1, Len(tempText))
If lineBreakType Then
tempTextNew = targetInput & vbCrLf
Else
tempTextNew = targetInput & vbLf
End If
resultText = tempTextBefore & tempTextNew & tempTextAfter
'delete
Else
Dim tempTextAfterRow As Long
tempTextAfterRow = 0
For i = 1 To CInt(targetRow)
If lineBreakType Then
tempTextAfterRow = InStr(tempTextAfterRow + 1, tempText, vbCrLf)
Else
tempTextAfterRow = InStr(tempTextAfterRow + 1, tempText, vbLf)
End If
Next i
If lineBreakType Then
tempTextAfterRow = tempTextAfterRow + 1
End If
tempTextAfter = Mid(tempText, tempTextAfterRow + 1, Len(tempText))
resultText = tempTextBefore & tempTextAfter
End If
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
If lineBreakType Then
.LineSeparator = -1
Else
.LineSeparator = 10
End If
.Open
.WriteText tempTextBefore & tempTextNew & tempTextAfter, 0
.SaveToFile filePath, 2
.Close
End With
End Function
To use it, pass arguments as follows.
- filePath — This will be the full path of the file. Example)C:\Users\user\Desktop\test\test.txt
- mode — Pass true,false. If True, add. If False, delete the row.
- targetRow — Specify the number of rows to be targeted. In the case of addition, it is the number of lines to be added, and in the case of deletion, it is the number of lines to be deleted.
- targetInput — In the case of an append, specify the value to be appended. It is not used in the case of deletion, so any value is fine. Empty letters are also acceptable.
- lineBreakType — Setting the character for newlines. If True, vbCrLf. vbLf if False. A quick explanation is that for files created in Windows, it is True.
The sample uses UTF-8. To use another encoding, update both .Charset = "UTF-8" assignments and test round-trip behavior with representative data.
An example implementation of an actual Function call is shown below.
Sub test()
'Example of appending test123 to line 5 of test.txt. (Files created in Windows)
editFile "C:\Users\user\Desktop\test\test.txt", True, 5, "test123", True
'Example of deleting line 5 of test.txt. (Files created in Windows)
editFile "C:\Users\user\Desktop\test\test.txt", False, 5, "", True
End Sub
For CSV output, passing test1,test2,test3 appends that comma-separated text. This simple example does not quote commas, line breaks, or quotation marks inside fields; use a CSV-aware routine when those values are possible.
2. Code Description
This is the end of the explanation of how to use the system, followed by an explanation of the code.
The details can be found in the code, but a rough flow of the process is as follows.
For postscript
- ①Reading text files in the path passed as argument
- ②Specify the number of characters up to the number of lines passed in the argument from the number of newlines. For example, in the case of line 5, there are four line breaks before line 5, so find the position where the fourth line break is found
- ③Obtain information up to the fourth line.
- ④Obtain information after the 5th line in the same manner as (2).
- ⑤The information in (3), the information to be appended passed as an argument, and the information in (4) are combined together.
- ⑥Create a file based on the information in ⑤ and overwrite the file.
Deletion
- ①Reading text files in the path passed as argument
- ②Specify the number of characters up to the number of lines passed in the argument from the number of newlines. For example, in the case of line 5, there are four line breaks before line 5, so find the position where the fourth line break is found
- ③Obtain information up to the fourth line.
- ④Obtain information from line 6 onward in the same manner as (2).
- ⑤The information in (3) and (4) are combined together. (Create a string with no fifth line)
- ⑥Create a file based on the information in ⑤ and overwrite the file.
In the case of appending and deleting, both are the same, but rather than editing the file, it is as if the necessary information is extracted from the original file, a new file is created, and then overwritten and saved.
3. Conclusion
The sample demonstrates the file-editing flow for VBA, but it should be tested on a copy before it is used with important CSV or text data.
Official references
■INFORMATION
■PROFILE
■CONTACT