Function ExtractPath(sFullPath As String, _
    Optional bIncludeLast As Boolean = True) As String

    ‘Returns only the path (not the file name) from a full path
    Dim lSepPos As Long
    Dim sTempPath As String
    Dim lInclLast As Long
    Const sPATHSEP As String = “”
    lSepPos = InStr(1, sFullPath, sPATHSEP)
    ‘If no path separator, ignore bIncludeLast
    If lSepPos = 0 Then
        lInclLast = 0
        lInclLast = CLng(bIncludeLast) + 1
    End If
    sTempPath = Left(sFullPath, Len(sFullPath) – lInclLast)
    Do Until lSepPos = 0
        sTempPath = Left(sFullPath, lSepPos – lInclLast)
        lSepPos = InStr(lSepPos + 1, sFullPath, sPATHSEP)
    ExtractPath = sTempPath
End Function

Posted in Uncategorized

4 thoughts on “ExtractPath

  1. A bit long winded, plus a couple of bugs….

    The quick & dirty function is:

    Function ExtractPath(sFullPath As String) As String
    ExtractPath = Left$(sFullPath, InStrRev(sFullPath, “”))
    End Function

    Won’t work in XL97 due to the InstrRev function, which is in VBA6. Needs XL2000+.

    This one matches the functionality of the original:

    Function ExtractPath(sFullPath As String, Optional bIncludeLast As Boolean = True) As String

    Dim lSepPos As Long
    Const sPATHSEP As String = “”

    lSepPos = InStrRev(sFullPath, sPATHSEP)
    If lSepPos > 0 Then
    If bIncludeLast = False Then
    lSepPos = lSepPos – 1
    End If
    ExtractPath = Left$(sFullPath, lSepPos)
    End If

    End Function

    All of these function fail with something like sFullPath = “A:File.dat”. This is a valid filename, even with the “” missing.

    For a filename like “File.dat” which does not contain a path, the original returns the passed filename, when it should return a blank.

    Then there’s UNC names (network)…


  2. “A bit long winded”

    Not the first time I’ve been accused of that. I don’t write functions (or try not to, at least) that don’t work in 97. A lot of people still use it, including everyone in my office except me. I’m considering dropping 97 support when the next version comes out, but I haven’t decided yet.


    I think I better fix it for that.

    “when it should return a blank”

    I designed it that, but I can’t remember why. It must have been a particular situation. But I agree, it should return an empty string.

    “Then there’s UNC names (network)…”

    It works for me on UNC paths. Mine are like Servier1FolderFile.xls which it handles okay. Is there something I’m missing?

    Thanks for the comments.

  3. It’s not too long winded, I’ve seen much worse. At least you didn’t use the Split() function, while being novel, is slow & memory hungry (and doesn’t handle A:File.dat either).

    I can’t remember what the UNC problem was, brain failure on my part probably.

    Many of my functions for this type of thing were written many years ago, in my version I wrote my own InstrRev(). That was part of what I meant by long winded. You end up with functions calling functions calling functions… but each function is quite small and readable. Test & forget. For example, my credit card check digit routine dates from 1985!

    One thing you shouldn’t do (even though it works) is this line mixing boolean & maths:

    lInclLast = CLng(bIncludeLast) + 1

    bIncludeLast is a Boolean, which isn’t really a number. By that I mean it’s either True or False, with no guarantee of what their actual values are. VB.Net would have changed the value of True from -1 to 1, but too many people with dodgy code complained!

    Many large companies still use Office97, so you still need to consider that.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.