Wsrtjtyk

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 pathfile.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.

Dir("C:Documentsmyfile.pdf")

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

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.

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.

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

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:userdocsLetter.txt"

win = "\Server01userdocsLetter.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 simplest approach if you are sure the file physically exists on the disk:

Dim fileName, filePath As String

filePath = "C:Documentsmyfile.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 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

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.

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

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