Extracting filename

Hi All
In a file open dialog I get the full file path and filename and extension.
eg

C:\mydocumentspath\subpath\myfile.xls

What is the best method to extract the filename extension only eg

myfile.xls

--
Cheers
Nigel

To get myfile.xls use:-
FileNm = activeWorkbook.Name

If it's the file extention you want (ie xls):-
Extn = Right(ActiveWorkbook.Name, 3)

Hope those work as haven't tryed them!

Use the instrRev function to get the last \

sFile = "C:\mydocumentspath\subpath\myfile.xls"

iPos = InStrRev(sFile, "\")
If iPos > 0 Then
Debug.Print Right(sFile, Len(sFile) - iPos)
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel" <nigel-sw@suxnospampanet.com> wrote in message
news:uHoMv97LFHA.3076@TK2MSFTNGP14.phx.gbl...
> Hi All
> In a file open dialog I get the full file path and filename and extension.
> eg
>
>
>
> What is the best method to extract the filename extension only eg
>
> myfile.xls
>
> --
> Cheers
> Nigel
>
>
>
>

in VB I use this:

Public FileSys As New FileSystemObject

Thanks Chaps

--
Cheers
Nigel



<donna.gough@hydro.com> wrote in message
news:1111593314.124914.268090@z14g2000cwz.googlegroups.com...
> To get myfile.xls use:-
> FileNm = activeWorkbook.Name
>
> If it's the file extention you want (ie xls):-
> Extn = Right(ActiveWorkbook.Name, 3)
>
> Hope those work as haven't tryed them!
>

Hi Bob,
I was toying with instr but did not know about the reverse version. Do you
know if this exists in xl97 - I am developing in xl2002 but the final
product will run in xl97 and at present I cannot test it?

--
Cheers
Nigel



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23HjCuC8LFHA.508@TK2MSFTNGP12.phx.gbl...
> Use the instrRev function to get the last \
>
> sFile = "C:\mydocumentspath\subpath\myfile.xls"
>
> iPos = InStrRev(sFile, "\")
> If iPos > 0 Then
> Debug.Print Right(sFile, Len(sFile) - iPos)
> End If
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> news:uHoMv97LFHA.3076@TK2MSFTNGP14.phx.gbl...
> > Hi All
> > In a file open dialog I get the full file path and filename and
extension.
> > eg
> >
> >
> >
> > What is the best method to extract the filename extension only eg
> >
> > myfile.xls
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> >
>
>

No, it is not available in xl97, but easy to code it yourself.

--
Regards,
Tom Ogilvy

"Nigel" <nigel-sw@suxnospampanet.com> wrote in message
news:OvJW7G8LFHA.2420@TK2MSFTNGP12.phx.gbl...
> Hi Bob,
> I was toying with instr but did not know about the reverse version. Do
you
> know if this exists in xl97 - I am developing in xl2002 but the final
> product will run in xl97 and at present I cannot test it?
>
> --
> Cheers
> Nigel
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%23HjCuC8LFHA.508@TK2MSFTNGP12.phx.gbl...
> > Use the instrRev function to get the last \
> >
> > sFile = "C:\mydocumentspath\subpath\myfile.xls"
> >
> > iPos = InStrRev(sFile, "\")
> > If iPos > 0 Then
> > Debug.Print Right(sFile, Len(sFile) - iPos)
> > End If
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> > news:uHoMv97LFHA.3076@TK2MSFTNGP14.phx.gbl...
> > > Hi All
> > > In a file open dialog I get the full file path and filename and
> extension.
> > > eg
> > >
> > >
> > >
> > > What is the best method to extract the filename extension only eg
> > >
> > > myfile.xls
> > >
> > > --
> > > Cheers
> > > Nigel
> > >
> > >
> > >
> > >
> >
> >
>
>

Thanks Tom, so I will need to emaulate this in xl97. What is the best
approach?

--
Cheers
Nigel



"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:eE80UM8LFHA.2420@TK2MSFTNGP12.phx.gbl...
> No, it is not available in xl97, but easy to code it yourself.
>
> --
> Regards,
> Tom Ogilvy
>
> "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> news:OvJW7G8LFHA.2420@TK2MSFTNGP12.phx.gbl...
> > Hi Bob,
> > I was toying with instr but did not know about the reverse version. Do
> you
> > know if this exists in xl97 - I am developing in xl2002 but the final
> > product will run in xl97 and at present I cannot test it?
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:%23HjCuC8LFHA.508@TK2MSFTNGP12.phx.gbl...
> > > Use the instrRev function to get the last \
> > >
> > > sFile = "C:\mydocumentspath\subpath\myfile.xls"
> > >
> > > iPos = InStrRev(sFile, "\")
> > > If iPos > 0 Then
> > > Debug.Print Right(sFile, Len(sFile) - iPos)
> > > End If
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> > > news:uHoMv97LFHA.3076@TK2MSFTNGP14.phx.gbl...
> > > > Hi All
> > > > In a file open dialog I get the full file path and filename and
> > extension.
> > > > eg
> > > >
> > > >
> > > >
> > > > What is the best method to extract the filename extension only eg
> > > >
> > > > myfile.xls
> > > >
> > > > --
> > > > Cheers
> > > > Nigel
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Nigel,

Nigel wrote:
> Hi Bob,
> I was toying with instr but did not know about the reverse version.
> Do you know if this exists in xl97 - I am developing in xl2002 but
> the final product will run in xl97 and at present I cannot test it?

No, InStrRev was introduced in Excel 2000.

Here's an example using the FileSytemObject:

Public Function gsGetFileName(rsPath As String) As String
Dim fso As Object

On Error GoTo ErrHandler

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(rsPath) Then gsGetFileName = _
fso.GetFileName(rsPath)

ExitRoutine:
Set fso = Nothing
Exit Function
ErrHandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitRoutine
End Function


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Just write a loop from the right backwards

For iPos = Len(sFile) to 1
If mid(sFil;e,iPos,1) = "\" Then
Exit For
End If
Next iPos

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Nigel" <nigel-sw@suxnospampanet.com> wrote in message
news:eoGp5P8LFHA.1948@TK2MSFTNGP14.phx.gbl...
> Thanks Tom, so I will need to emaulate this in xl97. What is the best
> approach?
>
> --
> Cheers
> Nigel
>
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:eE80UM8LFHA.2420@TK2MSFTNGP12.phx.gbl...
> > No, it is not available in xl97, but easy to code it yourself.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> > news:OvJW7G8LFHA.2420@TK2MSFTNGP12.phx.gbl...
> > > Hi Bob,
> > > I was toying with instr but did not know about the reverse version.
Do
> > you
> > > know if this exists in xl97 - I am developing in xl2002 but the final
> > > product will run in xl97 and at present I cannot test it?
> > >
> > > --
> > > Cheers
> > > Nigel
> > >
> > >
> > >
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:%23HjCuC8LFHA.508@TK2MSFTNGP12.phx.gbl...
> > > > Use the instrRev function to get the last \
> > > >
> > > > sFile = "C:\mydocumentspath\subpath\myfile.xls"
> > > >
> > > > iPos = InStrRev(sFile, "\")
> > > > If iPos > 0 Then
> > > > Debug.Print Right(sFile, Len(sFile) - iPos)
> > > > End If
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> > > > news:uHoMv97LFHA.3076@TK2MSFTNGP14.phx.gbl...
> > > > > Hi All
> > > > > In a file open dialog I get the full file path and filename and
> > > extension.
> > > > > eg
> > > > >
> > > > >
> > > > >
> > > > > What is the best method to extract the filename extension only eg
> > > > >
> > > > > myfile.xls
> > > > >
> > > > > --
> > > > > Cheers
> > > > > Nigel
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Hi Bob,
Of course - I'm just not thinking - it's been a long day, dealing with Class
Modules! (reference other posts)

--
Cheers
Nigel



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:utmR6h8LFHA.3424@TK2MSFTNGP12.phx.gbl...
> Just write a loop from the right backwards
>
> For iPos = Len(sFile) to 1
> If mid(sFil;e,iPos,1) = "\" Then
> Exit For
> End If
> Next iPos
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> news:eoGp5P8LFHA.1948@TK2MSFTNGP14.phx.gbl...
> > Thanks Tom, so I will need to emaulate this in xl97. What is the best
> > approach?
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:eE80UM8LFHA.2420@TK2MSFTNGP12.phx.gbl...
> > > No, it is not available in xl97, but easy to code it yourself.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> > > news:OvJW7G8LFHA.2420@TK2MSFTNGP12.phx.gbl...
> > > > Hi Bob,
> > > > I was toying with instr but did not know about the reverse version.
> Do
> > > you
> > > > know if this exists in xl97 - I am developing in xl2002 but the
final
> > > > product will run in xl97 and at present I cannot test it?
> > > >
> > > > --
> > > > Cheers
> > > > Nigel
> > > >
> > > >
> > > >
> > > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > > news:%23HjCuC8LFHA.508@TK2MSFTNGP12.phx.gbl...
> > > > > Use the instrRev function to get the last \
> > > > >
> > > > > sFile = "C:\mydocumentspath\subpath\myfile.xls"
> > > > >
> > > > > iPos = InStrRev(sFile, "\")
> > > > > If iPos > 0 Then
> > > > > Debug.Print Right(sFile, Len(sFile) - iPos)
> > > > > End If
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Nigel" <nigel-sw@suxnospampanet.com> wrote in message
> > > > > news:uHoMv97LFHA.3076@TK2MSFTNGP14.phx.gbl...
> > > > > > Hi All
> > > > > > In a file open dialog I get the full file path and filename and
> > > > extension.
> > > > > > eg
> > > > > >
> > > > > >
> > > > > >
> > > > > > What is the best method to extract the filename extension only
eg
> > > > > >
> > > > > > myfile.xls
> > > > > >
> > > > > > --
> > > > > > Cheers
> > > > > > Nigel
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>