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.
