Extracting filename

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

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
 
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)
 
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?
 
Nigel,
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?
Click to expand...

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)
 
Hi Bob,
Of course - I'm just not thinking - it's been a long day, dealing with Class
Modules! (reference other posts)
 
Back
Top