Searching a folder to match each file to a table

Question

I added a For loop (see k part) and it really slows down my entire program. Is it possible make this more efficient?

I am searching a specific folder and trying to match each file to a table in my spreadsheet. I am trying to make Quarters(1,j) in the For k loop same as Quarters(i,j) from the lower part of the code but not sure how to do that since I have already used integer i.

For j = 1 To 2
    For k = 1 To 39
        If k <= 29 Then
            'Looks at all the files in the folder for the given Quarter
            SourceFolderName = FolderPath & "\" & Quarters(1, j)
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            Set objFolder = objFSO.GetFolder(SourceFolderName)
        End If

        If k > 29 Then
            SourceFolderName = FolderPath & "\" & Quarters(k, j)
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            Set objFolder = objFSO.GetFolder(SourceFolderName)
        End If

        For Each objFile In objFolder.Files
            i = 1
            NotAssigned = True
            'Keep going until we match the file
            While NotAssigned = True
                'If the beginning of the file name matches for a given state,
                'assign the file name to that state for this quarter
                If Left(objFile.Name, 9) = StateAbbr(i, 1) & Quarters(i, j) & "FA" Then
                    WBName(i, j) = objFile.Name
                    'Stop trying to match the file 
                    NotAssigned = False
                End If
                If i = 39 Then NotAssigned = False
                i = i + 1
            Wend
        Next objFile
        Set objFile = Nothing
        Set objFolder = Nothing
        Set objFSO = Nothing
    Next k
Next j

A few things. (1) `Set objFSO = CreateObject("Scripting.FileSystemObject")` should be outside all loops. (2)`SourceFolderName = FolderPath & "\" & Quarters(1, j) Set objFolder = objFSO.GetFolder(SourceFolderName)` should be outside the `k` loop as it only usese the `i` variable. (3) I think you can use `DIR` with wildcards rather than testing each file. See http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba/10382861#10382861 — brettdj, Jul 31 '13 at 22:56
Hi I am not sure how to transform this using DIR. The reason is being I have too many loops and conditions going on. Can you help me with this? Thanks. — Futochan, Aug 01 '13 at 15:23
Thanks @brettdj. I would never thought of DIR until you mentioned. Now my run time is cut down from 40 min to 2 seconds. THANKS!!! For those who are interested, I posted my solution on top. — Futochan, Aug 01 '13 at 15:59

score 2 · Answer 1 · answered Aug 01 '13 at 16:25

I managed to change my entire code to use DIR instead of looping each cell in spreadsheet and looping each file in my folder. My run time cut down from 40 min to 2 second!!!!!!! I am so amazed by this now. Here is the solution if you are interested.

Dim StrFile As String
For j = 1 To 2
    For i = 1 To 39
        StrFile = Dir(FolderPath & "\" & Quarters(i, j) & "\*FA*")
    Do While Len(StrFile) > 0
        If Left(StrFile, 9) = StateAbbr(i, 1) & Quarters(i, j) & "FA" Then
            WBName(i, j) = StrFile
        End If
        StrFile = Dir
    Loop
    Next i  
Next j

Searching a folder to match each file to a table

1 Answers1