Retrieve File path based on cell value (match cell value to file name in directory)

Question

I've been researching this and will continue to do so, but figure i'd ask for some help along the way.

I'm trying to find if a file matching the cell contents of A1 exists in a directory. The file name may have characters before or after the value I'm searching for.

The below image shows a serial number in column A, i'm trying to find if a file name with that serial number exists in a directory, and if it exists, output the file path in column B.

Ideally, the outcome will be this.

I have done a bit of research and came across something similar, this will tell you how many files it finds in a directory with file names matching the cell, however, I'd like to adjust this to provide the file path if it exists.

    Sub countFiles()

    Set last = Range("A:A").Find("*", Cells(1, 1), searchdirection:=xlPrevious)

    For n = 2 To last.Row
        Cells(n, 2).Value = loopThroughFilesCount("C:\Users\yalinbah\Desktop\boyner\gör‌​‌​seller2\Tekstil\", Cells(n, 1).Value)
    Next

End Sub

Function loopThroughFilesCount(dirFolder As String, strToFind As String) As Double

    Dim filePath As Variant
    filePath = Dir(dirFolder)
    While (filePath <> "")
        If InStr(filePath, strToFind) > 0 Then
            filesCount = filesCount + 1
        End If
        filePath = Dir
    Wend

    loopThroughFilesCount = filesCount

End Function

Source: Code from above

Answer 1

Please, try the next function:

Function GetFilePath(dirFolder As String, strToFind As String) As String
     GetFilePath = Dir(dirFolder & "*" & strToFind & "*.*")
End Function

It can be tested using the next way:

Sub countFiles()
  Dim sh As Worksheet, lastRow As Long, i As Long
  Const foldPath As String = "C:\Users\yalinbah\Desktop\boyner\gör????seller2\Tekstil\"
  Set sh = ActiveSheet
  lastRow = sh.Range("A" & sh.rows.count).End(xlUp).row
  For i = 2 To lastRow
        sh.Range("B" & i).value = foldPath & GetFilePath(foldPath, sh.Range("A" & i).value)
  Next
End Sub

Edited:

The next function will return the folder containing a partial string (strToFind):

Function getFoldPath(dirFolder As String, strToFind As String) As String
    Dim fldName As String
    fldName = Dir(dirFolder & "*" & strToFind & "*", vbDirectory)
    Do While fldName <> ""
        If fldName <> "." And fldName <> ".." Then
            ' Use bitwise comparison to make sure dirFolder is a directory.
            If (GetAttr(dirFolder & fldName) And vbDirectory) = vbDirectory Then
                getFoldPath = fldName: Exit Function
            End If
        End If
        fldName = Dir
    Loop
End Function

You can test it using the next way. The partial string should be in "C:C" column:

Sub countFolders()
  Dim sh As Worksheet, lastRow As Long, i As Long, fldName As String
  Const foldPath As String = "C:\Users\yalinbah\Desktop\boyner\gör????seller2\Tekstil\"
  Set sh = ActiveSheet
  lastRow = sh.Range("C" & sh.rows.count).End(xlUp).row
  For i = 2 To lastRow
        fldName = getFoldPath(foldPath, sh.Range("C" & i).value)
        sh.Range("D" & i).value = IIf(fldName <> "", foldPath & getFoldPath(foldPath, sh.Range("C" & i).value), "")
  Next
End Sub