How to extract file name from path?
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 FileSystemObjectDim fileName As StringfileName = 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.
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 IfEnd Function