CODEWITHSUNDEEP
vba
Kobayashi
Kobayashi

Reputation: 119

Look for specific files in folder w/ VBA

I have a macro that loops through each file in a folder and do some things if the file has the today's date in its name.

Here is a piece of the code:

For Each objFile In objFolder.Files

If Left(objFile.Name, 8) = Format(Date, "dd-mm-yy") Then


        currSheet = Mid(objFile.Name, 10, 4)
        Sheets(currSheet).Activate
        'LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
        'ActiveSheet.Range("A1:D" & LastRow).Clear
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & FolderName & objFile.Name _
            , Destination:=Range("$A$1"))
            .Name = Left(objFile.Name, Len(objFile.Name) - 4)
           '. 
           '.
           '.
End If
Next objFile

I've just realized that it takes around 60 seconds to find the today's files, because it loops through all files in folder (around 0.1 second per file, but there's a lot of files).

I'd like to find files with the following names (only for today's date), I will run this macro once a day, for example: 

30-07-18 CA01 NEGS.txt
30-07-18 CA02 NEGS.txt
30-07-18 CA03 NEGS.txt
30-07-18 CA04 NEGS.txt

So I know the name pattern.
There's a maximum of 4 files in the same date. So it would be great if I could search only these files (and check if it exists), instead of loop through all files. Is is possible to do it? Any help will be appreciated!

Upvotes: 0

Views: 40

Answers (1)

Domenic
Domenic

Reputation: 8124

The Dir function accepts wildcards, and can return the file names that match a specified pattern. Here's an example...

Option Explicit

Sub test()

    Dim strPath As String
    Dim strFile As String
    Dim strPattern As String

    strPattern = Format(Date, "dd-mm-yy")

    strPath = "C:\Users\Domenic\Desktop\"

    strFile = Dir(strPath & strPattern & "*.txt", vbNormal)
    Do While Len(strFile) > 0
        'Do stuff
        '
        '
        strFile = Dir
    Loop

End Sub

Upvotes: 3

Related Questions