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

Use something like this

=InstrRev(myWB.name,".")

HTH,
Barb Reinhardt

"Michelle" wrote:

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

Try these 2 statements in the immediate pane of any workbook that's been
saved:

?mid(thisworkbook.Name,instrrev(thisworkbook.Name, "."))
?left(thisworkbook.Name,instrrev(thisworkbook.Name ,".")-1)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134200

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

M


"p45cal" wrote in message
...

Try these 2 statements in the immediate pane of any workbook that's been
saved:

?mid(thisworkbook.Name,instrrev(thisworkbook.Name, "."))
?left(thisworkbook.Name,instrrev(thisworkbook.Name ,".")-1)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=134200

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" wrote:

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

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)




On Sun, 13 Sep 2009 12:17:04 +0100, "Michelle"
wrote:

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