Find a file in folder using different keywords VBA

Question

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

Paul Ogilvie · Accepted Answer · 2015-06-29 19:21:49Z

1

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.

edited Jun 29, 2015 at 19:21

answered Jun 27, 2015 at 12:50

Paul Ogilvie

25.3k4 gold badges25 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Jacob Over a year ago

Thanks a lot Paul. I have posted an answer as a reply since there isn't much I can paste in here. Please could you take a look at it?

Paul Ogilvie Over a year ago

You suggested to exit the loop when the keyword is empty (and I reviewed your edit and pressed the wrong button): You should NOT exit the loop before i > MAX_KWD!! That is the terminating condition and indicates that there were NO matches!!

Collectives™ on Stack Overflow

Find a file in folder using different keywords VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related