Friday, July 02, 2010

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

9 comments:

  1. Bob Phillips2:06 AM

    This is what I would use

    strFilePath = "C:\Users\comp\Documents\sample.xlsx"
    vParts = Split(strFilePath, "\")
    sFileName = vParts(UBound(vParts))

    ReplyDelete
    Replies
    1. Thanks a lot. I used used your code and was great help

      Delete
    2. Anonymous9:06 AM

      I'm getting an expected array error for the UBound, any ideas?

      Delete
  2. Anonymous1:37 AM

    Bob..You are right. This is very simple as we are using only 2 functions.

    sFileName=split(strFilePath,"\")(ubound(split(strFilePath,"\")))

    ReplyDelete
  3. Anonymous5:06 PM

    I used Shasur logic. Thanks for the great tips.Saved my research time.

    ReplyDelete
  4. Anonymous9:13 PM

    sFileName = Dir(strFilePath)

    ReplyDelete
  5. can anyone tell me how to get only file name without its extension ??
    Thanks in advance..

    ReplyDelete
  6. Extract Filename without the extension:
    sFName = Left(sFName, InStrRev(sFName, ".") - 1)

    ReplyDelete
  7. Extract filename without extension:
    sFName = Left(sFName, InStrRev(sFName, ".") - 1)

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.