Astra

VBA Get File Path from Filename

Estimated reading: 3 minutes

Suppose you have a folder with thousands of files or subfolders. It’s a hassle to find any particular file location if you don’t know the exact file location. When you are unsure exactly where the file is located on your computer or do not want to copy the file name manually, you can use the following VBA code to get the file path from the filename.

Sub GET_FILE_PATH()
    Dim File_System_obj As Object
    Dim Search_Folder As String
    Dim File_Name As String
    Dim Found_File As String
    
    'Get the file name you want to search for from the cell E7 and assign to the File_Name variable
    File_Name = ActiveWorkbook.Sheets(1).Range("E7").Value
    
    'Get the search folder name from the cell E8 and assign to the Search_Folder variable
    Search_Folder = ActiveWorkbook.Sheets(1).Range("E8").Value
 
    'Create an instance of the FileSystemObject
    Set File_System_obj = CreateObject("Scripting.FileSystemObject")
    
    'Recursively search for the file in the specified folder
    Found_File = RecursiveSearch(File_System_obj.GetFolder(Search_Folder), File_Name)
    
    'Check if the file was found
    If Found_File <> "" Then
        MsgBox "File found at " & Found_File
        ActiveWorkbook.Sheets(1).Range("E9").Value = Found_File
    Else
        MsgBox "File not found"
    End If
End Sub

Function RecursiveSearch(Folder As Object, File_Name As String) As String
    Dim Sub_Folder As Object
    Dim File As Object
    
    'Search for the file in the current folder
    For Each File In Folder.Files
        If File.Name = File_Name Then
            RecursiveSearch = File.Path
            Exit Function
        End If
    Next File
    
    'Recursively search for the file in subfolders
    For Each Sub_Folder In Folder.SubFolders
        RecursiveSearch = RecursiveSearch(Sub_Folder, File_Name)
        If RecursiveSearch <> "" Then Exit Function
    Next Sub_Folder
    
    'Return an empty string if the file was not found
    RecursiveSearch = ""
    
End Function

Note: Before running the code, please check the following references.

VBA Get File Path from Filename
CLick here to download the excel sheet to Get File Path from Filename by vba

Steps to run this code and see results:

Step 01: In the Excel sheet(macros enabled workbook format- .xlsm), insert the file name you want to search and search directory in cells E7 and E8, respectively.

VBA Get File Path from Filename1

Note: Here, we entered the file path as C:\Users\USER\Desktop\SOLVED EXCEL and the file name as Important.txt. Cause we are sure the file is located inside the directory C:\Users\USER\Desktop\SOLVED EXCEL, but not sure that the file is exactly located (eg. inside any subfolders)

Step 02: Go to the Developer tab. Click on the Visual Basic icon.

Step 03: Click on the Insert Module icon and paste the VBA code we provided earlier by pressing Ctrl+V. Then save by pressing Ctrl+S.

VBA Get File Path from Filename2

Step 04: Pressing F5 key and click on the Run button

VBA Get File Path from Filename3

The result is: 

A message box will show the exact file location. Click on OK 

VBA Get File Path from Filename4

And the file location will be inserted in cell E9

VBA Get File Path from Filename5

9 thoughts on “VBA Get File Path from Filename”

  5. bacaan doa nabi yunus

    Hi, i read your blog from time to time and i own a similar one and i was just curious if you get a lot of spam feedback?
    If so how do yyou prevent it, any plugin or anything you can suggest?
    I get so much lately it’s driving me mad sso any assistance is very
    much appreciated.

    Stop by my sitre :: bacaan doa nabi yunus

    Reply
  8. http://Forum.Ll2.ru/member.php?690687-Svetlmai

    What i do not realiz is actually hoow you’re
    noow not actually much more well-appreciated than yoou might be rkght now.
    Yoou are very intelligent. You recognize therefore significantly
    in the case of this subject, produced me in mmy view imagine it from so many various angles.
    Its like men annd women don’t seem too bee fascinated except it is something to do with Girl gaga!
    Your personal stuffs excellent. At all times deal with iit up!

    My page :: http://Forum.Ll2.ru/member.php?690687-Svetlmai

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Share this Doc
Jump to Topics

Powered By SOLVED EXCEL

SOLVED EXCEL
Scroll to Top