问题4:如何将同一文件夹中的多个文本文件读入到工作簿中?
解答:通常,我们所看到的例子都是在工作簿中读入一个文本文件中的内容。假设有几个文本文件,我们把它们放在与工作簿相同的文件夹中,那么,现在如何在该工作簿中一次性读取这几个文本文件的内容。下面的程序演示了上述过程,示例工作簿附后,其中源数据引用了
lichaobin网友在他的提问贴中所附的数据。
分两种情况:
(一)所读入的文本文件总行数小于65536行,您可以使用以下代码。
‘**************************************************
Sub Sample1()
Dim n As Long, a(), ff As Integer, txt As String, myDir As String, x
Dim myF As String, i As Long
myDir = ThisWorkbook.Path & Application.PathSeparator
myF = Dir(myDir & "*.txt")
Do While myF <> ""
ff = FreeFile
Open myDir & myF For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, "|")
n = n + 1
ReDim Preserve a(1 To n)
a(n) = x
Loop
Close #ff
myF = Dir()
Loop
Cells.Clear
With ThisWorkbook.Worksheets("Sheet1").Range("a1")
For i = 1 To UBound(a)
.Offset(i - 1).Resize(, UBound(a(i)) + 1) = a(i)
Next
End With
End Sub
‘**************************************************
(二)所读入的文本文件总行数大于65536行,您可以使用以下代码。其中使用了一个变量t和一个判断语句,当多于65536行时,将剩下的数据写入另一工作表中。
Sub Sample2()
Dim n As Long, a(), ff As Integer, txt As String, myDir As String, x
Dim myF As String, i As Long, t As Integer
t = 1
myDir = ThisWorkbook.Path & Application.PathSeparator
myF = Dir(myDir & "*.txt")
Do While myF <> ""
ff = FreeFile
Open myDir & myF For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, "|")
n = n + 1
ReDim Preserve a(1 To n)
a(n) = x
If n = 65536 Then
With ThisWorkbook.Sheets(t).Range("a1")
For i = 1 To UBound(a)
.Offset(i - 1).Resize(, UBound(a(i)) + 1) = a(i)
Next
End With
n = 0: Erase a: t = t + 1
End If
Loop
Close #ff
myF = Dir()
Loop
If n > 0 Then
With ThisWorkbook.Sheets(t).Range("a1")
For i = 1 To UBound(a)
.Offset(i - 1).Resize(, UBound(a(i)) + 1) = a(i)
Next
End With
End If
End Sub
示例文档见读取多个文本文件.rar。
E27ouF6l.rar (53.87 KB)