excel - 将多个文本文件导入 Excel

Question

我有大约 600 个文本文件。每个文件包含 2 列，并以空格分隔。有什么办法可以将它们全部导入到同一个 Excel 电子表格中吗？

我看到了一篇关于此问题的帖子并使用了以下脚本，但这对我不起作用。它给了我未定义用户定义类型

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:\mypath\")

' 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

`

感谢您的帮助!

Answer 1

您很可能需要设置对 Windows 脚本宿主对象模型的引用。

为此，请从 Visual Basic 编辑器中选择“工具/引用”，然后向下滚动以查找“Windows 脚本宿主对象模型”。勾选此框，然后按确定。现在尝试再次运行您的代码。

此外，我注意到您提到您的数据被分为两列并以空格分隔。您需要替换以下行中的分隔符:

Items = Split(TextLine, "|")

这样:

Items = Split(TextLine, " ")

最后，您最好替换它:

For i = 0 To UBound(Items)
  cl.Offset(0, i).Value = Items(i)
Next

这样:

cl.Resize(1,UBound(Items)-LBound(Items)+1).value = Items