将多个文本文件导入Excel

Question

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

我看到一篇关于这个帖子的文章，并使用了下面的脚本，但那对我没用。这红粉我User-defined type not defined

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编辑器中选择Tools/References，然后向下滚动以找到“Windows Script Host Object Model”。勾选此框然后按确定。现在尝试再次运行您的代码。

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

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