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.

Function FullNameToFileName(sFullName As String) As String
  Dim k As Integer
  Dim sTest As String
  If InStr(1, sFullName, "[") > 0 Then
    k = InStr(1, sFullName, "[")
    sTest = Mid$(sFullName, k + 1, InStr(1, sFullName, "]") - k - 1)
  Else
    For k = Len(sFullName) To 1 Step -1
      If Mid$(sFullName, k, 1) = "\" Then Exit For
    Next k
    sTest = Mid$(sFullName, k + 1, Len(sFullName) - k)
  End If
  FullNameToFileName = sTest
End Function

Function FullNameToRootName(sFullName As String) As String
  Dim k As Integer
  Dim sTest As String
  sTest = FullNameToFileName(sFullName)
  For k = Len(sTest) To 1 Step -1
    If Mid$(sTest, k, 1) = "." Then Exit For
  Next
  sTest = Left$(sTest, k - 1)
  FullNameToRootName = sTest
End Function

Function FullNameToFileExt(sFullName As String) As String
  Dim k As Integer
  Dim sTest As String
  sTest = FullNameToFileName(sFullName)
  For k = Len(sTest) To 1 Step -1
    If Mid$(sTest, k, 1) = "." Then Exit For
  Next
  If k > 0 Then
    sTest = Right$(sTest, Len(sTest) - k)
  Else
    sTest = ""
  End If
  FullNameToFileExt = sTest
End Function

Function FullNameToPath(sFullName As String) As String
  ' does not include trailing backslash
  Dim k As Integer
  For k = Len(sFullName) To 1 Step -1
    If Mid$(sFullName, k, 1) = "\" Then Exit For
  Next k
  If k < 1 Then
    FullNameToPath = ""
  Else
    FullNameToPath = Mid$(sFullName, 1, k - 1)
  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
 

Related Posts:

• Robust VBA Save-As Technique
• Get Open or Save-As Filename
• Enhanced Export Chart Procedure
• List VBA Procedures by VBA Module and VB Procedure
• UDF to Calculate an Arbitrary Formula
• Validation Functions
• Installing an Excel Add-In
• Count Bold Cells in a Range
• Time Trials of Approaches to Measure Minimum and Maximum Chart Values
• Calculating Easter