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 ◊