Written by Rod Wing.
Many times in a macro you will need to determine if a file exists prior to opening it in an application, or using the Open function for your own text read/write operations. There is no built-in “does file exist” function in VBA so what do you do?
Create your own function
One simple solution is to create your own FileExists function. All you need to do is find a VBA function that requires an existing file and use VBA’s error handling capability. The example below uses the VBA GetAttr function. If the file does not exist the GetAttr function raises an error which we can trap and return false.
Public Function FileExists(fileName As String) As Boolean
On Error GoTo FILE_NOT_FOUND
GetAttr fileName
FileExists = True
Exit Function
FILE_NOT_FOUND:
FileExists = False
End Function
Reference the Windows Script Host Object Model
Another option is to use the FileSystemObject which is available in the Windows Script Host Object Model. To use this you will first need to add a reference to the object model.
1. From the VBA menu bar select Tools->References
2. In the References dialog scroll down near the bottom of the list of available references and select the Windows Script Host Object Model
3. Click on the OK button
Once the reference is added you can use the FileSystemObject object type.
Public Function FileExists2(fileName As String) As Boolean
Dim fso As FileSystemObject
Set fso = New FileSystemObject
FileExists2 = fso.FileExists(fileName)
Set fso = Nothing
End Function
Why bother with the FileSystemObject?
The FileSystemObject object type does much more than just test for the existence of a file. Use the VBA Object Browser (function key F2) to view what additional features are available. You will find that it has methods for copying, moving, and deleting both files and folders. It also has methods for extracting the folder name, base file name, and file extension from a full file name specification.
To illustrate some of what you can do take a look at the following macro. This macro will move a file to a sub folder named with the file’s extension. It tests for the existence of the file, then extracts the path, base name, and file extension. It will then create a sub folder with the name of the file extension, then move the file to that folder.
Sub MyMacro()
Dim fso As FileSystemObject
Dim fullFileName As String
Dim baseName As String
Dim fileExt As String
Dim pathName As String
Dim newPathName As String
Dim newFileName As String
Set fso = New FileSystemObject
'
' do something here to set fullFileName
'
fullFileName = "C:\Users\Test\MyData.txt"
'
With fso
If .FileExists(fullFileName) Then
pathName = .GetParentFolderName(fullFileName)
fileExt = .GetExtensionName(fullFileName)
baseName = .GetBaseName(fullFileName)
newPathName = pathName & "\" & fileExt
newFileName = newPathName _
& "\" & baseName & "." & fileExt
If Not .FolderExists(newPathName) Then
.CreateFolder newPathName
End If
If .FileExists(newFileName) Then
.DeleteFile newFileName, True
End If
.MoveFile fullFileName, newFileName
End If
End With
Set fso = Nothing
End Sub
Once you get comfortable with using the FileSystemObject you will be using it in many of your VBA macros.
In addition the Windows Script Host Object Model has additional objects for working with files, folders, and drives. Use of these additional objects will be covered in future tips
Last Month’s VBA Tip – Double Quote Characters in String Variables
Don’t want to miss out on other great information? Subscribe to this blog or our monthly eNewsletter now!
Learn More ◊ Contact us today ◊ Newsletter ◊
