How to extract file name from path?

Question

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

Try using the **split** function to get the file name from the path: [MSDN link](http://msdn.microsoft.com/pt-br/library/6x627e5f.aspx) — jonaspp, Nov 16 '09 at 16:44

score 154 · Answer 1 · edited Apr 01 '21 at 09:35

154

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.

edited Apr 01 '21 at 09:35

ZygD

22,092
39
79
102

answered Nov 18 '09 at 12:13

Zen

1,928
3
12
17

12

In Excel VBA, `Dim fileName As String; Dim fso as Object; Set fso = CreateObject("Scripting.FileSystemObject"); fileName = fso.GetFilename(path);` – IceArdor Apr 27 '15 at 23:46
5

A word of warning, if you get a "User-defined type not defined" error you need to set a reference to the VB script run-time library. Load the VB Editor (ALT+F11), Select Tools > References from the drop-down menu, and from the list of available references tick the check box next to 'Microsoft Scripting Runtime' and click OK. – Ian Stanway Nov 06 '15 at 12:21
2

Not a good solution : it needs a dependence extern to VBA. The code sill not work in all VBA environment. – Skrol29 Nov 06 '15 at 15:46
2

@Skrol29 every single Windows box in the world includes that library. Nothing to worry about. Accepted answer won't work on a Mac either. People that systematically late-bind the Scripting runtime don't know what they're doing, nor why. This answer is perfectly acceptable, and *does* beat the accepted answer by a mile. Or two. – Mathieu Guindon Jul 13 '17 at 20:00
4

@IceArdor Late-binding the Scripting Runtime buys you exactly nothing. You get a performance penalty for resolving the calls at run-time, and zero compile-time checks which means you make a typo and you won't know about it until you run the code, which will happily compile. Don't late-bind a library that's standard delivery on every single Windows machine since Win98. – Mathieu Guindon Jul 13 '17 at 20:05
@Mat'sMug Late-binding also sacrifices access to the named enums and constants that are often required in method calls. The one thing that does need to change in this answer is the use of `Dim fso As New FileSystemObject` to be 2 statements: `Dim fso As FileSystemObject : Set fso = new FileSystemObject`. Although I'm guilty of using the FSO so frequently, that I sometimes just declare a global variable as `Dim FSO As New FileSystemObject`, and then `FSO` can be used like a predeclared class, anywhere in the project. – ThunderFrame Jul 15 '17 at 13:33
@Ian Stanway In Excel VBA, the solution IceArdor proposes works for me without checking the Microsoft Scripting Runtime you propose – FlorianH Apr 04 '20 at 20:06

Dick Kusleika · Answer 2 · 2013-10-05T13:29:03.047

57

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

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

edited Oct 05 '13 at 13:29

answered Nov 22 '09 at 13:53

Dick Kusleika

32,673
4
52
73

5

Bad because it assumes that the path is for a physical file that the program can read. – Skrol29 Nov 06 '15 at 15:41
hi @Skrol29, could you please help to explain more on this? – Harry Duong Dec 17 '15 at 00:21
@HarryDuong `Dir` returns the file name, but only if the file actually exists at that location. If the file does not exist, `Dir` return an empty string. If you know the file exists, such as when you prompt the user to select a file, then this is a quick and easy way to get the file name. If you don't know whether the file exists, `Split` from @ArturPiwkowski is better. Two `Splits` will be faster than any string manipulation. – Dick Kusleika Dec 17 '15 at 16:48
@Harry Duong: The function Dir() does perform a scan on the computer to check the given mask. In this examples, the mask is the name of the expected file. If the file does not exist on the computer, the Dir() returns an empty value. But here is many cases when you what to extract the name from a path that does not physically exists. For example for a path of a file to be created, or a file just deleted before. – Skrol29 Dec 22 '15 at 16:08

score 48 · Accepted Answer · answered Nov 16 '09 at 16:43

48

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

answered Nov 16 '09 at 16:43

Gonzalo

20,805
3
75
78

1

Correct, but what if you're on a Mac? Unfortunately this is not a cross platform solution.. – Thomas Fankhauser Feb 24 '13 at 14:01
1

Then it's broken on windows, right? I already figured it out to use `Application.PathSeparator`, but unfortunately on OSX VBA even returns paths in the old `:`-notation. I had to write a function that converts those paths. – Thomas Fankhauser Feb 25 '13 at 17:07
6

This works, but is needlessly recursive, especially for VBA (which does not convert tail-recursion to a loop automatically). – Mary Jul 19 '13 at 18:58
5

IMHO, http://stackoverflow.com/a/1755577/481207 by Zen is a better answer. Code reuse, no recursion, no embedded constants. – Matt Jun 28 '15 at 21:33
@ThomasFankhauser it's an old comment but I'm confused: Why would you expect a cross platform solution? (plus currently [less than 1 in 5](https://insights.stackoverflow.com/survey/2018/#technology) [so] users are on Mac's) – ashleedawg Apr 25 '18 at 01:19
@ashleedawg Because there is no point in having a OS aware `Application.PathSeparator`, if it has the wrong value, and VBA runs cross platform. – Thomas Fankhauser Apr 26 '18 at 07:42

score 41 · Answer 4 · answered Oct 05 '13 at 01:01

41

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.

answered Oct 05 '13 at 01:01

user2780436

583
5
9

3

The best for me : smart, short and does off-road. – Skrol29 Nov 06 '15 at 15:51
5

+1 for simplicity - can even forego the function, and use the "=" line as-is, or in a loop, for multiple files. – tahwos Oct 03 '16 at 03:45
2

I prefer this one over any of the other answers. It's easily reusable in any other sub or function and doesn't require any fiddling with references – mattlore Sep 13 '17 at 17:53
This is great because you can use it in constrained scripting environments (RPA in my case). – charles ross Jul 27 '21 at 14:37

score 15 · Answer 5 · answered Apr 25 '18 at 04:40

15

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:

$**Extract Filename from <code>x:\path\filename</code>:**$

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

$**Extract Path from <code>x:\path\filename</code>:**$

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

Examples:

answered Apr 25 '18 at 04:40

ashleedawg

20,365
9
72
105

I don't like the style of a single line function unless you are attempting Code Golf. I was looking for a method I could embed into a SQL query and your code (without the function) was the simplest: `Left(pf,InStrRev(pf,"\"))` This takes advantage of the third parameter of `mid()` being optional. – Ben Aug 01 '19 at 19:02

score 10 · Answer 6 · edited Feb 08 '13 at 15:42

10

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

edited Feb 08 '13 at 15:42

Jon Egerton

40,401
11
97
129

answered Feb 08 '13 at 15:24

Artur Piwkowski

117
1
2

Not so smart because it run twice `Split(sFilePath, "\")`. – Skrol29 Nov 06 '15 at 15:39

score 5 · Answer 7 · edited Sep 26 '12 at 11:26

5

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)

edited Sep 26 '12 at 11:26

Florent

12,310
10
49
58

answered Sep 19 '12 at 14:17

niki

59
1
1

score 5 · Answer 8 · answered Dec 04 '12 at 17:15

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.

score 5 · Answer 9 · answered Oct 13 '13 at 16:58

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)

score 5 · Answer 10 · answered Mar 23 '17 at 05:46

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)

score 4 · Answer 11 · answered Nov 11 '18 at 10:42

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

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

Hope this will be helpful.

live-love · Answer 12 · 2014-03-04T20:39:13.167

2

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

edited Mar 04 '14 at 20:39

answered Aug 23 '13 at 14:32

live-love

48,840
22
240
204

score 1 · Answer 13 · edited Jun 15 '19 at 08:14

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

score 0 · Answer 14 · edited Oct 03 '13 at 18:38

0

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

edited Oct 03 '13 at 18:38

Darwind

7,284
3
49
48

answered Oct 03 '13 at 18:08

Doug

1

Dan · Answer 15 · 2014-10-03T06:47:20.517

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

score -1 · Answer 16 · answered Aug 25 '15 at 09:36

-1

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

answered Aug 25 '15 at 09:36

Paulos02

163
1
4

Although your answer may work for the question, could you please add some explanation of what is doing? – Ivan Aug 25 '15 at 12:00
This is better solution. It extracs filename and directory from given path pathFicheiro. There is no need for nomeFicheiro as you could just use pathFicheiro. pathFicheiro = FILENAME variable – user1054844 Nov 15 '16 at 09:25
Sorry but unless I'm missing something, this is [tag:.NET], so it doesn't answer the [tag:VBA] question. – ashleedawg Apr 25 '18 at 04:44

How to extract file name from path?

16 Answers16

Examples:

Linked

Related