extract file name from path

[jguscs]

Is there a special function for extracting the name of the file from it's path and extension?
(Or do I have to create a loop and check for instances of special characters such as "\" and "." in relation to their expected placement?)
For example, I want to extract the name of the file "FILE" from the path:
C:\Windows\Desktop\FILE.xls

 

[Nero]

Dim fs, f, s
Dim StrLen As Integer

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(Your File)
s = f.Name
Debug.Print (s)

StrLen = Len(Your File)
StrLen = StrLen - 4
s = Left(YourFile,StrLen)
    
Set fs = Nothing
Set f = Nothing

 

[jfgambit]

I use the following, store it in a Uilities Module and then call it from a Query or Form:

Public Function GetFileNameOnly(ByVal CompletePath As String) As String

On Error GoTo Proc_Err

GetFileNameOnly = Right(CompletePath, Len(CompletePath) - InStrRev(CompletePath, "\", Len(CompletePath)))

Proc_Exit:
Exit Function

Proc_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
Resume Proc_Exit

End Function

HTH

 

[dcx693]

jfgambit, as I recently discovered, the InstrRev() function was introduced in Access 2000, so this function won't work in versions 97 and previous.

 

[jfgambit]

dcx...

Yes...but I did see where the user was using 97...

But in case anyone else searches on this topic..

NOTE: THIS WORKS IN ACCESS 2000

 

[jguscs]

Thank you very much for your assistance, everyone.
(I am using 2000.)
The InstrRev() function was the one I was hoping would be included in Access-VB, but I had only heard of InStr() at the time.

 

[dcx693]

jfgambit, I just pointed it out for information. I suggested the InstrRev function to someone in another thread and he was using Access 97.

 

[jfgambit]

dcx...

No problem...I should have caught that in the first place...

good thing jguscs was using 2000

 

[jguscs]

by the way (I'm using jfgambit's method because... I can understand it.), I had to add the following code on to get rid of the extension:

'gets rid of path
File = Right(FilePath, Len(FilePath) - InStrRev(FilePath, "\", Len(FilePath)))

'gets rid of extension
File = Left(File, InStr(1, File, ".") - 1)

 

[Daxton A.]

HOW TO DO THIS IN 97

'****************************************************
'Get the Name of the File Used (includes extension)
'****************************************************
Sub GetFileName(ByRef theFile As String)

flag = 1
Do

If (InStr(theFile, "\")) = 0 Then
flag = 0
Else
theFile = Right(theFile, Len(theFile) - (InStr(theFile, "\")))
End If

Loop Until flag = 0

End Sub

 

[dcx693]

Daxton A., very nicely done. It just seemed a bit wordy to me, so I shortened it and made it into a function:

Function GetFileName(strFullname As String)
    Do Until (InStr(strFullname, "\")) = 0
        strFullname = Right(strFullname, Len(strFullname) - (InStr(strFullname, "\")))
    Loop
    GetFileName = strFullname
End Function

 

[Daxton A.]

Cool