Import CSV 文件B. Excel

我想请您提出帮助:

我有 CSV 从我需要导入的软件应用程序导出的文件 Excel 用于数据分析。 每日生成 40-50 CSVs. 目前我手动通过 "Get External Data from Text". 记录期间的代码 import, 是一个:


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


我希望能够自动 import 全部 CSV 来自所选文件夹的文件,其中我放置新文件并运行该过程 import. 每个文件必须在上一个文件的最后一行后立即插入。

你的帮助将非常感激。
已邀请:

奔跑吧少年

赞同来自:

将录制的代码放入函数,替换变量文件的静态名称,然后调用每个文件的函数。
*.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

要回复问题请先登录注册