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

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" wrote in message
...
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

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!

in VB I use this:

Public FileSys As New FileSystemObject

Thanks Chaps

--
Cheers
Nigel



wrote in message
oups.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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
No, it is not available in xl97, but easy to code it yourself.

--
Regards,
Tom Ogilvy

"Nigel" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Thanks Tom, so I will need to emaulate this in xl97. What is the best
approach?

--
Cheers
Nigel



"Tom Ogilvy" wrote in message
...
No, it is not available in xl97, but easy to code it yourself.

--
Regards,
Tom Ogilvy

"Nigel" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Thanks Tom, so I will need to emaulate this in xl97. What is the best
approach?

--
Cheers
Nigel



"Tom Ogilvy" wrote in message
...
No, it is not available in xl97, but easy to code it yourself.

--
Regards,
Tom Ogilvy

"Nigel" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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