If the characters are displayed correctly when you download the csv file yourself, I'd divide the process to 2 stages:
Sub DownloadFile(ByVal url As String, ByVal local As String)
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", url, False, "username", "password"
myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Type = 1
oStream.SaveToFile local, 2
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:=False
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.
Hi Uri. Thanks for your reply. In my case, the Local:=True doesn't change a thing (is it cause my locals are UK? Changing my locale isn't really an option as I will be processing files of many languages...). For example, the csv file has a in it, when I use the Workbooks.Open the character is still loaded as . I have noticed that when I do this manually (not via macro) the csv is loaded properly. I have recorded a macro from the manual steps but the macro behaviour is different. I have read that csv files may be missing UTF-8 heading - are you familiar with headings at all?
Local should not be used or have an effect when reading UTF-8. @Meehow Yes, putting a UTF-8 BOM in the file fixes it.
Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).