vba 将多个文本文件导入同一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21225894/
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
Importing multiple text files into the same sheet
提问by user3213673
I am able to import one text file into Excel using below code.
我可以使用以下代码将一个文本文件导入 Excel。
Sub test()
Sheet1.Cells(1, 1) = "Time"
Sheet1.Cells(1, 2) = "QueueName"
Sheet1.Cells(1, 3) = "Count"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\temp\Sample.txt", Destination:=Range("$A") _
)**strong text**
.Name = "Sample"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
How do I alter it to import 4 different text files in a way that the files are imported into a single sheet with all the data aligned in following order
我如何更改它以导入 4 个不同的文本文件，将文件导入到一个工作表中，所有数据按以下顺序对齐
File 1 data file 2 data File 3 data file 4 data
文件1数据文件2数据文件3数据文件4数据
Means file 2 data should start where the file 1 data ends.
File 1 data may vary. So I do not know the starting range of the file 2 data.
What should be the Destination:=Range("$A$2")?
意味着文件 2 数据应该从文件 1 数据结束的地方开始。
文件 1 数据可能会有所不同。所以我不知道文件2数据的起始范围。
应该是什么Destination:=Range("$A$2")？
回答by L42
You can use GetOpenFilenameexcel property like this:
您可以GetOpenFilename像这样使用excel 属性：
Sub Sample()
Dim myfiles
Dim i As Integer
myfiles = Application.GetOpenFilename(filefilter:="CSV Files (*.csv), *.csv", MultiSelect:=True)
If Not IsEmpty(myfiles) Then
For i = LBound(myfiles) To UBound(myfiles)
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myfiles(i), Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
.Name = "Sample"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next i
Else
MsgBox "No File Selected"
End If
End Sub
Hope this works for you.
希望这对你有用。
