Insert Regex in my code

[elmnas]

Hello guys I found this code on the forum

Could someone help me to add a regex expression


instead of
"change me" and "Changed!"
I want to search for "><"

and replace with ">\n\r<"

[COLOR=Navy]Sub[/COLOR] CleanupXML()
[COLOR=Navy]Dim[/COLOR] FSO [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR] [COLOR=SeaGreen]'//FileSystemObject[/COLOR]
[COLOR=Navy]Dim[/COLOR] ts(1) [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR] [COLOR=SeaGreen]'//TextStream[/COLOR]
[COLOR=Navy]Dim[/COLOR] s [COLOR=Navy]As[/COLOR] String, t [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] FileContents [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]

[COLOR=SeaGreen]'---------------------------------------------------------[/COLOR]
[COLOR=Navy]Const[/COLOR] SEARCH_FOR [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR] = "Change me" [COLOR=SeaGreen]'CaSe Sensitive![/COLOR]
[COLOR=Navy]Const[/COLOR] REPLACE_WITH [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR] = "Changed!" [COLOR=SeaGreen]'CaSe Sensitive![/COLOR]
[COLOR=SeaGreen]'---------------------------------------------------------[/COLOR]

[COLOR=Navy]On[/COLOR] [COLOR=Navy]Error[/COLOR] [COLOR=Navy]GoTo[/COLOR] ErrHandler:
s = Application.GetOpenFilename()
[COLOR=Navy]If[/COLOR] s <> "False" [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR=Navy]If[/COLOR] FSO.FileExists(s) [COLOR=Navy]Then[/COLOR]
    
        [COLOR=SeaGreen]'//Get File Contents[/COLOR]
        t = FSO.GetParentFolderName(s) & "\" & Replace(FSO.GetTempName(), ".tmp", ".xml")
        [COLOR=Navy]Name[/COLOR] s [COLOR=Navy]As[/COLOR] t
        [COLOR=Navy]Set[/COLOR] ts(0) = FSO.OpenTextFile(t, 1, False, -2) [COLOR=SeaGreen]'//For reading, use default encoding[/COLOR]
        FileContents = ts(0).ReadAll
        ts(0).Close
        [COLOR=Navy]Set[/COLOR] ts(0) = [COLOR=Navy]Nothing[/COLOR]
        
        [COLOR=SeaGreen]'//Make replacement[/COLOR]
        FileContents = Replace(FileContents, SEARCH_FOR, REPLACE_WITH)
        
        [COLOR=SeaGreen]'//Write new file contents[/COLOR]
        [COLOR=Navy]Set[/COLOR] ts(1) = FSO.OpenTextFile(s, 2, True, -2) [COLOR=SeaGreen]'//For writing, use default encoding[/COLOR]
        ts(1).Write (FileContents)
        ts(1).Close
        [COLOR=Navy]Set[/COLOR] ts(1) = [COLOR=Navy]Nothing[/COLOR]
        
        [COLOR=SeaGreen]'//Delete Temp file if all actions succeeded[/COLOR]
        FSO.DeleteFile (t)
    
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]

[COLOR=SeaGreen]'//Check that all files are closed[/COLOR]
My_Exit:
[COLOR=Navy]If[/COLOR] [COLOR=Navy]Not[/COLOR] ts(0) [COLOR=Navy]Is[/COLOR] [COLOR=Navy]Nothing[/COLOR] [COLOR=Navy]Then[/COLOR]
    ts(0).Close
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]If[/COLOR] [COLOR=Navy]Not[/COLOR] ts(1) [COLOR=Navy]Is[/COLOR] [COLOR=Navy]Nothing[/COLOR] [COLOR=Navy]Then[/COLOR]
    ts(1).Close
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]Set[/COLOR] FSO = [COLOR=Navy]Nothing[/COLOR]
[COLOR=Navy]Exit[/COLOR] [COLOR=Navy]Sub[/COLOR]

ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
[COLOR=Navy]Resume[/COLOR] My_Exit
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

 

[pgc01]

Hi

Why would you want to add a regex? Why not use the code you have just with the new strings?


On another note:

Are you sure "\n\r" is the right sequence? it's usually the other way around.

 

[elmnas]

You see my function loops through a XML file
the problem with my xml its, all text are on one and same line.
that is reason I want to make a script that loop through the file looking for with regex ><
and replace with >\n\r<
to get all tags + text on new line.

 

[pgc01]

Hi

Yes, I understood that, my question was: why would you want to use a regex? why not just use the Replace() function, like you already have in your code?

 

[elmnas]

Hi

Yes, I understood that, my question was: why would you want to use a regex? why not just use the Replace() function, like you already have in your code?

well that is ok so far the result is the same

 

[pgc01]

Run this code and then adapt it:

Sub test()
Dim s As String

'---------------------------------------------------------
Const SEARCH_FOR As String = "><"
Const REPLACE_WITH As String = ">" & vbCrLf & "<"
'----------------------------------------------

s = "<123><456><789>"
MsgBox s

s = Replace(s, SEARCH_FOR, REPLACE_WITH)
MsgBox s

End Sub

 

[elmnas]

Thank you Pgc01!!

Work as a charm!

 

[pgc01]

You're welcome. Thanks for the feedback.