How do you search a folder for a cell item " NAME", then return a hyperlink if found?

Hello all,

I am looking for a way to have my spread sheet search a folder with PDFs based on an item name in a call like "CO DETECTORS" IF another cell I have has a "TRUE" in it, and if the name is listed on the PDF file name return a hyperlink to that PDF file.

Can this be done?

example:
IF cell D1 is "TRUE"
Search for file name of cell D2 "CO DETECTORS" in a folder on my hard drive "C:\Users\user\Pictures\MP Navigator EX\2012_05_30"
and if file name found give cell D3 "HYPERLINK" to that file!

Thanks to all that take the time to look at this question, Jerry.

try this:

Sub hyperlink_add()
Dim MyFile As String
If Range("D1").Value = True Then
    MyFile = "C:\Users\user\Pictures\MP Navigator EX\2012_05_30\" & Range("D2").Value
    If Dir(MyFile) <> "" Then
        Range("D3").Hyperlinks.Add anchor:=Range("D3"), Address:=MyFile
    End If
End If
End Sub

will you interrogate all of the cells in column F separately, or do you want to find links to all the filenames in column B if only F7 is checked?
assuming the former, try this:

Sub hyperlink_add2()
Dim rng, cc As Range, MyFile As String
rng = Range("A7:F3338").Address ' enter range to interrogate here
For Each cc In Range(rng).Columns("F").Cells
    If cc.Value = "WX" Then
        MyFile = "C:\Users\user\Pictures\MP Navigator EX\2012_05_30\" & cc.Offset(0, -4).Value & ".pdf"
        If Dir(MyFile) <> "" Then Range("H7").Hyperlinks.Add anchor:=Range("H7"), Address:=MyFile
    End If
Next cc
End Sub

Thanks again for getting back to me NickyC,

I would like to interrogate all of the cells in column F separately.
I need to treat each row separately with its own hyperlink to the column B name PDF of its row, IF column F for that row has "WX" checked.

example: If "WX" Checked in column F of row 100 then return Hyperlink in column H of row 100 to the name in column B in row 100.
If "WX" not checked in column F of row 100 then no hyperlink in column H of row 100.

The same should be true of each row independent of each of the other rows.

I hope this helps.

hi the macro above should achieve that

ah - sorry; try this:

Sub hyperlink_add2()
Dim rng, cc As Range, MyFile As String
rng = Range("A7:F4019").Address ' enter range to interrogate here
For Each cc In Range(rng).Columns("F").Cells
    If cc.Value = "WX" Then
        MyFile = "C:\Users\user\Documents\Invoices\" & cc.Offset(0, -4).Value & ".pdf"
        If Dir(MyFile) <> "" Then cc.Offset(0, 2).Hyperlinks.Add anchor:=cc.Offset(0, 2), Address:=MyFile
    End If
Next cc
End Sub

You are the BEST!! Works like a charm, What can I do for you?

You have saved me hours and hours.

no worries - glad to help

NickyC, I can't Thank you enough, you are a VBA god!