vba - 使用具有精确数据格式的 VBA 从 Web 将 CSV 文件导入到 Excel 工作表

Question

我已经尝试使用以下代码将 csv 文件导入到 Excel 工作表(礼貌:http://investexcel.net/download-finviz-data/)，它工作正常。导入数据后，数据类型不正确。请看截图。



导入到 excel 后，零前缀已删除第二列。 QueryTables.Add(Connection:="URL;"... 是否有类似 '.TextFileColumnDataTypes' 的属性？

Sub GetWebCsvData()

Dim str As String
 Dim myarray() As Variant
'Delete existing data
Sheets("Data").Activate 'Name of sheet the data will be downloaded into. Change as required.
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents


str = "http://somedomain.com/filename.csv"
QueryQuote:
            With Sheets("Data").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("Data").Range("a1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = false
                .Refresh BackgroundQuery:=False
                .SaveData = True

            End With

Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)

Sheets("Data").Columns("A:B").ColumnWidth = 12
Range("A1").Select

End Sub

Answer 1

这工作得很好:

Option Explicit

Sub TestMe()

    Dim filePath As String: filePath = "C:\\file.csv"        
    Cells.Delete

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & filePath, _
                                                      Destination:=Range("A1"))
        .Name = "test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False            
    End With        
End Sub

正确的属性是 .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1) . 2在数组中代表文本:

Tribute to these guys .

MSDN - QueryTable.TextFileColumnDataTypes Excel