search for keywords in filename

[JakP]

I am working on a small project and fairly new to vba, but managing alright with the guidance of many helpful people. Here's what I'm looking at.

I have a workbook that saves a sheet to a new file for each client, the new filename contains the client name, a claim number, and the date it was saved. i.e. "Jim Tate 123456 04-30-10.xls" I would like to be able to search the directory where all these files are stored for a specific keyword like "tate" or "123456"

For any client there may be more than one file (from different dates) Ultimately, I would like to search for all file that match and be able to select and open the file for viewing and/or printing.

I am working with Excel 2007 under XP Pro SP 3.

Is there an effective way to accomplish this?

Thanks for the help

JakP

 

[Jon von der Heyden]

Hello and welcome to the board.

Something like this perhaps?

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] OpenFiles()
    [COLOR="Blue"]Dim[/COLOR] strPath [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] strFileName [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] strKeyword [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
 
    strKeyword = "blah" [COLOR="Green"]'enter keyword here[/COLOR]
    strPath = "C:\Excel\" [COLOR="Green"]'change path here[/COLOR]
    strFileName = [COLOR="Blue"]Dir[/COLOR](strPath & "*" & strKeyword & "*.xl*")
 
    [COLOR="Blue"]Do[/COLOR] [COLOR="Blue"]While[/COLOR] [COLOR="Blue"]Len[/COLOR](strFileName) > 0
        Workbooks.Open (strPath & strFileName)
        strFileName = [COLOR="Blue"]Dir[/COLOR]
    [COLOR="Blue"]Loop[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[JakP]

That's fantasitc, thanks very much. I'm just looking around to see if I can find reference on how to create that directory listing either in a box that the user can choose the file from (in the case of multiple files) or conversely creating the list on a new sheet with a hyperlink to open a specific file..

I'm sure I'll find that in the forums. Thanks again, you've helped imeasurabley.

JakP

 

[Jon von der Heyden]

Hi

This will create a list of hyperlinks of the files in column A of the active sheet.

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] OpenFiles()
    [COLOR="Blue"]Dim[/COLOR] strPath [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] strFileName [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] strKeyword [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
 
    strKeyword = "blah" [COLOR="Green"]'enter keyword here[/COLOR]
    strPath = "C:\Excel\" [COLOR="Green"]'change path here[/COLOR]
    strFileName = [COLOR="Blue"]Dir[/COLOR](strPath & "*" & strKeyword & "*.xl*")
 
    [COLOR="Blue"]Do[/COLOR] [COLOR="Blue"]While[/COLOR] [COLOR="Blue"]Len[/COLOR](strFileName) > 0
        [COLOR="Blue"]With[/COLOR] Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Hyperlinks.Add anchor:=Range(.Address), Address:=strPath & strFileName
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
        strFileName = [COLOR="Blue"]Dir[/COLOR]
    [COLOR="Blue"]Loop[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[JakP]

Excellent, thank you a ton. Here I was considering a new function for creating the hpyerlink.

Thank you for the warm welcome and the great help.

JakP