excel - 根据单元格值检索文件路径(将单元格值与目录中的文件名匹配)

Question

我一直在研究这个并将继续这样做，但我想在此过程中寻求一些帮助。
我正在尝试查找与 A1 的单元格内容匹配的文件是否存在于目录中。文件名可能在我要搜索的值之前或之后有字符。
下图显示了 A 列中的序列号，我试图查找具有该序列号的文件名是否存在于目录中，如果存在，则在 B 列中输出文件路径。

理想情况下，结果将是这样。

我做了一些研究并遇到了类似的东西，这将告诉你它在一个目录中找到了多少个文件名与单元格匹配的文件，但是，我想调整它以提供文件路径(如果存在)。

    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

来源: Code from above

Answer 1

请尝试下一个功能:

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

可以使用下一种方式进行测试:

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

已编辑 :
下一个函数将返回包含部分字符串 ( 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

您可以使用下一种方式对其进行测试。部分字符串应位于“C:C”列中:

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