Quickly Get (Extract) Filenames from FilePaths in Excel using VBA Macro

Let’s assume, you have a list of filepaths in one of the columns in your excel sheet and you want extract only filenames from each given file path and write the name of the file in the next column. You can do this using excel formulas. However, here I am going to show you a simple example on how quickly you can get or extract filenames from a list of paths using VBA macro.

Extract FileName from FilePath using VBA

Let me explain the above image before I show you the code. The first column has the file path, that is, all the files are in the C drive, inside a folder named test. There are different file types and I wish to extract or get only the filenames from the path and show it in the second column.

I also have a Command Button (an ActiveX Control) on the Excel sheet, and I’ll write the macro (code) inside its click event in the VBA section. You can find and add the Command Button from the Developer tab.

Now create a macro with any name you want and open the VBA IDE. Find Project Explorer tools from the top tools bar or simply press Ctrl+r to open the Project Explorer window. From the list of Excel objects find Sheet1 and double click it to open window to write the macro.

Option Explicit

Private Sub CommandButton1_Click()
    
    Dim myDataRng As Range
    Set myDataRng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row)
    
    Dim cell As Range
    
    For Each cell In myDataRng
        GetFileName cell.row, cell(1, 1)
    Next cell
    
    Set myDataRng = Nothing
End Sub

Private Sub GetFileName(iRow As Integer, sFilePath As String)
    On Error GoTo ErrHandler
    
    ' EXTRACT THE FILENAME FROM A FILE PATH.
    Dim objFSO
    Set objFSO = CreateObject("scripting.filesystemobject")
    Dim fileName As String
    fileName = objFSO.GetFileName(sFilePath)
    
    Cells(iRow, 2).Value = fileName         ' THE SECOND COLUMN.
     
ErrHandler:
    '
End Sub

Inside the button’s click event, I have declared two Range object. The first range will define the data in the first column (A). The second is a cell range that will check for contents in each cell in the defined data range.

Set myDataRng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row)

Next, I have written a procedure that takes two parameters, the row and file Path, extracted from each cell in the first column. In this, I am creating File System Object using the CreateObject class. Using this object, I now have access to wide range of methods to lots of things. However, I use the GetFileName() method to quickly and effortlessly return the filenames of the given filepath.

fileName = objFSO.GetFileName(sFilePath)

This is one of the easiest ways to extract filenames in VBA. If you are working files and folders using the File System Object, this is very useful example.

← PreviousNext →