vba 将 CSV 文件导入 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17899897/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import CSV files into Excel
提问by CatalinVG
I would like to ask for your help with the following:
我想在以下方面寻求您的帮助：
I have CSV files exported from a software application that I need imported in Excel to analyse the data. Daily are generated 40-50 CSVs. For now I do this manually through "Get External Data from Text". The code recorded during the import is:
我有从软件应用程序导出的 CSV 文件，我需要将其导入 Excel 以分析数据。每天生成 40-50 个 CSV。现在我通过“从文本中获取外部数据”手动执行此操作。导入时记录的代码为：
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;SYSTEM:Users:catalin:Documents:LINELLA:WH Analytics:data:pick 01-18:050:Inquiry closed lists SKU_0142.csv" _
, Destination:=Range("A1704"))
.Name = "Inquiry closed lists SKU_0142"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlMacintosh
.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, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
.UseListObject = False
End With
Selection.End(xlDown).Select
Range("A1710").Select
I want to be able to import automatically all CSV files from a selected folder where I'll put new files and launch the import process. Each file should be inserted immediately after last row of the previous files.
我希望能够从选定的文件夹中自动导入所有 CSV 文件，我将在其中放置新文件并启动导入过程。每个文件应立即插入到前一个文件的最后一行之后。
Your help will be much appreciated.
您的帮助将不胜感激。
回答by stenci
Put the code you recorded in a function, replacing the static file name with a variable, then call that function for each *.csvfile in the folder. The get the example below to work you need to save a file with this macro in the same folder as the csv files. For my quick test I had to replace the separator from ;to ,, and to remove the last row .UseListObject = False.
将您记录的代码放在一个函数中，用变量替换静态文件名，然后为*.csv文件夹中的每个文件调用该函数。要使下面的示例工作，您需要使用此宏将文件保存在与 csv 文件相同的文件夹中。对于我的快速测试我不得不更换分离;到,，并删除最后一行.UseListObject = False。
Sub ImportAllCSV()
Dim FName As Variant, R As Long
R = 1
FName = Dir("*.csv")
Do While FName <> ""
ImportCsvFile FName, ActiveSheet.Cells(R, 1)
R = ActiveSheet.UsedRange.Rows.Count + 1
FName = Dir
Loop
End Sub
Sub ImportCsvFile(FileName As Variant, Position As Range)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FileName _
, Destination:=Position)
.Name = Replace(FileName, ".csv", "")
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlMacintosh
.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, 1, 1, 1, 1, 1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
