VBA importing UTF-8 CSV file from a web server

If the characters are displayed correctly when you download the csv file yourself, I'd divide the process to 2 stages:

Downloading

Sub DownloadFile(ByVal url As String, ByVal local As String)Dim WinHttpReq As ObjectSet WinHttpReq = CreateObject("Microsoft.XMLHTTP")WinHttpReq.Open "GET", url, False, "username", "password"WinHttpReq.sendmyURL = WinHttpReq.responseBodyIf WinHttpReq.Status = 200 Then    Set oStream = CreateObject("ADODB.Stream")    oStream.Open    oStream.Type = 1    oStream.Write WinHttpReq.responseBody    oStream.SaveToFile local, 2     oStream.CloseEnd IfEnd Sub

Loading CSV

Sub OpenCsv(ByVal csvfile As String)Workbooks.OpenText Filename:= _ csvfile,Local:=True,StartRow:=1, DataType:=xlDelimited, TextQualifier:= _xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _, Comma:=True, Space:=False, Other:=FalseEnd Sub

Note That: The Local parameter is the key here,it makes VBA use your excel's local configuration (vietnamese), which is by default set to False.

Putting it all together

Sub DownloadAndLoad  DownloadFile "http://myserver.com/myFile.csv","C:\myFile.csv"  OpenCsv "C:\myFile.csv"End Sub

I have been looking at a similar problem where we import utf-8 encoded csv files in to a worksheet. I am not pulling the data from a web server but this might help.

My solution is to read the utf-8 file to a local variable then insert it into a sheet. I tried saving the data to a temp file with ansi encoding but doing this caused all the characters to lose their accents.

Function ReadUTF8CSVToSheet(file As String)    Dim ws As Worksheet    Dim strText As String    ' read utf-8 file to strText variable   With CreateObject("ADODB.Stream")        .Open        .Type = 1  ' Private Const adTypeBinary = 1        .LoadFromFile file        .Type = 2  ' Private Const adTypeText = 2        .Charset = "utf-8"        strText = .ReadText(-1)  ' Private Const adReadAll = -1    End With    ' parse strText data to a sheet    Set ws = Sheets.Add()    intRow = 1    For Each strLine In Split(strText, chr(10))        If strLine <> "" Then            With ws                .Cells(intRow, 1) = strLine                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _                    Semicolon:=False, Comma:=True, Space:=False, Other:=False            End With            intRow = intRow + 1        End If    Next strLine    ReadUTF8CSVToSheet = ws.NameEnd Function' to runstrSheetName = ReadUTF8CSVToSheet("C:\temp\utf8file.csv")

IMO, there seems to be a bug/conflict in Excel when opening UTF-8/UTF-8-BOM files using the recorded macro code, specifically when the Origin parameter is set to 65001 which is supposed be UTF-8.

I have found two workarounds to this issue:

1. Remove the Origin parameter from the function call and see if the file loads properly Workbooks.OpenText Filename:="C:\file.csv".

   MSDN says:

   If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

   I would think that as soon as you link the file with Excel, it should try to read the header of the file and select the correct Country Code automatically (well, assuming the header is not missing).

2. I have tried different Country Codes and found that in my specific scenario setting Origin:=1252 (1252 - windows-1252 - ANSI Latin 1; Western European (Windows)) loads the file in Excel just fine.