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.
gerdami says
File Name functions should be adapted to be used either on a classic filesystem and on Sharepoint where the backslashes “\” are slashes “/” as for any http:// location.
Jon Peltier says
That’s a matter of using a constant for PathSeparator, which could be “\”, “/”, or “:” (for Macs).
Sebastian Helm says
What’s the crude bracket handling in FullNameToFileName for? “C:\myfolder\[some[convoluted]file]name.txs” gives “some[convoluted”, which is not what the blue highlighted text (nice presentation, btw!) would make one think.
Jon Peltier says
Excel treats square brackets, if they appear, as containing the workbook name.
The full path and file name for a workbook might be
C:\Users\My Account\Documents\MyWorkbook.xlsx
But if I use this formula in a cell
=CELL("filename")that cell would show
C:\Users\My Account\Documents\[MyWorkbook.xlsx]SheetName
My simple
FullNameToFileNamefunction checks for the square brackets, and assumes there are no square brackets other than possibly around the workbook name. If brackets are present, it returns all the text starting right after the first open bracket and ending right before the first close bracket.But I notice that in 2014 (this post was written in 2008) I changed my function to this:
Public Function FullNameToFileName1(sFullName As String) As StringDim k As Integer
For k = Len(sFullName) To 1 Step -1
If Mid(sFullName, k, 1) = Application.PathSeparator Then Exit For
Next k
Dim sTest As String
sTest = Mid(sFullName, k + 1, Len(sFullName) - k)
FullNameToFileName1 = sTest
End Function
But that fails to make use of
InStrRev, introduced in Excel 2000. And there’s really no need for the variablesTest.Public Function FullNameToFileName2(sFullName As String) As StringDim iPathSep As Long
iPathSep = InStrRev(sFullName, Application.PathSeparator)
FullNameToFileName2 = Mid$(sFullName, iPathSep + 1)
End Function
All of the functions have been streamlined, and the new versions now appear in this article.
Sebastian Helm says
Thank you for your thorough reply and for the update. The code looks much more elegant now, and probably has a better performance, too. Since you changed the sequence, you might want to change that of the preceding examples, as well.
Only a question about FullNameToRootName: Now FullNameToRootName(“C:\My Directory\My Documents\MyFile”) yields an empty string. Since I translate “RootName” to “NameNoExt”, I would expect it to return just “MyFile”. Why do you prefer the empty string?
Jon Peltier says
Sebastian –
FullNameToRootNameshould return the file name if there is no extension, not an empty string. My mistake. I’ve fixed it.Sebastian Helm says
Working great now, thanks!