Extract Filenames from FilePaths in Excel with VBA Macro

Last updated: 06th February 2025

Let’s assume you have a list of file paths in one of the columns in your Excel sheet, and you want to extract only the filenames from each path and write them in the next column. While this can be done using Excel formulas, I'll show you a simple example of how you can quickly achieve this using a VBA macro.

Extract FileName from FilePath using VBA

Let me explain the image above before I show you the code. The first column contains the file paths, with all the files located in the C drive within a folder named test. There are various file types, and my goal is to extract only the filenames from each given file path and display them 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. Simply go to the Developer tab, click Insert and then select "Command Button" from the ActiveX Controls section.

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()
    ' Define the range of data in column A.
    Dim myDataRng As Range
    Set myDataRng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row)
    
    ' Loop through each cell in the defined range.
    Dim cell As Range
    For Each cell In myDataRng
        GetFileName cell.row, cell(1, 1)
    Next cell
    
    Set myDataRng = Nothing     ' Clean up the range object.
    Exit Sub
End Sub

Private Sub GetFileName(iRow As Integer, sFilePath As String)
    On Error GoTo ErrHandler
    
    ' Create a File System Object to extract the filename from the 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:
    ' Error handling code can be added here.
    MsgBox "Error extracting filename from path: " & sFilePath, vbExclamation
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 →