Find and replace in text files

Replacing text inside text files doesn't mean you need to open them or paste them into Excel. We can do everything strictly in VBA.

All we need to do is use VBA's own I/O commands: Open, Print, Close, etc…

This procedure will open a given text file and replace whatever you need. It works similar to the Replace function except instead of a string variable you pass it the name of a file. Although ultimately we do use the Replace function to do the dirty work.

Sub FindAndReplace(filePath As String, findWhat As String, _
                   replaceWith As String)
' from Excel Help:
' A variable-length string can contain up to approximately 2 billion
' (2^31) characters.
Dim nextFileNum As Long
Dim oldFileContents As String
Dim newFileContents As String
Dim textFileTypes() As String
Dim fileExtension As String

  If Len(Dir(filePath)) = 0 Then
    Exit Sub
  End If

  ' only act on "text" files
  textFileTypes = QuoteString("txt csv html xml", ",")

  fileExtension = LCase$(Right$(filePath, 3))

  If UBound(Filter(textFileTypes, fileExtension)) = -1 Then
    Exit Sub
  End If

  ' open file and read contents
  nextFileNum = FreeFile

  Open filePath For Input As #nextFileNum
  oldFileContents = Input$(LOF(nextFileNum), #nextFileNum)
  Close #nextFileNum

  ' replace old char with new char
  newFileContents = Replace(oldFileContents, findWhat, replaceWith)

  ' reopen file and write new contents
  nextFileNum = FreeFile

  Open filePath For Output As #nextFileNum
  Print #nextFileNum, newFileContents
  Close #nextFileNum

End Sub

Function QuoteString(str As String, delimiter As String) As String()

Dim tempString() As String
Dim newString As String

  newString = Replace(str, " ", delimiter)

  ' split the string into an array, using delimiter
  tempString = Split(newString, delimiter)

  QuoteString = tempString

End Function

Let's examine how this function (and the secondary function QuoteString) works.

We pass in a filename, the character being sought, and the character to replace it with. First we check if the filename is valid. Note that we could also skip this check and use this function to create the file if it doesn't exist.

We delimit the file types to just "text" files that can be opened using Notepad. You can add more filetypes here. Because I was lazy when I wrote this, I've kept these checks inline instead of factoring them out into their own functions.

The QuoteString function returns a String array of file extensions. I'll admit I did go a little out of the way to make the function harder to follow. All it does is take a space-delimited list of file extensions and the delimiter we want to use. It returns a String array of comma-delimited file extensions. I used it because I like declaring textFileTypes as String instead of as Variant (which I would need to do if I wanted to use the Array function to pass the comma-delimited string directly). Also, it's easier for me to write "txt csv html xml" than it is to encase each discrete String in quotes like this: "txt", "csv", "html", "xml". I usually mess it up.

The file is opened and its contents are placed into a String variable. There is a character limit here; although I doubt you'll hit it, it is something to be aware of. The Replace function takes out the sought after character(s) and puts in the desired character(s) in its stead. We reopen the file and place the newly revised contents in it. Finally, the file is closed.

You might ask why we have to open the file twice. After all, the Help documentation for the Open statement says:

In Binary, Input, and Random modes, you can open a file using a different file number without first closing the file. In Append and Output modes, you must close a file before opening it with a different file number.

Initially we're using Input mode, so we should be able to open the file with a new file number. I tried keeping the file open (i.e. not using the Close statement), using a different file number and reopening the file for Output, but I kept getting the "File In Use" error. So either I did it wrong, the Output parameter caused the error, or the documentation is wrong.

Sample usage

Sub TestFindAndReplace()

  On Error GoTo ErrorHandler

  Call FindAndReplace("C:\testfile.txt", "text to find", "text to replace")

ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Related Articles:

• May 14, 2010 -- Remove and replace special characters in VBA
• January 29, 2009 -- Excel Tutorial Series – Text Functions
• December 19, 2008 -- SearchBox Week, Day 5