vba 我可以将多个文本文件导入一张 Excel 表格吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4937664/
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
Can I import multiple text files into one excel sheet?
提问by Kim
I have one folder with multiple text files that I add one text file to every day. All text files are in the same format and are pipe delimited.
我有一个包含多个文本文件的文件夹，我每天都会向其中添加一个文本文件。所有文本文件都采用相同的格式并以竖线分隔。
Is it possible to create code for excel that will automatically import the data from the multiple text files into one worksheet?
是否可以为excel创建代码，将多个文本文件中的数据自动导入到一个工作表中？
I found some code that would import all the text files from the folder, but only if I changed it all to comma delimited first. Also, I could not get it to update if I added files to the folder.
我找到了一些可以从文件夹中导入所有文本文件的代码，但前提是我首先将其全部更改为逗号分隔。此外，如果我将文件添加到文件夹中，则无法更新。
Any help would be greatly appreciated!
任何帮助将不胜感激！
回答by chris neilsen
A good way to handle files in general is the 'FileSystemObject'. To make this available in VBA you need to add a reference to it:
通常处理文件的好方法是“FileSystemObject”。要使其在 VBA 中可用，您需要添加对它的引用：
(select the Tools\References menu. In the References dialog, select 'Microsoft Scripting Runtime')
（选择 Tools\References 菜单。在 References 对话框中，选择“Microsoft Scripting Runtime”）
The following code example will read all files in a folder, reads their content one line at a time, split each line into | separated bits, and writes theses bits to the active sheet starting at cell A1, one row per line.
下面的代码示例将读取文件夹中的所有文件，一次一行读取它们的内容，将每一行拆分为 | 分隔位，并将这些位写入活动表，从单元格 A1 开始，每行一行。
Sub ReadFilesIntoActiveSheet()
Dim fso As FileSystemObject
Dim folder As folder
Dim file As file
Dim FileText As TextStream
Dim TextLine As String
Dim Items() As String
Dim i As Long
Dim cl As Range
' Get a FileSystem object
Set fso = New FileSystemObject
' get the directory you want
Set folder = fso.GetFolder("D:\YourDirectory\")
' set the starting point to write the data to
Set cl = ActiveSheet.Cells(1, 1)
' Loop thru all files in the folder
For Each file In folder.Files
' Open the file
Set FileText = file.OpenAsTextStream(ForReading)
' Read the file one line at a time
Do While Not FileText.AtEndOfStream
TextLine = FileText.ReadLine
' Parse the line into | delimited pieces
Items = Split(TextLine, "|")
' Put data on one row in active sheet
For i = 0 To UBound(Items)
cl.Offset(0, i).Value = Items(i)
Next
' Move to next row
Set cl = cl.Offset(1, 0)
Loop
' Clean up
FileText.Close
Next file
Set FileText = Nothing
Set file = Nothing
Set folder = Nothing
Set fso = Nothing
End Sub
the sub is deliberately simplified to remain clear (i hope) and will need work to be made robust (eg add error handling)
sub 被故意简化以保持清晰（我希望）并且需要工作以使其稳健（例如添加错误处理）
回答by John Conroy
Sounds like it'd be easiser to run a script to cycle thru all the files in the directory, create a new file composed of all the files' contents as new lines, and save that as a csv. something like:
听起来运行脚本来循环遍历目录中的所有文件会更容易，创建一个由所有文件内容组成的新文件作为新行，并将其另存为 csv。就像是：
import os
basedir='c://your_root_dir'
dest_csv="<path to wherever you want to save final csv>.csv"
dest_list=[]
for root, subs, files in os.walk(basedir):
for f in files:
thisfile=open(basedir+f)
contents=thisfile.readlines()
dest_list.append(contents)
#all that would create a list containing the contents of all the files in the directory
#now we'll write it as a csv
f_csv=open(dest_csv,'w')
for i in range(len(dest_list)):
f_csv.write(dest_list[i])
f_csv.close()
You could save a script like that somewhere and run it each day, then open the resulting csv in excel. This assumes that you want to get the data from each file in a particular directory, and that all the files you need are in one directory.
您可以将这样的脚本保存在某处并每天运行，然后在 excel 中打开生成的 csv。这假设您想从特定目录中的每个文件中获取数据，并且您需要的所有文件都在一个目录中。
回答by Fionnuala
You can use Schema.ini ( http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx), Jet driver and Union query, with any luck.
您可以使用 Schema.ini ( http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx)、Jet 驱动程序和 Union 查询，运气好的话。
