0

I have an XML file with about 30000 characters I want to put into a String variable. In that variable, I want to replace certain characters using Replace function.

In the below code neither Replace nor Instr detect the characters I'm looking for. When I shorten the XML to around 3000 characters, the code works.

Strings can be up to 2 billion characters long, so what's going on here?

The only thing that comes to mind is that str is a String and the Instr/Replace require a String Expression as an argument. Is there a limit on the length of String Expression these functions can handle?

Dim str As String
Dim arrInvalidChars() As String
Dim arrValidChars() As String
Dim i As Integer

arrInvalidChars = Split(Expression:="ä, ü, ö, ß, é, ç", Delimiter:=", ")
arrValidChars = Split(Expression:="ae, ue, oe, ss, e, c", Delimiter:=", ")

For i = LBound(arrInvalidChars) To UBound(arrInvalidChars)
    On Error Resume Next
    Debug.Print InStr(1, str, arrInvalidChars(i), vbTextCompare)
    str = Replace(str, arrInvalidChars(i), arrValidChars(i), , , vbTextCompare)
    On Error GoTo 0
Next i
Community
  • 1
  • 1
piotr
  • 1
  • 3
    Why are you using `Instr` and `Replace` on the entire XML file? Use [an XML library](https://stackoverflow.com/q/11305/4088852) and only work with the specific nodes you need to run your replacement code on. – Comintern Nov 03 '18 at 17:04
  • The reason is I've been using loadxml method to load my XML file; that method takes a Unicode BSTR that is encoded in UCS-2 or UTF-16 only. My XML file contains ISO8859-1 characters which get messed up on load, so I though it may be easier to just remove then using the Replace method. I bid of a "duck tape" solution, but seemed perfectly valid and I don't understand why it's not working. Anyway, will switch to load method to load my XML - then it can be encoded in ISO8859-1. Thanks! – piotr Nov 04 '18 at 16:56

0 Answers0