[string] How to extract file name from path?

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

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)

The best way of working with files and directories in VBA for Office 2000/2003 is using the scripting library. Add a reference to Microsoft Scripting Runtime (Tools > References in the IDE).

Create a filesystem object and do all operations using that.

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

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. Check it out.

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))

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

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)

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.

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

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

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)

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 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 nme As String = My.Computer.FileSystem.GetFileInfo(pathFicheiro).Name
Dim dirc As String = My.Computer.FileSystem.GetFileInfo(nomeFicheiro).Directory

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

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, "\"))))) 

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.