Character error when using MSXML2

levifunk

New Member
Joined
Mar 14, 2011
Messages
20
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:
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

New Member
Joined
Mar 14, 2011
Messages
20
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Changing the Charset in Firefox does work correctly. I assume you can't grab the info from another place inside the HTML?
 

levifunk

New Member
Joined
Mar 14, 2011
Messages
20
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?
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,998
One way is by using responseBody instead of responseText. responseBody is an array of bytes from which you can build a string, like this:
Code:
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

New Member
Joined
Mar 14, 2011
Messages
20
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)
 

Forum statistics

Threads
1,082,044
Messages
5,362,855
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top