我可以将多个文本文件导入到一个Excel表中吗？ [英] Can I import multiple text files into one excel sheet?

问题描述

我有一个文件夹有多个文本文件，我每天添加一个文本文件。所有文本文件的格式相同，并以管道分隔。

是否可以创建Excel的代码，将自动将多个文本文件中的数据导入一个工作表？

我发现一些代码将导入文件夹中的所有文本文件，但只有当我将其全部更改为首先以逗号分隔。此外，如果我将文件添加到文件夹，我无法获得更新。

任何帮助将不胜感激！

解决方案

处理文件一般是'FileSystemObject'。要在VBA中使用此功能，您需要添加一个引用：

（选择Tools\References菜单，在引用对话框中，选择Microsoft Scripting Runtime ）

以下代码示例将读取文件夹中的所有文件，一次读取其内容一行，将每行分成|分开的位，并将这些位写入活动表单，从单元格A1开始，每行一行。

  Sub ReadFilesIntoActiveSheet（）
 Dim fso As FileSystemObject 
 Dim文件夹作为文件夹
 Dim文件作为文件
 Dim FileText As TextStream 
 Dim TextLine As String 
 Dim Items（）As String 
 Dim i As Long 
 Dim cl As Range 
 
'获取一个FileSystem对象
设置fso = New FileSystemObject 
 
'获取目录你想要
设置文件夹= fso.GetFolder（D：\YourDirectory\）
 
'将起始点设置为
设置cl = ActiveSheet.Cells （1，1）
 
'循环通过文件夹中的所有文件
对于每个文件在folder.Files 
'打开文件
设置FileText = file.OpenAsTextStream （ForReading）
 
'一次读取文件
尽管没有FileText.AtEndOfStream 
 TextLine = FileText.ReadLine 
 
'将行解析为|分隔符
 Items = Split（TextLine，|）
 
'将数据放在活动表单中的一行
对于i = 0到UBound（Items）
 cl.Offset（0，i）.Value = Items（i）
下一个
 
'移动到下一行
设置cl = cl.Offset（1，0）
循环
 
'清理
 FileText.Close 
下一个文件
 
设置FileText = Nothing 
设置文件=没有
设置文件夹=没有
设置fso =没有
 
 End Sub 
  

sub被故意简化以保持清晰（我希望），并且需要努力使其变得健壮（例如添加错误处理）

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?

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!

解决方案

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:

(select the Tools\References menu. In the References dialog, select '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.

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)

这篇关于我可以将多个文本文件导入到一个Excel表中吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！