excel - How do you get just the filename rather than the entire file path of an open file?

Question

In other words, would I need to do some string processing after invoking the Application.GetOpenFileName() Method?

Answer 1

Why reinvent the wheel and write tons of boilerplate code? Just use the existing FileSystemObject's GetFileName method, already written and tested and debugged for you:

filename = FSO.GetFileName(path)

Here's a working example:

Dim path As String
Dim filename As String
Dim FSO As Scripting.FileSystemObject
Set FSO = New FileSystemObject

path = "C:mydirmyotherdirmyfile.txt"

filename = FSO.GetFileName(path) 'Bingo. Done.

Debug.Print filename ' returns "myfile.txt"

' Other features:
Debug.Print FSO.GetBaseName(path) ' myfile
Debug.Print FSO.GetExtensionName(path) ' txt
Debug.Print FSO.GetParentFolderName(path) ' C:mydirmyotherdir
Debug.Print FSO.GetDriveName(path) ' C:
' et cetera, et cetera.

You will need to set a reference as follows: Tools > References... > set checkmark next to Microsoft Scripting Runtime.

Otherwise use late binding:

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")