Excel VBA check if file exists with a twist

It is hard to be specific and short in this topic, but I try my best:

Let's say we have a folder on a specific location where we get files with names for example let's use x1; x2; x3, etc.

Then we have an excel file where we have numbers in a column. ex. 1; 2; 3; etc.

I have written a script that makes it possible to click those numbers so an explorer window will pop up searching for the specific number in the folder.

For example we click the number "2", and an explorer window will pop up with the search result for "2x" in that specific folder.

The script looks like this:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

Dim c As String
Dim backup As String

c = Target.Range
backup = "~%3D" & Left(c, 7)

    If Left(c, 1) = "C" And Len(c) > 7 And Right(c, 1) = "$" Then 'this part doesn't really matter
    RetVal = Shell("c:\Windows\explorer.exe ""search-ms:displayname=Search%20Results&crumb=System.Generic.String%3A" & backup & "%20kind%3A%3Dfolder&crumb=location:%5C%5Cserver%5Cbackup$", vbNormalFocus)

    End If

End Sub

I wonder if it is possible somehow, to find out if there is a file that matches the search or it does not exists. What I mean is, for example if I click on the number "2" in excel, the explorer will pop up and it will show the files which names contain "x2" or it will show "No items match your search."

I would like to return this somehow to excel if we have a search result or we don't. Even a Boolean would do the trick for me.

I can not use the ordinary check like:

If Dir(folder) = "" Then

because we can have multiple files with the same name and their name contains the date of their creation. ex. x1_02_04_2015 and we can also have an x1_05_11_2014

Thank You in advance!

Solution

You can try the following. You don't really need to use any shell functionality

     Sub LoopThroughFilesWorkbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
        Dim MyObj As Object, MySource As Object, file As Variant, myBool As Boolean
        file = Dir("c:\testfolder\")
        Do While file <> ""
            Bool = False
            If InStr(file, "test") > 0 Then ' you can change teh condition here to fit your criteria
               myBool = True
               MsgBox ("Found" & file)
            End If
            file = Dir
        Loop
        If Not myBool Then
            MsgBox ("File not found")
        End If
    End Sub