Extracting file name and folder name from the file path using VBA in Microsoft Excel

In this article, we have shared VBA code for writing a custom function for extracting the file name and folder name from the file path.

Raw data for this example specifies the full path of certain files.

RawData

Logic explanation

In this example, we have created VBA custom function “FileOrFolderName” which returns the file or folder name as output.

“FileOrFolderName” function takes two parameters as input. First parameter takes file path as input. Second parameter takes Boolean value as input, if we want file name as output then we will assign True value to this parameter and if we want folder name as output then we will assign False value to this parameter.

For separating the file name and folder name from the file path, firstly, we find the location of last occurrence of path separator within the file path. After the last occurrence of path separator, we can easily separate the file name and folder name as text. On right side of the path separator is the file name and text on left side of the path separator is the folder name.

If no path separator exists in the file path then default directory path is specified as the folder name.

Executing the custom function

For using defined custom function, go to cell C14 and enter the function =FileOrFolderName(B14,FALSE) and in cell D14, enter the function =FileOrFolderName(B14,TRUE), where cell B14 contain the file path.

Cell C14 will give the folder name and Cell D14 will give the file name.

ArrowAfterRunningMacro

Please follow below for the code

    
Function FileOrFolderName(InputString As String, _
    ReturnFileName As Boolean) As String
    
'Returns the foldername or the filename based on boolean value assigned

Dim i As Integer, FolderName As String, FileName As String

i = 0

'Code used for finding the position of last occurence of path separator
While InStr(i + 1, InputString, Application.PathSeparator) > 0
    i = InStr(i + 1, InputString, Application.PathSeparator)
Wend

'Extract the folder path
'If No occurence of path separator is found then assign the default directory path
If i = 0 Then
    FolderName = CurDir
Else
    FolderName = Left(InputString, i - 1)
End If

'Extracting the file name
FileName = Right(InputString, Len(InputString) - i)

'Returning the folder or file name from function based on ReturnFileName parameter
If ReturnFileName Then
    FileOrFolderName = FileName
Else
    FileOrFolderName = FolderName
End If

End Function

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Ashok
January 23, 2020 at 11:11 am

Sub ListAllFileNames()
Dim strTargetFolder As String, strFileName As String, nCountItem As Integer

' Initialization
nCountItem = 1
Path = Range("e5").Text
strTargetFolder = Path '"C:\Users\vemsri01\Desktop\pppppp" & "\"
strFileName = Dir(strTargetFolder, vbDirectory)

' Get the file name
Do While strFileName ""
If strFileName "." And strFileName ".." Then
Cells(nCountItem, 1) = strFileName
nCountItem = nCountItem + 1
End If
strFileName = Dir
Loop
End Sub

Reply ↓