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
Posts
This is what I would use
ReplyDeletestrFilePath = "C:\Users\comp\Documents\sample.xlsx"
vParts = Split(strFilePath, "\")
sFileName = vParts(UBound(vParts))
Thanks a lot. I used used your code and was great help
DeleteI'm getting an expected array error for the UBound, any ideas?
DeleteBob..You are right. This is very simple as we are using only 2 functions.
ReplyDeletesFileName=split(strFilePath,"\")(ubound(split(strFilePath,"\")))
I used Shasur logic. Thanks for the great tips.Saved my research time.
ReplyDeletesFileName = Dir(strFilePath)
ReplyDeletecan anyone tell me how to get only file name without its extension ??
ReplyDeleteThanks in advance..
Extract Filename without the extension:
ReplyDeletesFName = Left(sFName, InStrRev(sFName, ".") - 1)
Extract filename without extension:
ReplyDeletesFName = Left(sFName, InStrRev(sFName, ".") - 1)