Get file name or path

Tested in Excel 365 (16.8730.2046) 64-bit

 
 
  1. Public Function GetFileNameOrPath(FilePath As String, Optional FileOrFolder As String = "File") As String
  2. ' FileOrFolder should be: file, folder or missing
  3. ' default return is file name
  4.    Dim FolderPath As String, FileName As String
  5.    
  6.     FolderPath = Left(FilePath, InStrRev(FilePath, "\") - 1)
  7.     FileName = Right(FilePath, Len(FilePath) - Len(FolderPath) - 1)
  8.    
  9.     If UCase(FileOrFolder) = "FILE" Then
  10.         GetFileNameOrPath = FileName
  11.     Else
  12.         GetFileNameOrPath = FolderPath
  13.     End If
  14. End Function
  15. ' use it like this:
  16. Sub main()
  17.     Dim myPath As String
  18.     myPath = GetFileNameOrPath("C:\datafile.csv", "folder")
  19.     Debug.Print myPath
  20. End Sub

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.