filename

Recently I posted several VBA procedures for exporting charts and other purposes. These make use of a number of procedures that I use when manipulating file names. I inadvertently left these out of the posts, so I am including them here.

The following shows what the functions return when passed a file name and path of “C:\My Directory\My Documents\MyFile.abc”

• FullNameToFileName
  C:\My Directory\My Documents\MyFile.abc
   
• FullNameToRootName
  C:\My Directory\My Documents\MyFile.abc
  Does not include “dot”
   
• FullNameToExt
  C:\My Directory\My Documents\MyFile.abc
  Does not include “dot”
   
• FullNameToPath
  C:\My Directory\My Documents\MyFile.abc
  Does not include trailing backslash

I also include a couple procedures “borrowed” from Karl Peterson, one-time Microsoft Classic VB MVP, to test for the presence of a file or directory. They differ from most similar functions in that they to not rely on Dir, and so do not affect and are unaffected by other procedures which may be in the process of an extensive loop using Dir.

And now, the procedures.

Public Function FullNameToFileName(sFullName As String) As String
  Dim iPathSep As Long
  iPathSep = InStrRev(sFullName, Application.PathSeparator)
  FullNameToFileName = Mid$(sFullName, iPathSep + 1)
End Function

Public Function FullNameToPath(sFullName As String) As String
  ''' does not include trailing backslash
  Dim iPathSep As Long
  iPathSep = InStrRev(sFullName, Application.PathSeparator)
  FullNameToPath = Left$(sFullName, iPathSep - 1)
End Function

Function FullNameToRootName(sFullName As String) As String
  Dim sFileName As String
  sFileName = FullNameToFileName(sFullName)
  Dim iExtDot As Long
  iExtDot = InStrRev(sFileName, ".")
  If iExtDot > 0 Then
    FullNameToRootName = Left$(sFileName, iExtDot - 1)
  Else
    FullNameToRootName = sFileName
  End If
End Function

Function FullNameToFileExt(sFullName As String) As String
  Dim iExtDot As Long
  iExtDot = InStrRev(sFullName, ".")
  If iExtDot > 0 Then
    FullNameToFileExt = Mid$(sFullName, iExtDot + 1)
  Else
    FullNameToFileExt = vbNullString
  End If
End Function

Function FileExists(ByVal FileSpec As String) As Boolean
  ' Karl Peterson MS VB MVP
  Dim Attr As Long
  ' Guard against bad FileSpec by ignoring errors
  ' retrieving its attributes.
  On Error Resume Next
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExists = Not ((Attr And vbDirectory) = vbDirectory)
  End If
End Function

Function DirExists(ByVal FileSpec As String) As Boolean
  ' Karl Peterson MS VB MVP
  Dim Attr As Long
  ' Guard against bad FileSpec by ignoring errors
  ' retrieving its attributes.
  On Error Resume Next
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    DirExists = (Attr And vbDirectory) = vbDirectory
  End If
End Function

Note: Functions updated 16 January 2023.