Replace special characters with escape sequences

T

Tea

Member
Local time
Today, 01:02
Joined
Oct 30, 2020
Messages
51
I'm trying to find a way to replace special characters (<, >, &, ', ") in my description field with the escape sequences, so I can use the newString in a string, that is later on use to generate a xml file.

Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
Next

The problem is when I try to put the line with ampersand into the code, it gives a weird string like this:
Code: 
<ItemDesc>Test &#38;#38;#38;lt; &#38;#38;#38;gt; &#38;#38;#38;#39; &#38;#38;#38;</ItemDesc>

when it should look like this.
Code: 
<ItemDesc>Test < > & </ItemDesc>

With the character <, > and ' it works prefectly fine.

Is there a way to resolve this matter with a different approach?
 
Last edited:
why do you need to Replace those characters when creating an XML?
have you tried without converting them?
 
Yes I did. The xml parser throws an exception and the xml file is created without the CDATA tags. So it's just a one line string without tags
 
Sub the & first?, before you put any of the replacement characters with & there?
 
what i can see is in your code is that you are Replacing it with "&...(etc).
but later on those "&..(etc) (replacement) will be replaced Again, sample:

< will be replaced with "&alt;"
..
..
on the later part of the code this will again replaced:

"&alt;" ==> (replace the & with "&#38;") ==> &#38;alt;

*** therefore put the last part of your code to the first.

this:
Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
Next

will become:
Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
Next
 
arnelgp said:
what i can see is in your code is that you are Replacing it with "&...(etc).
but later on those "&..(etc) (replacement) will be replaced Again, sample:

< will be replaced with "&alt;"
..
..
on the later part of the code this will again replaced:

"&alt;" ==> (replace the & with "&#38;") ==> &#38;alt;

*** therefore put the last part of your code to the first.

this:
Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
Next

will become:
Code: 
Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
Next
Click to expand...
I have tried that, but it's still not working. Has the same result.
Gasman said:
Sub the & first?, before you put any of the replacement characters with & there?
Click to expand...
What do you mean?
 
do you have your Textfield formatted as Richtext?
on second thought, i don't know what we are doing here.
XML is different from HTM/HTML.
 
arnelgp said:
do you have your Textfield formatted as Richtext?
on second thought, i don't know what we are doing here.
XML is different from HTM/HTML.
Click to expand...
I don't thnk it's necessary to have Rich text on this field as it's not for HTML purposes. I'm generating an XML through a form. But if a field contains those special characters it does not create the xml tags becuase XML can'T read these symbols without escaping them.
 
remove the For...Next Loop:

myString = CleanString(rs![desc])


in separate Module:

Code: 
Public Function CleanString(ByVal var As Variant)
'Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim newString As String, char As Variant
newString = var & ""
If Len(newString) = 0 Then Exit Function
'For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
'Next
CleanString = newString
End Function

OR on your Original Code:

Code: 
'Remove this, no need
'Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
'Remove For Each Loop...
'For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
'Next
 
Yes, but you are looping for each character, so after the first loop, you have & and we can go on an on. :(
You need to just replace them once.
I'd probably have an array of what to look for and what to replace, else use a case statement inside the loop.
 
  • Like
Reactions: Tea
arnelgp said:
remove the For...Next Loop:

myString = CleanString(rs![desc])


in separate Module:

Code: 
Public Function CleanString(ByVal var As Variant)
'Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim newString As String, char As Variant
newString = var & ""
If Len(newString) = 0 Then Exit Function
'For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
'Next
CleanString = newString
End Function

OR on your Original Code:

Code: 
'Remove this, no need
'Const SpecialCharacters As String = "<,>,',&"  'modify as needed
Dim myString As String
Dim newString As String
Dim char As Variant
myString = rs![desc]
newString = myString
'Remove For Each Loop...
'For Each char In Split(SpecialCharacters, ",")
    newString = Replace(newString, "&", "&#38;") '#ampersand line, tried with #amp; also
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
'Next
Click to expand...
I have remove the for each altogether, it works now.
 
Gasman said:
Yes, but you are looping for each character, so after the first loop, you have & and we can go on an on. :(
You need to just replace them once.
I'd probably have an array of what to look for and what to replace, else use a case statement inside the loop.
Click to expand...
I have removed the for each loop, now it works fine. Made kinda sense, when the escape sequence has it's own &.
 
Gasman said:
But do you understand why it was not working?
Click to expand...
Is there a different approach where the code would look cleaner? Is there a function that replaces especially these symbols? I would like to apply this to another field also, but it would mean I would have to repeat the same problem. If there was another & in the second string for the field [notes].

Code: 
Dim myString As String
Dim newString As String
Dim myString2 As String
Dim newString2 As String
myString = rs![desc]
newString = myString
    newString = Replace(newString, "&", "&amp;")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, Chr(34), "&#34;")
myString2 = rs![notes]
newString2 = myString2
    newString2 = Replace(newString2, "&", "&amp;")
    newString2 = Replace(newString2, "<", "&lt;")
    newString2 = Replace(newString2, ">", "&gt;")
    newString2 = Replace(newString2, "'", "&#39;")
    newString2 = Replace(newString2, Chr(34), "&#34;")
 
Tea said:
I have removed the for each loop, now it works fine. Made kinda sense, when the escape sequence has it's own &.
Click to expand...
Only partially, as you were looping for each character.
If you kept the order the same then you could use the char in the loop, but then you need to know what to replace it with, hence my choice of arrays.
 
Tea said:
Is there a different approach where the code would look cleaner? Is there a function that replaces especially these symbols? I would like to apply this to another field also, but it would mean I would have to repeat the same problem. If there was another & in the second string for the field [notes].

Code: 
Dim myString As String
Dim newString As String
Dim myString2 As String
Dim newString2 As String
myString = rs![desc]
newString = myString
    newString = Replace(newString, "&", "&amp;")
    newString = Replace(newString, "<", "&lt;")
    newString = Replace(newString, ">", "&gt;")
    newString = Replace(newString, "'", "&#39;")
    newString = Replace(newString, Chr(34), "&#34;")
myString2 = rs![notes]
newString2 = myString2
    newString2 = Replace(newString2, "&", "&amp;")
    newString2 = Replace(newString2, "<", "&lt;")
    newString2 = Replace(newString2, ">", "&gt;")
    newString2 = Replace(newString2, "'", "&#39;")
    newString2 = Replace(newString2, Chr(34), "&#34;")
Click to expand...
Use arnel's CleanString function.
 
You must log in or register to reply here.

Similar threads

D
Solved Populate Form & Associated Table with Values from another Table
Replies
5
Views
311
Pat Hartman
P
C
Help to replace Application.FileSearch
Replies
2
Views
172
cheekybuddha
cheekybuddha
T
Hyperlinks
Replies
9
Views
593
isladogs
isladogs
N
Code does not support certain characters such as full-width symbols or Chinese
Replies
1
Views
519
ebs17
E
nimeshaanethmini
Need Help with VBA Code for Access Form Filtering
Replies
5
Views
339
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom