Separate Filename and Extension

[Michelle]

I'd like to be able to separate the extensions from filenames, do I have to
find the last '.' ? If so what's the best way?

In the back of my mind there is a statement that reurns the filename with
out the extension - or did I dream that?

Thanks

M

 

[Barb Reinhardt]

Use something like this

=InstrRev(myWB.name,".")

HTH,
Barb Reinhardt

 

[Joel]

You can use a scripting object which automatically does it for you, but it is
easier doing it like thiis. Find the last slash in the string

folder = left(FName,instrrev(FName,"\") - 1)
BaseName = mid(FName,instrrev(FName,"\") + 1)

 

[Michelle]

Thanks guys, I didn't know about instrrev...

M

 

[Chip Pearson]

Try something like the following:

Dim FName As String
Dim ExcludeDot As Boolean
Dim Extension As String
FName = "C:\Test\book2.xlsm"
ExcludeDot = True
Extension = Mid(FName, InStrRev(FName, ".") + Abs(ExcludeDot))
Debug.Print Extension

If ExcludeDot is True the extension will not contain the leading
period. If ExcludeDot is False, the extension will begin with the
leading dot. It is assume that FName does in fact have an extension.

In my standard code library, I use code like the following:

Type TFileNameStruct
Root As String
Path As String
File As String
Extension As String
End Type

Sub GetFileNameStruct(ByVal FileName As String, _
ByRef TStruct As TFileNameStruct)
Dim N As Long
If StrComp(Left(FileName, 2), "\\", vbBinaryCompare) = 0 Then
TStruct.Root = Left(FileName, _
InStr(3, FileName, "\") - 1)
Else
TStruct.Root = Left(FileName, _
InStr(1, FileName, ":", vbBinaryCompare))
End If

N = InStrRev(FileName, "\")
TStruct.Path = Left(FileName, N - 1)
TStruct.File = Mid(FileName, N + 1)
N = InStrRev(FileName, ".")
If N > 0 Then
TStruct.Extension = Mid(FileName, N)
End If
End Sub

I then call this code with something like

Sub Test()
Dim TStruct As TFileNameStruct
Dim FileName As String
FileName = "C:\Test\Sub\Book1.xlsm"
GetFileNameStruct FileName, TStruct
With TStruct
Debug.Print .Root, .Path, .File, .Extension
End With
End Sub





Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)