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

CodePudding user response：

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