Code to replace text using VBA OpenTextFile write leaves file corrupted

Question

I wish to open a .docx file from a macro in Excel file, look for all instances of a String in that file and replace that String with another.

I can find and open the file. I do not know if the String has been replaced, because the code below corrupts the file. The file can no longer be opened by Word.

Here is the code for the macro:

Dim objFSO
Dim objTS 'define a TextStream object
Const ForReading = 1
Const ForWriting = 2
Const TristateUseDefault = -2

Dim strContents As String
Dim fileSpec As String

Sub SearchAndReplaceTextInFile()
    fileSpec = "C:\some\path\to\file\location\file.docx"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTS = objFSO.OpenTextFile(fileSpec, ForReading, TristateUseDefault)

    strContents = objTS.ReadAll
    strContents = Replace(strContents, "adress", "address")

    Set objTS = objFSO.OpenTextFile(fileSpec, ForWriting, TristateUseDefault)

    objTS.Write strContents
    objTS.Close
End Sub

TinMan · Accepted Answer · 2018-06-22 18:58:12Z

2

OpenTextFile only supports two iomode modes, ForReading and ForAppending. To replace the text in a text file use CreateTextFile and set the overwrite to True.

Find and Replace in Text File

Dim objFSO
Dim objTS                                             'define a TextStream object
Const ForReading = 1
Const ForWriting = 2
Const TristateUseDefault = -2

Dim strContents As String
Dim fileSpec As String

Sub SearchAndReplaceTextInFile()
    fileSpec = "C:\Users\best buy\Documents\EBirdRegionalRequest.txt"    '"C:\some\path\to\file\location\file.docx"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTS = objFSO.OpenTextFile(Filename:=fileSpec, iomode:=ForReading, Format:=TristateUseDefault)

    strContents = objTS.ReadAll
    strContents = Replace(strContents, "adress", "address")

    Set objTS = objFSO.CreateTextFile(Filename:=fileSpec, overwrite:=True)

    objTS.Write strContents
    objTS.Close

    Debug.Print objFSO.OpenTextFile(Filename:=fileSpec, iomode:=ForReading, Format:=TristateUseDefault).ReadAll
End Sub

Find and Replace in Word Document

Sub SearchAndReplaceTextWordDoc(fileSpec As String, Find As String, Replace As String)
    Const wdReplaceAll As Long = 2
    Const wdFindContinue = 1
    Dim wdRange As Variant
    Dim doc As Object, wdApp As Object
    Set wdApp = CreateObject("Word.Application")
    Set doc = wdApp.Documents.Open(Filename:=fileSpec, ReadOnly:=False)

    For Each wdRange In doc.StoryRanges
        With wdRange.Find
            .Text = Find
            .Replacement.Text = Replace
            .Wrap = wdFindContinue
            .ClearFormatting
            .Replacement.ClearFormatting
            .Replacement.Highlight = False
            .Execute Replace:=wdReplaceAll
        End With
    Next
    wdApp.Visible = True

    doc.Close SaveChanges:=True
    wdApp.Quit
End Sub

Comments

You cannot use a FileSystemObject to modify an Office Document. Office Documents are actually Zip files. If you change the file extension of a docx or xlsx to zip and open it, you will see several folders. With in these folders are a manifest, meta data, and xml files.

edited Jun 22, 2018 at 18:58

answered Jun 21, 2018 at 8:36

TinMan

7,8142 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mycowan Over a year ago

This works with txt files. But with docx files, they become corrupted.

TinMan Over a year ago

Sorry about that. You should open the doc file with CreateObject("Word.Application")

TinMan Over a year ago

@mycowan I wish I would have noticed the file extension. I assumed that you were opening a text file with word. Thanks for accepting my answer. Happy Coding!!

Collectives™ on Stack Overflow

Code to replace text using VBA OpenTextFile write leaves file corrupted

1 Answer 1

Find and Replace in Text File

Find and Replace in Word Document

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Find and Replace in Text File

Find and Replace in Word Document

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related