vba - 仅循环浏览目录中存在的文本文件以使用 vba 导入数据

Question

我有一个下面的代码，它从目录中存在的所有文件中导入数据。我只想将数据输入限制为 .txt 文件。即仅从文本文件导入。任何帮助。

    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:\workdir\NX12IP25_RenewDaimler\textfiles\t")
    Set cl = ActiveSheet.Cells(1, 1)
    For Each file In folder.Files
        Set FileText = file.OpenAsTextStream(ForReading)
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine
            Items = Split(TextLine, "|")
    cl.Value = folder & "\" & file.Name 
    For i = 0 To UBound(Items)
        cl.Offset(0, i + 1).Value = Items(i)
    Next
            Set cl = cl.Offset(1, 0)
        Loop
        FileText.Close
    Next file
    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub

Answer 1

您应该使用 If检查最后 4 个字符是否为 .txt 的语句.你可以通过两种方式做到这一点:

1) If Right(file.Name, 4) = ".txt" Then ...
或者

2) 使用正则表达式:If file.Name like "*.txt" Then ... .

我更喜欢第一个解决方案，它更有效。

检查此条件应该是您在循环中执行的第一个操作，因此您将忽略与我们的模式不匹配的文件(在这种情况下，不是文本文件)。