以下是一种无需在 Excel 中打开即可从关闭的 CSV 中查找列数的方法。
我假设如下。
1)您正在打开一个逗号分隔的文件。如果没有,那么您将必须适当修改代码
2)CSV 中的第 1 行有标题 (任何列中至少有 1 个标题)
尝试这个(我测试了它,但如果您遇到任何错误,请告诉我们:)
Option Explicit
Const ExlCsv As String = "C:\test.csv"
Sub Sample()
Dim MyData As String, strData() As String, TempAr() As String
Dim ArCol() As Long, i As Long
'~~> Open the text file in one go
Open ExlCsv For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
'~~> Check for any empty headers and replace ",," by ","
Do While InStr(1, strData(0), ",,") > 0
strData(0) = Replace(strData(0), ",,", ",")
Loop
'~~> Split the headers to find the number of columns
TempAr() = Split(strData(0), ",")
'~~> Create our Array for TEXT
ReDim ArCol(1 To UBound(TempAr))
For i = 1 To UBound(TempAr)
ArCol(i) = 2
Next i
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & ExlCsv, Destination:=Range("$A$1") _
)
.Name = "Output"
.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 = ArCol
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
EDIT
或者,这是一个更简单的方法(想知道为什么我之前没有想到它......)
Option Explicit
Const ExlCsv As String = "C:\test.csv"
Sub Sample()
ActiveSheet.Cells.NumberFormat = "@"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & ExlCsv, Destination:=Range("$A$1") _
)
.Name = "Output"
.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
'<~~ This doesn't make any difference anymore
.TextFileColumnDataTypes = Array(2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub