VBA读取csv文件

一种方法：

调用Excel自带的查询功能  QueryTables.Add 方法读取csv

通过录制宏查看用法

Sub 宏2()

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\\test.csv", Destination:=Range("$A$1"))

        .CommandType = 0

        .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 = 1258  ' 文件编码

        .TextFileStartRow = 1

        .TextFileParseType = xlDelimited

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = False

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = True

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

    End With

End Sub

另一种方法：

通过调用Excel 应用打开一个csv文件并把数据复制到指定工作簿

Sub file()

Dim file_path As String

Dim wb, wb0 As Workbook

Dim excelApp As Excel.Application

file_path = "F:\test.csv"

Set wb0 = ThisWorkbook

Set wb = GetObject(file_path) ' 打开文件

wb.Sheets(1).UsedRange.Copy  ' 复制数据 因为csv打开后只有一个sheet

wb0.Sheets(1).Range("A1").Select

wb0.Sheets(1).Paste  ' 粘贴

End Sub