Get directory path filename in VBA

Posted on 2012-09-06
Last Modified: 2012-09-06
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

Question by:Blowfelt82
    LVL 85

    Expert Comment

    by:Rory Archibald
    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?
    LVL 7

    Accepted Solution

    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


    Author Closing Comment

    Worked like a charm!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now