Testing for the Existence of a File

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

Testing for the Existence of a File

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 ◊

EnvisionCAD Group EnvisionCAD YouTube Channel   

EnvisionCAD

Since 1996, EnvisionCAD has been a nationally recognized leader in the configuration, customization, implementation, training & support for CAD software solutions. Our individualized approach has benefited private engineering firms and government agencies alike. Basic or advanced, we can help you get the most from your CAD technology.

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *