How to extract file name from path?

How do I extract the filename myfile.pdf from C:\Documents\myfile.pdf in VBA?

The best way of working with files and directories in VBA for Office 2000/2003 is using the scripting library.

Create a filesystem object and do all operations using that.

Early binding:

Add a reference to Microsoft Scripting Runtime (Tools > References in the IDE).

Dim fso as new FileSystemObject
Dim fileName As String
fileName = fso.GetFileName("c:\any path\file.txt")

Late binding (see comments for more)

With CreateObject("Scripting.FileSystemObject")
    fileName = .GetFileName(FilePath)
    extName = .GetExtensionName(FilePath)
    baseName = .GetBaseName(FilePath)
    parentName = .GetParentFolderName(FilePath)
End With

The FileSystemObject is great. It offers a lot of features such as getting special folders (My documents, etc.), creating, moving, copying, deleting files and directories in an object oriented manner.

Dir("C:\Documents\myfile.pdf")

will return the file name, but only if it exists.

This is taken from snippets.dzone.com:

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
    
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

I've read through all the answers and I'd like to add one more that I think wins out because of its simplicity. Unlike the accepted answer this does not require recursion. It also does not require referencing a FileSystemObject.

Function FileNameFromPath(strFullPath As String) As String

    FileNameFromPath = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "\"))

End Function

http://vba-tutorial.com/parsing-a-file-string-into-path-filename-and-extension/ has this code plus other functions for parsing out the file path, extension and even the filename without the extension.

I can't believe how overcomplicated some of these answers are... (no offence!)

Here's a single-line function that will get the job done:

---

Function getFName(pf)As String:getFName=Mid(pf,InStrRev(pf,"\")+1):End Function

---

Function getPath(pf)As String:getPath=Left(pf,InStrRev(pf,"\")):End Function

---

Examples:

Dim sFilePath$, sFileName$
sFileName = Split(sFilePath, "\")(UBound(Split(sFilePath, "\")))

If you want a more robust solution that will give you both the full folder's path AND the filename, here it is:

Dim strFileName As String, strFolderPath As String
Dim lngIndex As Long
Dim strPath() As String

strPath() = Split(OpenArgs, "\")   'Put the Parts of our path into an array
lngIndex = UBound(strPath)
strFileName = strPath(lngIndex)    'Get the File Name from our array
strPath(lngIndex) = ""             'Remove the File Name from our array
strFolderPath = Join(strPath, "\") 'Rebuild our path from our array

Or as a sub/function:

Private Sub SeparatePathAndFile(ByRef io_strFolderPath As String, ByRef o_strFileName As String)	
	Dim strPath() As String
	Dim lngIndex As Long
	
	strPath() = Split(io_strFolderPath, "\")  'Put the Parts of our path into an array
	lngIndex = UBound(strPath)
	o_strFileName = strPath(lngIndex)   'Get the File Name from our array
	strPath(lngIndex) = ""              'Remove the File Name from our array
	io_strFolderPath = Join(strPath, "\")     'Rebuild our path from our array	
End Sub

You pass the first parameter with the full path of the file and it will be set to the folder's path while the second parameter will be set to the file's name.

Here's a simple VBA solution I wrote that works with Windows, Unix, Mac, and URL paths.

sFileName = Mid(Mid(sPath, InStrRev(sPath, "/") + 1), InStrRev(sPath, "\") + 1)

sFolderName = Left(sPath, Len(sPath) - Len(sFileName))

You can test the output using this code:

'Visual Basic for Applications 
http = "https://www.server.com/docs/Letter.txt"
unix = "/home/user/docs/Letter.txt"
dos = "C:\user\docs\Letter.txt"
win = "\\Server01\user\docs\Letter.txt"
blank = ""

sPath = unix 
sFileName = Mid(Mid(sPath, InStrRev(sPath, "/") + 1), InStrRev(sPath, "\") + 1)
sFolderName = Left(sPath, Len(sPath) - Len(sFileName))

Debug.print "Folder: " & sFolderName & " File: " & sFileName

Also see: Wikipedia - Path (computing)

To get the file name in an excel macro is:

filname = Mid(spth, InStrRev(spth, "\", Len(spth)) + 1, Len(spth))
MsgBox Mid(filname, 1, InStr(filname, ".") - 1)

The simplest approach if you are sure the file physically exists on the disk:

Dim fileName, filePath As String
filePath = "C:\Documents\myfile.pdf"
fileName = Dir(filePath)

If you are not sure about existence of file or just want to extract filename from a given path then, simplest approach is:

fileName = Mid(filePath, InStrRev(filePath, "\") + 1)

Function file_name_only(file_path As String) As String

Dim temp As Variant
temp = Split(file_path, Application.PathSeparator)
file_name_only = temp(UBound(temp))
End Function

1. here you give your file name as input of the function
2. the split function of VBA splits the path in different portion by using "" as path separator & stores them in an array named "temp"
3. the UBound() finds the max item number of array and finally assigns the result to "file_name_only" function

Hope this will be helpful.

Here's an alternative solution without code. This VBA works in the Excel Formula Bar:

To extract the file name:

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

To extract the file path:

=MID(A1,1,LEN(A1)-LEN(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))))

I am using this function... VBA Function:

Function FunctionGetFileName(FullPath As String) As String
'Update 20140210
Dim splitList As Variant
splitList = VBA.Split(FullPath, "\")
FunctionGetFileName = splitList(UBound(splitList, 1))
End Function

Now enter

=FunctionGetFileName(A1) in youe required cell.

or You can use these...

=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

I needed the path, not the filename.

So to extract the file path in code:

JustPath = Left(sFileP, Len(sFileP) - Len(Split(sFileP, "\")(UBound(Split(sFileP, "\"))))) 

This gleaned from Twiggy @ http://archive.atomicmpc.com.au and other places:

'since the file name and path were used several times in code
'variables were made public

Public FName As Variant, Filename As String, Path As String

Sub xxx()
   ...
   If Not GetFileName = 1 Then Exit Sub '
   ...
End Sub

Private Function GetFileName()
   GetFileName = 0 'used for error handling at call point in case user cancels
   FName = Application.GetOpenFilename("Ramp log file (*.txt), *.txt")
   If Not VarType(FName) = vbBoolean Then GetFileName = 1 'to assure selection was made
   Filename = Split(FName, "\")(UBound(Split(FName, "\"))) 'results in file name
   Path = Left(FName, InStrRev(FName, "\")) 'results in path
End Function

Dim nme As String = My.Computer.FileSystem.GetFileInfo(pathFicheiro).Name
Dim dirc As String = My.Computer.FileSystem.GetFileInfo(nomeFicheiro).Directory