How to search files from a directory with two keywords in file name???

[aaromic2000]

I have a shared drive mapped to directory "M:\\"

I need to search a file from one of the folders in the directory. I'm able to list all the files from a folder in a excel sheet by specifying the path in the program. But I don't know how to search a individual file in the folder with two keywords in the file name...

Can anyone help me out.../

 

[bertie]

Here is one way.

Set up your key words and the path to the folder:

  [COLOR=red]key1[/COLOR] = "Boo"
 [COLOR=red] key2[/COLOR] = "k1"
 
  sPath = "c:\temp\"

Get the first Excel file:

  sFilename = Dir(sPath & "*.xl*")

Loop through all the Excel files in the folder and use the InStr() function to test the filename

  [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] sFilename = ""
    [COLOR=darkblue]If[/COLOR] InStr(1, sFilename, [COLOR=red]key1[/COLOR], vbTextCompare) Or _
      InStr(1, sFilename, [COLOR=red]key2[/COLOR], vbTextCompare) [COLOR=darkblue]Then[/COLOR]
 
      MsgBox "Found one"
 
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    sFilename = Dir()
  [COLOR=darkblue]Loop[/COLOR]

Putting it all together:

[COLOR=darkblue]Sub[/COLOR] test()
  [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] sFilename [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] key1 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] key2 [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
 
  key1 = "Boo"
  key2 = "k1"
 
  sPath = "c:\temp\"
 
  [COLOR=darkblue]If[/COLOR] Right(sPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR] sPath = sPath & "\"
 
  sFilename = Dir(sPath & "*.xl*")
 
  [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] sFilename = ""
    [COLOR=darkblue]If[/COLOR] InStr(1, sFilename, key1, vbTextCompare) Or _
      InStr(1, sFilename, key2, vbTextCompare) [COLOR=darkblue]Then[/COLOR]
 
      MsgBox "Found one"
 
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    sFilename = Dir()
  [COLOR=darkblue]Loop[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps,
Bertie