vba 如何将多个txt文件的数据导入一张Excel表格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16281420/
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
How to import multiple txt files' data into one Excel sheet?
提问by Marc Chemali
I would like to get the data of many txt files in one folder into one Excel sheet.
我想将一个文件夹中的许多txt文件的数据放到一张Excel表格中。
I recorded a macro and ended up opening the files in different workbooks.
我录制了一个宏并最终打开了不同工作簿中的文件。
Sub GetTxtData()
ChDir "C:\Users\Desktop\TXT"
Workbooks.OpenText Filename:="C:\Users\Desktop\TXT\Data0.txt", Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) _
, Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
Workbooks.OpenText Filename:="C:\Users\Desktop\TXT\Data1.txt", _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
End Sub
回答by Denise Skidmore
Try importing the data instead of opening the csv files.
尝试导入数据而不是打开 csv 文件。
In Excel 2007 this is done by Data, From Text.
在 Excel 2007 中，这是通过数据，来自文本完成的。
You'll get something like:
ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Desktop\TXT\Data0.txt", Destination:=Range("$A$1")) ...
你会得到类似的东西：
ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Desktop\TXT\Data0.txt", Destination:=Range("$A$1")) ...
You're going to have to change the destination in code to keep the files from overwriting each other in the same spot. I'd use a full address including sheet name if I wanted them all on different sheets.
您将不得不更改代码中的目的地，以防止文件在同一位置相互覆盖。如果我希望它们都在不同的工作表上，我会使用包括工作表名称的完整地址。
