Character error when using MSXML2

[levifunk]

I'm having a problem downloading data when the website uses foreign or special characters. In this case "Rübæus"

Here is my code:

Dim xmlhttp As New MSXML2.XMLHTTP50
Dim text1 As String
xmlhttp.Open "GET", "http://beeradvocate.com/beer/profile/1199/23474", False
xmlhttp.send
text1 = xmlhttp.responseText
Sheets("Sheet1").Cells(1, 1) = text1

If you look at what is written on Sheet 1, in the 4th line of text returned you'll see this:
title>Founders R￿- Founders Brewing Company - Grand Rapids, MI - BeerAdvocate /title>
<br>
<b>instead of:</b>
title>Founders R<b>übæus</b>- Founders Brewing Company - Grand Rapids, MI - BeerAdvocate /title>
<br>
It is obviously erroring when trying to write out the "ü"

Any thoughts?!

 

[Juan Pablo González]

Well, I believed it was because you weren't setting the Content-Type, but from what I tested, it didn't change anything... weird.

 

[levifunk]

Yea, I tried various Content-Type settings, but it didn't change anythings. Let me know if you come up with any other ideas.

 

[Juan Pablo González]

Changing the Charset in Firefox does work correctly. I assume you can't grab the info from another place inside the HTML?

 

[levifunk]

Correct. Any time it shows up, it just shows the "￿". Is there a way to expand the characters it recognizes? or is that what the Content-Type was suppose to do?

 

[levifunk]

or should text1 be set as something other than String?

 

[Juan Pablo González]

I saw it correctly in other instances, just not the title.

I used

xmlhttp.setRequestHeader "Content-Type", "text/html; Charset=iso-8859-1"

right above the "xmlhttp.send" line

And it should be a string.

 

[John_w]

One way is by using responseBody instead of responseText. responseBody is an array of bytes from which you can build a string, like this:

Sub XMLHttp_Get()

    Dim XMLHttp As New MSXML2.XMLHttp
    Dim body As String, i As Long
    Dim title As String
    
    XMLHttp.Open "GET", "http://beeradvocate.com/beer/profile/1199/23474", False
    XMLHttp.send
    
    body = ""
    For i = 1 To Len(XMLHttp.responseBody)
        body = body & Chr(XMLHttp.responseBody(i))
    Next
    
    title = Mid(body, InStr(LCase(body), Replace("< title >", " ", "")), 30)
    Debug.Print title
    
    Sheets("Sheet1").Cells(1, 1) = title
    
End Sub

Note - without the spaces in the "< title >" string the forum tries to render it as HTML and messes up my post, hence the use of the Replace function to remove these spaces. In your code, replace this function call with "< title >" but without any spaces.

 

[levifunk]

I saw it correctly in other instances, just not the title.

I used

xmlhttp.setRequestHeader "Content-Type", "text/html; Charset=iso-8859-1"

right above the "xmlhttp.send" line

And it should be a string.

The closest I see it is showing:
"R&uuml ;b&aelig ;us" (I added the spaces)

 

[Juan Pablo González]

Use the ResponseBody as John_W put above.