Search for a file and return path

Question

I have a column which has file names. I have a folder which has all the Excel files. Is it possible to link both these and get the file path in adjacent cell?

For example: I have a value "AAA", "BBB", and so on in column A1,A2 similarly I have a set of excel files in a folder with name AAA.xlsx, BBB.xlsx.

I'll choose folder path to macro manually every time.

Finally, by comparing the cell value in column A and file name in the folder, the file path should be displayed in column B for matched items.

More details are needed. Example screenshots are useful too. Also, what have you tried yourself to do the task? — ZygD, Mar 10 '20 at 10:51
Do you want to perform a search? Because otherwise the Excel should know where to look. It can be a parent folder of the calling file, or some predefined folder which you should provide yourself. — ZygD, Mar 10 '20 at 11:06
yes I want to search for cell value in the folder and get the path of the file — Raags, Mar 10 '20 at 11:18

ZygD · Answer 1 · 2020-03-10T12:29:07.860

This sub (return_paths) should do it. It uses the function GivePath.

Sub return_paths()
  Dim sSearchPath As String: sSearchPath = "C:\Temp"
  Dim oFSO As Object
  Dim i As Long
  Set oFSO = CreateObject("Scripting.FileSystemObject")
  For i = 1 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    ActiveSheet.Cells(i, 2) = GivePath(ActiveSheet.Cells(i, 1), oFSO.GetFolder(sSearchPath))
  Next
End Sub


Private Function GivePath(sName As String, oDir As Object, Optional ByRef blnFound As Boolean) As String
  Dim f As Object
  Dim sf As Object

  If blnFound Then Exit Function

  For Each f In oDir.Files
    If f.Name = sName Then
      GivePath = f.Path
      blnFound = True
    End If
    If blnFound Then Exit Function
  Next

  For Each sf In oDir.SubFolders
    GivePath = GivePath(sName, sf, blnFound)
    If blnFound Then Exit Function
  Next

  If GivePath = "" Then GivePath = "The file was not found"

End Function

After running the result looks like this:

q0mlm · Answer 2 · 2020-03-10T12:28:11.350

1

Following your last edit this should work:

Sub SetFullPath()

MyPath = "C:\Insert path of the folder where you have the files here\"
For Each cell In Range("B1:B100")'Customize your range
If Len(Dir(MyPath & Range("A" & cell.Row).Value & ".xlsx")) = 0 Then
cell.Value = "File Not Found"
Else: cell.Value = MyPath & Range("A" & cell.Row).Value & ".xlsx"
End If
Next

End Sub

Let me know if something has to be changed.

edited Mar 10 '20 at 12:28

answered Mar 10 '20 at 11:50

q0mlm

313
1
3
10

Hi, I need to choose the path by browsing explorer.. also this code is not working for me when path is fed. – Raags Mar 10 '20 at 13:02
Mmm then I am not sure of what you mean by this "Ill choose folder path to macro manually everytime". What kind of error do you get? I've made a quick test and it worked fine. – q0mlm Mar 10 '20 at 13:09

score 0 · Answer 3 · answered Mar 10 '20 at 13:36

Please find the solution I got from consolidating all results. Thanks guys. you all are awesome!!!

Sub FolderDetails()
Dim FSO As New FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim rRng As Range, rCl As Range
Dim sFolder As String
''// Open the select folder prompt
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = -1 Then ' if OK is pressed
sFolder = .SelectedItems(1)
End If
End With
Set rRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
For Each rCl In rRng
If FSO.FileExists(sFolder & Application.PathSeparator & rCl.Value & ".xlsx") Then
rCl.Offset(, 1).Value = sFolder
Else: rCl.Offset(, 1).Value = "The File Does Not Exist"
End If
Next rCl
End Sub

Search for a file and return path

3 Answers3