Converting weird characters and symbols into normal language in excel

Question

I am using the VBA code to extract information from a website into excel cells, and the numerical information is fine but I have a problem with text strings. I am mostly extracting information from Georgian websites, and the texts with the Georgian language are not properly displayed in excel, so I was wondering if there is any chance (code or something else) I could convert these symbols into proper language.

Sub GetData()

Dim request As Object
Dim response As String
Dim html As New HTMLDocument
Dim website As String
Dim price As Variant
Dim address As Variant
Dim x As Integer
Dim y As Range

x = 1
Do Until x = 9
    Set y = Worksheets(1).Range("A21:A200"). _
    Find(x, LookIn:=xlValues, lookat:=xlWhole)
    website = "https://www.myhome.ge/ka/pr/11247371/iyideba-Zveli-ashenebuli-bina-veraze-T.-WoveliZis-qucha"
    
' Create the object that will make the webpage request.
Set request = CreateObject("MSXML2.XMLHTTP")

' Where to go and how to go there.
request.Open "GET", website, False

' Get fresh data.
request.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"

' Send the request for the webpage.
request.send

' Get the webpage response data into a variable.
response = StrConv(request.responseBody, vbUnicode)

' Put the webpage into an html object.
html.body.innerHTML = response

' Get info from the specified element on the page.
address = html.getElementsByClassName("address").Item(0).innerText
price = html.getElementsByClassName("d-block convertable").Item(0).innerText
  
y.Offset(0, 1).Value = address
y.Offset(0, 5).Value = price

x = x + 1

Loop

End Sub

This is the code that I took from a youtube video (https://www.youtube.com/watch?v=IOzHacoP-u4) and slightly modified, and it works, I just have a problem with how excel displays the characters in text strings.

Please show a minimal reproducible example of your issue. Without code, it is very hard to help you. — Pᴇʜ
– Pᴇʜ, Commented Jul 12, 2021 at 8:16
@gugu - You don't need this line response = StrConv(request.responseBody, vbUnicode), Just assign responsetext to the innerhtml, html.body.innerHTML = request.responseText — Raymond Wu
– Raymond Wu, Commented Jul 12, 2021 at 9:32
Thank you very much @RaymondWu, your answer was extremely helpful and simple. I have been looking for this everywhere. Thank you again. — guga poladashvili
– guga poladashvili, Commented Jul 12, 2021 at 10:04
@gugu - I don't see anything in the code relating to ID (I only see address and price?) so I'm not exactly sure why your "code" is not working. But assuming I'm correct in reading the website, try Trim$(Replace(html.getElementsByClassName("id-container")(0).innerText, ":", vbNullString)). In your example this would return : 11247371 which I have applied Replace and Trim$ functions to process the result. — Raymond Wu
– Raymond Wu, Commented Jul 12, 2021 at 13:24

Raymond Wu · Accepted Answer · 2021-07-13 02:38:51Z

1

For your issue in the question

Remove this line response = StrConv(request.responseBody, vbUnicode) as it's not required.
Change html.body.innerHTML = response to html.body.innerHTML = request.responseText.

For your issue in comment

To retrieve the ID of the property, it can be retrieved from the class id-container, you will need to perform some string processing though to remove the extract :

propertyID = Trim$(Replace(html.getElementsByClassName("id-container")(0).innerText, ":", vbNullString))

Note: You should try to avoid declaring variable as Variant. innerText property returns a String datatype so you should declare address and price as String.

answered Jul 13, 2021 at 2:38

Raymond Wu

3,4272 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting weird characters and symbols into normal language in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related