How to insert a text into text file using VBA

Question

I want to insert a string at a particular position in text file of "utf-8" format.

let say the content in the file is "12367890"

now i want to insert "45" after "3" i.e at position 3,

now the content in the file becomes "1234567890"

I wrote some piece of but it is not working

dim str as string 
Dim binaryObj As Object

str = "12367890"

Set binaryObj = CreateObject("adodb.stream")
binaryObj.Open
binaryObj.Charset = "UTF-8"
binaryObj.Type = 2
h = 0
For h = 0 To length
    jpByte = Mid(jpString, h + 1, 1)
    binaryObj.WriteText jpByte
Next
binaryObj.WriteText ChrW(0)
binaryObj.Position = 6
binaryObj.WriteText "4"
binaryObj.Position = 7
binaryObj.WriteText "5"

binaryObj.SaveToFile "D:\A4\Message_tool\withBom.bin", adSaveCreateOverWrite

Instead of inserting 4 and 5, these are gettin replaced with 6 & 7. output = "12345890"

Look up [`FileSystemObject`](http://stackoverflow.com/a/5798392/380384) and how it can be used to handle text files. — John Alexiou, Oct 23 '14 at 13:46

score 0 · Answer 1 · answered Oct 23 '14 at 13:35

0

As you may have guessed, "WriteText" overwrites the text at that position, rather than inserting. Instead, write everything up until the new character insertion point (after the "3"), write the "4" and "5", then output the rest.

You may find it easier to read the file into a string, then manipulate the string with the built-in string functions, then output to the file, instead of manipulating text files.

answered Oct 23 '14 at 13:35

The other other Alan

1,868
12
21

[Hidden features of VBA with an emphasis on the `Mid$()` function](http://stackoverflow.com/questions/1070863/hidden-features-of-vba) – Oct 23 '14 at 13:50

score 0 · Answer 2 · edited May 23 '17 at 12:29

You can create a temp file with the modified text and replace this existing file. Here is a proof of concept.

Public Sub TextFileModify()

    Dim fso As New FileSystemObject
    Dim text As String, line As String, temp As String
    Dim path As String, fs As TextStream, fs2 As TextStream

    'First create a text file with original content
    path = fso.BuildPath(fso.GetSpecialFolder(2), "textfile.txt")
    Set fs = fso.CreateTextFile(path, True)
    fs.WriteLine "12367890"
    fs.WriteLine "other stuff"
    fs.Close

    'Now open the file to replace a line of text
    temp = fso.BuildPath(fso.GetSpecialFolder(2), fso.GetTempName())
    Set fs = fso.OpenTextFile(path, ForReading)
    Set fs2 = fso.CreateTextFile(temp)
    While Not fs.AtEndOfStream
        If fs.line = 1 Then
            line = fs.ReadLine
            fs2.WriteLine Left(line, 3) & "45" & Mid(line, 4)
        Else
            fs2.WriteLine fs.ReadLine
        End If
    Wend
    fs.Close
    fs2.Close

    'New delete old file and replace with new file
    fso.DeleteFile path
    fso.MoveFile temp, path

    ' textfile.txt now contains "1234567890" in the first line and the rest of the file is identical

End Sub

Notes:

You have to add a reference to the Microsoft Scripting Runtime (per. Example here)
fso.GetSpecialFolder(2) returns the path to your temp folder.
fso.GetTempName() returns a filename like radA5FC8.tmp

How do you do this when there are hundreds of lines? I can't use writeline for every one :( — Tianna Wrona, Sep 19 '17 at 20:45
The `WriteLine()` commands were only used to create the example file. It is very unlikely that the original file is going to be hard-coded with `WriteLine()` statements. It is more likely it is generated from somewhere else and the replace operation will only need to act to a single line. — John Alexiou, Sep 19 '17 at 22:04

How to insert a text into text file using VBA

2 Answers2

Linked