File name from string without extension

[patriciaxxx]

This must be simple enough but I am going round in circles getting nowhere

I want a SINGLE LINE EXPRESSION to return the file name from a string without the extension

This is my code

Function myfilename()
Dim varFileName As String
varFileName = "C:\Documents and Settings\New Folder\index.html"

varFileName = Right(varFileName, Len(varFileName) - InStrRev(varFileName, "\"))
varFileName = Left(varFileName, InStrRev(varFileName, ".") - 1)

Debug.Print varFileName
End Function

It does work and returns the file name
index

but the expression for varFileName is on 2 rows
I need varFileName = just one line here so those two expressions need to be combine into one

 

[PHV]

= Left(Mid(varFileName, InStrRev(varFileName, "\") + 1), InStrRev(Mid(varFileName, InStrRev(varFileName, "\") + 1), ".") - 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[patriciaxxx]

Very helpful
Thank you
Just what I needed

 

[strongm]

Or, for fun:

[blue]    Dim varFileName As String
    varFileName = "C:\Documents and Settings\New Folder\index.html"
    Debug.Print CreateObject("scripting.filesystemobject").GetBaseName(varFileName)[/blue]

 

[vbajock]

Dim MyArray() as string
Dim extension as string

MyArray = Split(SomefileName, ".")
extension = MyArray(UBound(MyArray)-1)

 

[strongm]

That unfortunately only works as written if:

a) by extension you didn't mean extension; and
b) if you are passed a filename, rather than a file path (which is what the OP has indicated)

 

[vbajock]

It gets you the extension, whether you have a path or not is immaterial. You can use len() from there to get the rest of it if you know what your doing

 

[strongm]

No , it does not. Not as written.

You need to change

extension = MyArray(UBound(MyArray) - 1)

to

extension = MyArray(UBound(MyArray))

in order to meet your stated goals (which are not what the OP wanted).