1

I am trying to get the filename of the Excel file that is opened, however keep getting the full path plus the filename. For example, I keep getting: C:\some_dir\other_dir\another_dir\filename.xls when in reality, I just want filename out of filename.xls. The issue is that the user could go into many sub-directories so can't simply use a parsor since it may change every time. Thoughts?

strFileToOpen = Application.GetOpenFileName(Title:="Select File to Open")
Community
  • 1
  • 1
nas
  • 99
  • 1
  • 1
  • 6
  • Apart from Tim's methods, if you are going to open the file selected, then you can use something like `set wb = Workbooks.Open(strFileToOpen)` to open the file, followed by `filename = wb.Name` to get the filename without the path. – YowE3K Jul 22 '17 at 00:02
  • `Dir(strFileToOpen)` – Slai Jul 22 '17 at 00:49
  • 3
    Possible duplicate of [How to extract file name from path?](https://stackoverflow.com/questions/1743328/how-to-extract-file-name-from-path) – Slai Jul 22 '17 at 00:50

2 Answers2

3
Dim arr, strFileToOpen 

strFileToOpen = Application.GetOpenFileName(Title:="Select File to Open")
arr = Split(strFileToOpen, Application.PathSeparator)
debug.print arr(ubound(arr)) '>> filename

or (if on Windows)

Set fso = CreateObject("Scripting.FileSystemObject")
Debug.Print fso.GetFileName(strFileToOpen)

FileSystemObject has a bunch of methods for working with paths.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
2

InstrRev(strFileToOpen,"\") will return the position of the last "\". Add one to get the position of the first character of the filename.extension portion.

strFileToOpen = Application.GetOpenFileName(Title:="Select File to Open")
If Len(strFileToOpen) Then
    strFileToOpen = Mid$(strFileToOpen, InstrRev(strFileToOpen,"\")+1)
End If
thx1138v2
  • 566
  • 3
  • 6