How to extract file name from FullPath string using VBA

Extract Name of the File from Path / Fullname using VBA

There are many methods to extract the filename from a given string. You can use FileSystemObject's function GetFileName or can use Arrays to get the last element of the array split by path separator

Here we use even simpler functions like Dir and InStrRev to achieve the same

Dir function will retrieve the name only if the file exists:


strFilePath = "C:\Users\comp\Documents\sample.xlsx"

sFileName = Dir(strFilePath)


If the file doesn't exist, Dir function will return an empty string. The following would be a better option


strFilePath = "C:\Users\comp\Documents\sample.xlsx"

sFileName = Mid(strFilePath, InStrRev(strFilePath, "\") + 1, Len(strFilePath))


Try it out and post the options you use