excel - VBA 从 Web 服务器导入 UTF-8 CSV 文件

Question

我在网络服务器上存储了一个 UTF-8 CSV 文件。当我下载文件时，将其放在硬盘上，然后使用此宏(从宏记录器)将其导入到 Excel 工作表中:

Sub Macro2()
Workbooks.OpenText Filename:= _
    "C:/myFile.csv", Origin _
    :=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
    , Comma:=True, Space:=False, Other:=False
End Sub

所有字符(越南语字符)均正确显示。

当我尝试相同的宏，但不是提供文件的本地地址(“C:/myFile.csv”)时，而是传递文件的 URL(“http://myserver.com/myFile.csv”)，CSV 会正确导入到我的文件中。 Excel 工作表，但越南语字符不再正确显示。

我也尝试过使用“数据”选项卡，但 Excel 似乎忽略了编码:

With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;C:/myFile.csv" _
                , Destination:=Range("$A$1"))
                .Name = "myFile.csv"
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 65001
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "~"
                .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
       End With

示例数据:„; A; A; 1/4； A‰; ™,™

Excel 错误地读取为:?; A，; ”; 1/4； ”; ，，；

Answer 1

如果您自己下载csv文件时字符显示正确，我会将过程分为两个阶段:

下载

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"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile local, 2 
    oStream.Close
End If

End Sub

加载 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:=False
End Sub

注意: Local 参数是这里的关键，它使 VBA 使用 Excel 的本地配置(越南语)，这是通过默认设置为False。

把它们放在一起

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