Opening Photos using VBA / Passing Cell location to Macro?

[SpiffWilkie]

I run a daily report at work and would like to link photos to the report. There are values in the report that are part of the filename but the rest of the filename is unknown so I have to use wildcards which throws the option of using a hyperlink out the window (unless someone else knows better than I do). I have a button and a macro set up to open photos. Here's the code I stole to make that work:

Sub DoPhotosExist()
    Dim i As Integer
    With Application.FileSearch
        .LookIn = "J:\Camera Files\Inspection Photos\"
        '* represents wildcard characters
        .Filename = Range("F3").Value & "*.jpg"
        If .Execute > 0 Then 'Photos Exist
        For i = 1 To .FoundFiles.Count
        Call Shell("c:\WINNT\system32\rundll32.exe C:\WINNT\system32\shimgvw.dll,ImageView_Fullscreen " & .FoundFiles(i))
        Next i
    End If
End With
End Sub

So, that code works. Unfortunately, as you can see, I'm stuck with using a single cell value if I got that route.
I'm just looking for ways I can make this a little more dynamic. Some days I might have 0 or 1 or 2 lines with values and some days I might have 50. The report that is run is created by macros that pull data from Access so normally a person doesn't actually look at the report when it's published and I would like to keep this as automated as possible.
Is there a way to embed code in a cell next to the values in the report so I can do something like this?

=if(len(F3)>0, run the code with F3 as an argument, "") 
if(len(F4)>0, run the code with F4 as an argument, "")
etc, etc, etc.

Am I completely missing something super obvious? Any suggestion are appreciated.
Thanks!

 

[jbeaucaire]

In this macro, each time through the loop the variable PHOTO should have a different photoname. You can do what you want with that value.

Option Explicit

Sub DoPhotoNamesExist()
Dim PhtRng As Range, pName As Range
Dim fPath As String, Photo As String

fPath = "J:\Camera Files\Inspection pNames\"
Set PhtRng = Range("F3:F" & Rows.Count).SpecialCells(xlCellTypeConstants)

For Each pName In PhtRng
    Photo = Dir(fPath & pName & "*.jpg")
    Do While Len(Photo) > 0
        Call Shell("c:\WINNT\system32\rundll32.exe C:\WINNT\system32\shimgvw.dll,ImageView_Fullscreen " & Photo)
        Photo = Dir
    Loop
Next pName

End Sub

 

[SpiffWilkie]

Thanks for the help. If I'm not mistaken, your code, as it stands, opens each photo at the same time? I really only want to open one at a time if the user clicks on a link on a particular line.
I ended up writing a loop that looks for the filename and then creates a hyperlink if the photos exist.

Sub CreateHyperLinks()
    Dim SearchRow As Integer
    SearchRow = 3
    While Len(Range("F" & SearchRow)) > 0
        Dim i As Integer
        With Application.FileSearch
            .LookIn = "J:\Camera Files\Inspection Photos\"
            '* represents wildcard characters
            .Filename = Range("F" & SearchRow).Value & "*.jpg"
            If .Execute > 0 Then 'Photos Exist
            For i = 1 To .FoundFiles.Count
            Range("G" & SearchRow).Value = "=hyperlink(" & Chr(34) & .FoundFiles(i) & Chr(34) & ", " & Chr(34) & "Click To View Photos" & Chr(34) & ")"
            Next i
        End If
    End With
    SearchRow = SearchRow + 1
    Wend

End Sub