CODEWITHSUNDEEP
vbaexcel
Alex Jacob
Alex Jacob

Reputation: 97

Find a file in folder using different keywords VBA

I am a new to VBA. My issue is that I have a file that will be updated it into a specific folder by different users. Now everytime a user updates the file, the name of the file might not be the samefolder. However, I can narrow it down using specific keywords. I have been able to search for a file using a keyword but not multiple keywords. Please can you point me in the right direction on how I can use multiple keywords to find a file in a folder? Is it possible to write code that will work like the below?

Sub Start_countries()
Dim keyword, pathname, filename As String
pathname = "C:\XYZ\"

keyword = "lol" Or "rofl" Or "lmfao" Or "rotfl"

filename = Dir(pathname & "*.xls*")
Do While filename <> "*.xls*"
   If LCase(filename) Like "*" & keyword & "*" Then
     Set wb = Workbooks.Open(pathname & filename)
     Find_count_sum_in_file filename
     wb.Close SaveChanges:=True
   Else
     msgbox = "No file Found"
   End If
Loop

 End Sub

Upvotes: 1

Views: 3680

Answers (1)

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

Try the following (adapted following your comment):

Private Const MAX_KWD = 5   ' use a constant to make sure everyone uses the same value

Sub Start_countries()
Dim keyword(1 To MAX_KWD), pathname As String

    'Keywords for first file search
    keyword(1) = "lol"
    keyword(2) = "rofl"
    keyword(3) = "lmfao"
    keyword(4) = "rotfl"
    pathname = "C:\XYZ1\"
    search_for_files keyword(), pathname

    'Keywords for second file search
    keyword(1) = "omg"
    keyword(2) = "fyi"
    keyword(3) = "ok"
    keyword(4) = "brb"
    pathname = "C:\XYZ2\"
    search_for_files keyword(), pathname

End Sub

Sub search_for_files(keyword(), pathname)
Dim filename As String, s As String
Dim i As Integer

    filename = Dir(pathname & "*.xls*")
    Do While InStr(filename, ".xls") <> 0
        s = LCase(filename)
        For i = 1 To MAX_KWD
            If (InStr(s, keyword(i)) > 0) Then Exit For ' found!
        Next i
        If (i <= MAX_KWD) Then
            Set WB = Workbooks.Open(pathname & filename)
            Find_count_sum_in_file filename
            WB.Close SaveChanges:=True
        Else
            MsgBox "No file Found"
        End If
        filename = Dir()
    Loop
End Sub

Note that in Dim pathname, filename, s As String only s is declared as String; all others are declared as Variant (the As String does not apply to all variables declared on the line).

Note also that in your While filename <> "*.xls*" the test will be exact, i.e. it will look also for asterisks (*) in filename.

Upvotes: 1

Related Questions