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
Else
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)
Loop
ExtractPath = sTempPath
End Function
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)…
Tony
“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.
“A:File.dat”
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.
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.
Thanks!
Saved me a lot of time!