Separate Filename and Extension



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?





Barb Reinhardt

Use something like this


Barb Reinhardt


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)



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)
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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Get Filename without the extension 9
Separating workbook name from extension 2
FileName 2
filename 1
Finding File Extension from Full Filename (VBA) (2007) 2
filename 2
& Filename 1
% in Filename 6