Hello everyone,

I am coding a macro that make use of WinHttp.WinHttpRequest.5.1 to make request to the freegeoip.net API. Everything is working great except for one thing, the response that I get for my web request has accented character and they get messed up once excel write the value in my worksheet. The response come in the form of a CSV and I came to realize that Excel has issue with CSV and encoding. Most solution online mention re saving the CSV in the proper encoding but this is not an option as it is supposed to be fully automated script

The code look that way:

    IPlist = Range(Cells(Selection.Row, 1).Address).value
    WHOIS = Range(Cells(Selection.Row, 2).Address).Formula
    URL = "https://freegeoip.net/csv/" & IPlist
    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "GET", URL
    'MyRequest.Open "GET", "https://freegeoip.net/csv/"
    
    ' Envoi la requête à l'API.
    MyRequest.send
    
    
    Cells(ActiveCell.Row, 2).value = MyRequest.responseText
    
    MsgBox MyRequest.responseText
         
         ActiveCell.Offset(1, 0).Select

    DoEvents

    If i = lastrow Then Unload ufProgress
Next i

     Loop
The response sent by the API would normally be like this:

''82.238.158.02,FR,France,IDF,ÌŽle-de-France,Poissy,78300,Europe/Paris,48.9290,2.0495,0''

But once excel put the response value in a cell, ti show up like this:

''82.238.158.02,FR,France,IDF,ÃŽle-de-France,Poissy,78300,Europe/Paris,48.9290,2.0495,0''

What do you guys think would be the best way to deal with this? The API also support sending response in XML but it is still the same issue.

I thought of adding to the macro a section that would take care of replacing the wrong character with the proper one or the closest unaccented character as I was able to find a character table with everything I needed at http://www.i18nqa.com/debug/utf8-debug.html . The issue with this solution is that I have character like "Ã" for ''Ì'' and "é" for ''é'', but if I use script to replace character, it doesn't make the distinction between "Ã" and other instance that start with the à but has another character following like "é" and it end up replacing all the à by Ì leaving the string with the wrong letter.

Thank you in advance for your insights!