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.
Thanks for reading.