在子文件夹 - VBA中打开部分名称的所有文件 [英] Open all files with a partial name in subfolders - VBA

问题描述

我试图以ToDo.xlsx结尾的所有文件打开所有子文件夹的循环。

I am trying to open all files with names ending with ToDo.xlsx, by doing a loop that goes through all subfolders.

我在另一篇文章中发现了这个循环在StackOverflow中，但是当有大量文件（在我的情况下为35k）时需要太多时间：

I found this loop in another post in StackOverflow, but it takes too much time when there are a lot of files (~35k in my case) :

Public Sub NonRecursiveMethod()
    Dim fso, oFolder, oSubfolder, oFile, queue As Collection

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection
    queue.Add fso.GetFolder("your folder path variable") 'obviously replace

    Do While queue.Count > 0
        Set oFolder = queue(1)
        queue.Remove 1 'dequeue
        '...insert any folder processing code here...
        For Each oSubfolder In oFolder.SubFolders
            queue.Add oSubfolder 'enqueue
        Next oSubfolder
        For Each oFile In oFolder.Files
            '...insert any file processing code here...
        Next oFile
    Loop
End Sub

我的处理代码是：

If InStr(oFile.Name,"ToDo"= <> 0 Then Workbooks.Open Filename:=oSubfolder & oFile

有没有办法把这个条件放在for循环中，以最小化处理时间？

Is there a way to put this condition in the for loop, in order to minimize the processing time ?

谢谢

推荐答案

代替 循环，尝试如果以下lop更快：

In stead of the For each loop, try if the following lop is faster:

filename = Dir(oFolder.Name & "\*ToDo.xlsx")
While (filename <> "")
    ' processing... Full name is oFolder.Name & "\" & filename
    filename = Dir
Wend

这篇关于在子文件夹 - VBA中打开部分名称的所有文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！