CODEWITHSUNDEEP
vbaexcel
GohanP
GohanP

Reputation: 549

Replace function, how to replace not all founded strings?

I create XML file from excel file. I save a string from one cell in a variable sObserved. When in that string I have a character "&" it should be replaced to "&"and when I have a character ";" it should be replaced to "&#59;". I use for this function Replace, this is my code:

        sObserved = Replace(sObserved, "&", "&")
        sObserved = Replace(sObserved, ";", "&#59;")

But this can't work good, because when it will replace "&" on "&", the ";" will appear and next operation will change it to "&amp&#59;" If I'll change an order it also will be wrong, because then sign "&" in "&#59" will be replaced.

Is there any possibility to replace it just like I wanted? I will be gratefull for any ideas because I stuck here.

Upvotes: 4

Views: 547

Answers (3)

Leo Chapiro
Leo Chapiro

Reputation: 13979

Try this:

sObserved = Replace(sObserved, "&", "&")
sObserved = Replace(sObserved, ";", "&#59;")
sObserved = Replace(sObserved, "&amp&#59;", "&")

Upvotes: 2

Madivad
Madivad

Reputation: 3337

What you're after is a function to encode the output to be HTML friendly: htmlEncode. There are several scripts/functions around the web people have wrote, here is one:

' Encode an string so that it can be displayed correctly
' inside the browser.
'
' Same effect as the Server.HTMLEncode method in ASP

Function HTMLEncode(ByVal Text As String) As String
Dim i As Integer
Dim acode As Integer
Dim repl As String

HTMLEncode = Text

For i = Len(HTMLEncode) To 1 Step -1
    acode = Asc(Mid$(HTMLEncode, i, 1))
    Select Case acode
        Case 32
            repl = " "
        Case 34
            repl = """
        Case 38
            repl = "&"
        Case 60
            repl = "<"
        Case 62
            repl = ">"
        Case 32 To 127
            ' don't touch alphanumeric chars
        Case Else
            repl = "&#" & CStr(acode) & ";"
    End Select
    If Len(repl) Then
        HTMLEncode = Left$(HTMLEncode, i - 1) & repl & Mid$(HTMLEncode, _
            i + 1)
        repl = ""
    End If
Next
End Function

ref: http://www.devx.com/vb2themax/Tip/19162

There is another here: http://www.codeproject.com/Articles/33064/VBScript-HTML-Encode but that seems to encode everything that's not a character or letter. The regex in this one could probably be expanded a little better to include those that are ok for HTML.

Function HTMLEncode(ByVal sVal)

sReturn = ""

If ((TypeName(sVal)="String") And (Not IsNull(sVal)) And (sVal<>"")) Then

    For i = 1 To Len(sVal)

        ch = Mid(sVal, i, 1)

        Set oRE = New RegExp : oRE.Pattern = "[ a-zA-Z0-9]"

        If (Not oRE.Test(ch)) Then
            ch = "&#" & Asc(ch) & ";"
        End If

        sReturn = sReturn & ch

        Set oRE = Nothing
    Next
End If

HTMLEncode = sReturn
End Function

Upvotes: 1

Fred
Fred

Reputation: 5808

This function is from http://www.codeproject.com/Articles/33064/VBScript-HTML-Encode. You may need to tweek it but it does a character by character check.

Function HTMLEncode(ByVal sVal)

    sReturn = ""

    If ((TypeName(sVal)="String") And (Not IsNull(sVal)) And (sVal<>"")) Then

        For i = 1 To Len(sVal)

            ch = Mid(sVal, i, 1)

            Set oRE = New RegExp : oRE.Pattern = "[ a-zA-Z0-9]"

            If (Not oRE.Test(ch)) Then
                ch = "&#" & Asc(ch) & ";"
            End If

            sReturn = sReturn & ch

            Set oRE = Nothing
        Next
    End If

    HTMLEncode = sReturn
End Function

Upvotes: 1

Related Questions