string - How to extract file name from path?

Question

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

Answer 1

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