3

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!

Community
  • 1
  • 1
  • After googling around StrConv really seems to be the only solution... Don't know why it doesn't work for you. Have you ever tried to change the request header to something like "Content-Type", "text/html; charset=utf-8"? โ€“ Jan Rothkegel Aug 07 '14 at 18:25
  • Try the response on this question. Worked for me: http://stackoverflow.com/questions/7100229/xmlhttp-and-special-characters-eg-accents โ€“ variant Nov 24 '14 at 21:22
  • http://stackoverflow.com/questions/23810324/vba-convert-string-to-unicode worked for me โ€“ Michiel van der Blonk Mar 07 '17 at 16:21

0 Answers0