Extracting the file name ONLY from path

[ReportSmithing]

I'm hoping someone can help me out with this problem. I need to extract the file name from a path - I've already been able to strip away up to the file name but I need to strip away the file extension also.

Here's what I have so far...

Dim i As Long
fname = ActiveTitle()
FileExt = Right$(ActiveTitle(), Len(ActiveTitle()) - InStr(ActiveTitle(), "."))

On Error Resume Next
For i = Len(fname) To 1 Step -1
If Mid(fname, i, 1) = "\" Then
Exit For
End If
Next i
GetFileName = Trim(Mid(fname, i + 1))

This works fine, but I still need the file extension removed. Heres the catch: I can't use RevInStr to do this since I'm working on an older application.

Thanks for you help.

 

[TonyJollans]

Hi ReportSmithing,

I suspect there's a FAQ on this but really you've already got code which looks from the end to find the position of the last "\" so just do the same to find the position of the last "." in the resulting string and take Left$( ... , i-1)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[earthandfire]

This covers a number of eventualities:

Only use one of the lines beginning Function.
The first has a typed parameter and returns a string, the second uses variants and could therefore receive and return Nulls

A Null parameter
An empty string parameter
No filename (path only)
No extension
Just the filename as a parameter
A full path and filename (with or without extension)

Function GetFilename(p As String) As String
Function GetFilename(p)
Dim f As String
Dim a As Integer
Dim b As Integer

  If IsNull(p) Then
    GetFilename = ""
  Else
    a = 0
    b = InStr(p, "\")
    'Using While ... Wend in case Do While ... Loop is not available
    While b <> 0
      a = b
      b = InStr(b + 1, p, "\")
    Wend
    f = Right(p, Len(p) - a)
    a = InStr(f, ".")
    If a = 0 Then
      GetFilename = f
    Else
      GetFilename = Left(f, a - 1)
    End If
  End If
End Function

Hope this helps.

 

[strongm]

You might also want to consider:

[blue]MsgBox CreateObject("scripting.filesystemobject").GetFileName(fname)[/blue]

 

[fumei]

Unless you have files with odd extensions, then really, there are only four characters to deal with - ".xxx". I am not understanding the need for such amount of code. The .Name property does not include the path, so there is no need for dealing with "\" etc.

Excel:
Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)

For Word:
Left(ActiveDocument.Name, Len(ActiveDocument.Name) - 4)

Gerry
See my Paintings and Sculpture

 

[SkipVought]

More than one way to skin a cat...

for versions > Excel 97 Split is available...

sFName = Split(Split(PathName, "\")(UBound(Split(PathName, "\"))), ".")(0)
sPath = Left(PathName, InStrRev(PathName, "\") - 1)

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[strongm]

> there are only four characters to deal with - ".xxx"

I'd argue that that is somewhat shortsighted

 

[TonyJollans]

I'm a little more cautious than some of you - at least in code for other people. The file extension is not necessarliy three characters (unless yuou're working with known extensions in which case, obviously, it's easy) and, also, the filename may have a dot in it so the only sure way is to search for the last dot.

It seemed to me that the OP was explicitly asking for code equivalent to InStrRev as he had a pre-2K version. Silly thing was he already had it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

MSOffice FAQs: faq68-2561

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[strongm]

ooh, and I missed the rquirement to strip the extension. Change my code to:

MsgBox CreateObject("scripting.filesystemobject").GetBaseName(fname)

 

[fumei]

short sighted....yes, it is absolutely true that one SHOULD be cautious. The extension could very well be ".xx" and only two characters. Therefore, I agree, properly, there should be a test for the last dot.

This is a good example of needs analysis and real requirements. It could very be that the simple answer IS simple. If a needs analysis determines the specific requirement is for processing files that DO all have a dot + three character extension....then process them with a simple solution.

Gerry
See my Paintings and Sculpture

 

[TonyJollans]

Hi Gerry,

I was actually thinking of the not uncommon 4-charater extensions like .tiff or .html when I was posting but Mike's scripting solution covers all the angles and I can't imagine anything shorter.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fumei]

Except the original post was using ActiveTitle....which hardly implies that the file is going to be a .TIFF, or .HTML, now does it? The original post implied an Office application that is only dealing with itself.

But, yes.... you are technically correct, a four character extension would have to be covered. My point though was if there is no need for anything but a three character extension....why have extraneous code to cover situations that will never arise?

If there is only a NEED for three character extensions, then code for that need. If there is a NEED to have it flexible, then code to have that flexibility.

Gerry
See my Paintings and Sculpture

 

[ReportSmithing]

Hi All,

I forgot to thank all of you for you advice. I ended up taking TonyJollans advice and this is what I came up with (works fine so far)...

Dim i As Long
fname = ActiveTitle()
FileExt = Right$(ActiveTitle(), Len(ActiveTitle()) - InStr(ActiveTitle(), "."))

On Error Resume Next
For i = Len(fname) To 1 Step -1
If Mid(fname, i, 1) = "\" Then
Exit For
End If
Next i
GetFileName = Trim(Mid(fname, i + 1))

On Error Resume Next
For i = Len(GetFileName) To 1 Step -1
If Mid(GetFileName, i, 1) = "." Then
Exit For
End If
Next i
TheFileName = Trim(Left(GetFileName, i - 1))

If you notice, I'm actually using the same code over again and looking for the "." in GetFileName.

THANKS AGAIN!

 

[strongm]

>then code for that need. If there is a NEED to have it flexible, then code

Given that there is a short, flexible one-liner solution, why would one limit oneself to a partial solution?

 

[PHV]

one-liner solution
Not really as good practice is to instantiate the fso, call the GetBaseName method and then release the fso.
Anyway, its the way I suggest too.

 

[strongm]

That's exactly what the one-liner does

 

[PHV]

That's exactly what the one-liner SHOULD does.
Unfortunately MS products are known to not always properly release memory for objects implicitely set to nothing.

 

[strongm]

It is exactly what the one-liner does do. At least, the last time I bothered running it though SoftICE (which I'll grant was some time ago, and was for VB6 rather than VBA)

I guess my mileage differs from yours, as I have never really had a problem (well, no greater than with explicit release) with implied nothingness releasing memory (except for back in the days of VB4, nearly 9 years ago)

 

[PHV]

The most common example that comes in my mind is Recordset objects in Access VBA (preventing the app from quitting ...).