Replace 2 double quotes with 1 double quote with VBA

I have a TNT XML request that I am sending from a Microsoft Access 2003 database to their XML API server to retrieve a label. The response that is sent back to me must be translated with XSLT. The problem is that the response that I receive contains two double quotes for each quoted phrase instead of one. I need to modify the HTML so that it displays normally, with one double quote on either side.

Just to give an example of what this looks like:

<META http-equiv=""Content-Type"" content=""text/html"">
<title>TNT Label</title><script type=""text/javascript"">

                        if (firstPagePrinted) {
                            document.writeln('<div class=""pagebreak"">');
                            document.writeln('<font size=""1"" color=""#FFFFFF"">.</font>');

I have tried using replace() many times in all kinds of variations. I've used the ASCII Chr(34) representation of ", as well as Unicode CHrW(&H22). These are just a few examples of my unsuccessful attempts to solve this problem:

Replace(strResult, Chr(34), ChrW(&H22))
Replace(strResult, """", ChrW(&H22))
Replace(strResult, """", "")

This is the code I am using to transform and save the XML document:

Dim strResponse, strDisplayString, strSQL, desktopPath As String
Dim sTNTXML, sResponseXML, nodeList, strResult

Dim objRequest As New MSXML2.ServerXMLHTTP
objRequest.Open "POST", "https://iConnection.tnt.com/ShipperGate2.asp", False, "", ""
objRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
objRequest.setRequestHeader "Accept", "*/*"
objRequest.setRequestHeader "User-Agent", "ShipperGate_socket/1.0"
objRequest.setRequestHeader "Host", "iConnection.tnt.com"
objRequest.setRequestHeader "Content-Length", CStr(Len(sTNTXML)) + 7

DoCmd.Hourglass True
objRequest.send "xml_in=GET_LABEL:" & modTNTShipping.completeNum
sResponseXML = objRequest.responseText
DoCmd.Hourglass False

Dim mydoc As New MSXML2.DOMDocument60
mydoc.async = False
mydoc.loadXML (sResponseXML)

Dim stylesheet As New MSXML2.DOMDocument60
stylesheet.async = False
stylesheet.Load "http://iconnection.tnt.com:81/Shipper/NewStyleSheets/label.xsl"

Dim result As New MSXML2.DOMDocument60
result.async = False

strResult = mydoc.transformNode(stylesheet)

Dim strQuote As String
strResult = Replace(strResult, Chr(34), ChrW(&H22))

Open "C:\users\chris\desktop\TNTLabel.html" For Output As #1
Write #1, strResult
Close #1

If there is anything somebody could suggest to alleviate this issue (even if it doesn't use Replace()), I would greatly appreciate it.

Solution

Try this, maybe, using FileSystemObject to create the text file/html file:

Public Const strResult As String = "<META http-equiv=""Content-Type"" content=""text/html"">" '"document.writeln('<div class=""pagebreak"">');"
Sub Test()
Dim sFile As String
Dim fso As Object 'Scripting.FileSystemObject

sFile = "C:\users\" & Environ("username") & "\desktop\TNTLabel.html"

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CreateTextFile(Filename:=sFile, Overwrite:=True).Write strResult

Set fso = Nothing
End Sub

This creates the following text file, note that there are no double-quotes and I did not use any Replace or other string functions to remove them from the strResult:

screenshot

Alternatively, when I used freefile method to write the file, it preserves it as string literal, where the double-quotes are required as an escape character. I am still not sure that you should be "replacing" or removing these, since this is an HTML file which includes script/javascript, so you have string script inside a string HTML. In my very limited web/HTML experience, this might actually be necessary to properly escape the characters, or as JLRishe suggests, perhaps this is correct for XSLT.

Open "C:\users\chris\desktop\TNTLabel.html" For Output As #1
Write #1, strResult
Close #1