VBA Search for String and Replace

Question

Let's say we have a text file called text.txt. Within this text file are two lines of interest.

src="somethingrandom1111.png"
src="morerandomnumberstuffthisnamewillvary.png"

What I want to do is search the entire document for src=" and then replace everything that comes after that with some other string until another " is found. So,

src="somethingrandom1111.png"
src="morerandomnumberstuffthisnamewillvary.png"

Would become

src="thiswasreplace.gif"
src="thistoo!.jpg"

This is a part of my code:

TempFile = Environ$("temp") & "\" & "index.htm"
With TempWB.PublishObjects.Add( _
     SourceType:=xlSourceRange, _
     Filename:=TempFile, _
     Sheet:=TempWB.Sheets(1).Name, _
     Source:=TempWB.Sheets(1).UsedRange.Address, _
     HtmlType:=xlHtmlStatic)
    .Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, 0)
ts = Replace(ts, 'src="', something)
ts.Close

Is every replace you are doing going to be unique? Your "Would become" section suggests this. You could loop through every line in the file looking for src=" and then use methods like Mid to get the filename in order to replace it. — Matt
– Matt, Commented Aug 19, 2014 at 17:31

Ron Rosenfeld · Accepted Answer · 2014-08-19 17:54:36Z

One or two issues.
- Replace acts on a string, not a textstream object as you are doing in your routine.
- Also, it appears as if you want to search for multiple strings, and have different replacement values for each string.

The following code shows one approach to this. I used early binding and the VBscript regular expression engine to do the replacements. I stored the "find string / replace string" in a hard coded array for ease of access. And I used the OpenTextFile method rather than GetFile, but for no particular reason.

You should be able to adapt to your requirements:

Option Explicit
Option Base 0
Sub FindReplaceInFile()
    Const sPath = "C:\users\ron\desktop\"
    Const sFN As String = "Text.txt"
    Dim FSO As FileSystemObject
    Dim TS As TextStream
    Dim S As String
    Dim arrFindReplace() As Variant
    Dim RE As RegExp
    Dim I As Long
    Dim sPat As String, sRepl As String

arrFindReplace = Array(Array("somethingrandom1111.png", "thiswasreplace.gif"), _
        Array("morerandomnumberstuffthisnamewillvary.png", "thistoo!.jpg"))

Set FSO = New FileSystemObject
Set TS = FSO.OpenTextFile(sPath & sFN, ForReading)
S = TS.ReadAll
TS.Close

Set RE = New RegExp
With RE
    .Global = True
    .IgnoreCase = True
End With

For I = 0 To UBound(arrFindReplace)
    sPat = "(src="")" & arrFindReplace(I)(0) & """"
    sRepl = "$1""" & arrFindReplace(I)(1) & """"
    With RE
        .Pattern = sPat
        S = .Replace(S, sRepl)
    End With
Next I

Set TS = FSO.OpenTextFile(sPath & sFN, ForWriting)
TS.Write S
TS.Close

End Sub

Jagadish Dabbiru · Accepted Answer · 2014-08-19 20:04:01Z

You need to use regular expressions for this. To activate regular expression engine in Excel Visual Studio. Follow the steps

Start Microsoft Visual Basic Studio.
On the File menu, click New Project.
Click Standard Exe in the New Project dialog box, and then click OK.
On the Project menu, click References.
Double-click Microsoft VBScript Regular Expressions 5.5, and then click OK.

Here is the Code

Sub RegEx_Macro()
TempFile = Environ$("temp") & "\" & "index.htm"
With TempWB.PublishObjects.Add( _
     SourceType:=xlSourceRange, _
     Filename:=TempFile, _
     Sheet:=TempWB.Sheets(1).Name, _
     Source:=TempWB.Sheets(1).UsedRange.Address, _
     HtmlType:=xlHtmlStatic)
    .Publish (True)
End With

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile(TempFile, 1)
strdata = ts.ReadAll

Dim objRegExp As RegExp
   Dim objMatch As Match
   Dim colMatches   As MatchCollection
   Dim RetStr As String
   ' Create a regular expression object.
   Set objRegExp = New RegExp

   'Set the pattern by using the Pattern property.
   objRegExp.Pattern = "src="".*?"""

   ' Set Case Insensitivity
   objRegExp.IgnoreCase = True

   'Set global applicability.

   objRegExp.Global = True
   Set colMatches = objRegExp.Execute(strdata)  ' Execute search.

    For Each objMatch In colMatches ' Iterate Matches collection.
            strtoreplace = InputBox("Enter string to be replaced" , "Enter Replacement String For"& VbCrLf & objMatch.Value ) 'Give string to be replaced in inputbox
            strdata = Replace(strdata, objMatch.Value, strtoreplace)
    Next
    'ts.Close
    Set ts = fso.OpenTextFile(TempFile, 2)
    ts.Write strdata
    ts.Close

End Sub

Hope This Helps !!

Collectives™ on Stack Overflow

VBA Search for String and Replace

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related