我想将 CSV 文件合并到不同工作表中的 Excel 工作簿中。
我希望每个文件都放在一个工作表中,并以 CSV 文件名作为工作表名称。
答案1
创建宏:
Sub ListAllFile3()
Application.DisplayAlerts = False
Dim strFile As String
strFile = Dir("C:\CSVFile\*.csv", vbNormal)
Do While Len(strFile) > 0
Set ws = Worksheets.Add
ws.Name = strFile
With Worksheets(strFile).QueryTables.Add(Connection:="TEXT;C:\CSVFile\" & strFile, Destination:=Range"$A$1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
strFile = Dir
Loop
Application.DisplayAlerts = True
End Sub