I am having an issue when retrieving an xml request, I get the error
Run-time error '-2147023783(80070459)':
No mapping for the Unicode character exists in the target multi-byte code page.
The data being retrieved has the ยป character within it. Now I know it has something to do with the fact that it can't be correctly mapped due to differences in bit allocation however there must be a way to strip it out or convert it.
I have tried using VBA.Strings.StrConv() however it still gives the same error message
Function getData(XMLString As String) As String
Dim sURL, sResult, sHeaderResult, strPostData, sXmlDocument, resultsformat, sepChar, wholeLine As String
Dim subStrCount, i, colNdx, pos, nextPos, saveColNdx, maxRows, maxCols As Integer
ReDim subStr(0) As String
Dim oHttp, myDom, MyData As Object
Dim rowNdx As Long
Dim tempVal As Variant
Dim myRange As Range
'URL to open
sURL = "http://website.com"
'Create Post Data
With Worksheets("Control")
strPostData = "action=" & .Range("Action").Value & "&baseviewid=" & .Range("BaseViewID").Value & "&key=" & .Range("Key").Value & "&resultsformat=" & .Range("Format").Value & "&userid=" & .Range("UserID").Value
End With
' Create an XMLDocument object and add some error trapping
On Error Resume Next
Set myDom = CreateObject("MSXML2.DOMDocument")
If Err.Number <> 0 Then
MsgBox "Error 0 has occured while creating a MSXML2.DOMDocument object"
End If
'Load XML text
myDom.LoadXML XMLString
' Create an XMLHTTP object and add some error trapping
On Error Resume Next
Set oHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
If Err.Number <> 0 Then
Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
MsgBox "Error 0 has occured while creating a MSXML.XMLHTTPRequest object"
End If
On Error GoTo 0
If oHttp Is Nothing Then
MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object"
Exit Function
End If
'Join all parameters in one string
strPostData = strPostData & "&xmlquery=" & myDom.XML
'SEND REQUEST WITH ALL THE PARAMETERS
With oHttp
.SetProxy 2, "http://proxy.something.com:8443"
.Open "POST", sURL, False
.SetTimeouts 120000, 120000, 120000, 120000
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.send (strPostData)
sHeaderResult = .GetAllResponseHeaders
sResult = responseText
'sResult = VBA.Strings.StrConv(.responseText, vbFromUnicode)
'sResult = VBA.Strings.StrConv(.responseText, vbUnicode)
End With
getData = sResult
End Function
Occasionally lots of Chinese style characters will appear instead of the error. If I use data that does not have any unusual characters, the query works as expected.
Any workaround would be greatly appreciated!