• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 927
  • Last Modified:

Get directory path filename in VBA

I have a column in an Excel spreadsheet that has around 40k records in it, the column is part of a much larger spreadsheet with multiple columns. Each row in the spreadsheet has a varying directory path...

I am looking for a way in VBA to clear out the directory path leaving just the filename (preferably without the file extension). Belowut this runs  I found to try and acheive this - but this runs very very slowly and is not an option..

Does anyone know a quick way of achieving this?

Function GetFilenameFromPath(ByVal strPath As String) As String
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Open in new window

1 Solution
Rory ArchibaldCommented:
Can you just use the Find/Replace dialog (ctrl+h) and enter:
in the Find box, leave the replace box blank, and press Replace All?
Recursively calling GetFilenameFromPath() will indeed take up a lot of time. Try this instead:

Function GetFilenameFromPath(ByVal strPath As String) As String
    Dim pos As Integer

    pos = InStrRev(strPath, "\")
    If pos > 0 Then
        strPath = Mid(strPath, pos + 1)
    End If
    GetFilenameFromPath = strPath

End Function

Open in new window

Blowfelt82Author Commented:
Worked like a charm!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now