copy

Thursday, 23 January 2014



Determine The Drive, Folder & File Existence


Introduction



The File System Object (FSO) provides access to a computer’s file system. The particular object contains 3 object collections, 4 other objects, as well as several properties and methods (see the picture below). FSO is a quite powerful object, but it should be used with caution; since it can manipulate files that might belong to the operating system, a poorly written VBA code can even cause malfunctions in the entire computer. I would suggest you take a backup of your folder/file that you need to manipulate (copy, move, delete etc.) before running any code. In any case, be careful when you working with FSO, and, especially, when you delete files.

File System Object Diagram

The section below focuses on three useful properties of the File System Object – Drive Exists, Folder Exists and File Exists – which can be used to determine the existence of a drive, folder and file respectively. I have adapted these properties into three corresponding VBA functions, which return string messages depending on whether or not the specified drive/folder/file exist.



VBA code



All function share the same approach; create the FSO object and then apply the corresponding property. The VBA code of the DriveExists function is presented first.

Option Explicit

Function DriveExists(DriveName As String) As String

    '-----------------------------------------------------------------------------
    'Determines if the specified drive exists. It returns a string message.
    'The function uses the DriveExists method of File System Object to determine
    'if the drive exists. The DriveName must be in the form: "F:\", or "F".
           
    'Written by:    Christos Samaras
    'Date:          19/01/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------
   
    'Declare the necessary variable.
    Dim FSO As Object
    
    'Without the next statement, the Find function might fail.
    On Error Resume Next
    
    'Check if the drive's name is not empty.
    If DriveName = vbNullString Then
        DriveExists = "Drive name is empty."
        Exit Function
    End If
    
    'Check if there are more than 3 characters in the drive's name.
    If Len(DriveName) > 3 Then
        DriveExists = "Long drive name."
        Exit Function
    End If
    
    'Create the File System Object.
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'Check if the FSO was created.
    If FSO Is Nothing Then
        DriveExists = "Couldn't create the FSO!"
        Exit Function
    End If
    
    'Check if there is a colon in the driver's name. If not, add it.
    If WorksheetFunction.Find(":", DriveName) <> 2 Then
        DriveName = DriveName & ":"
    End If
            
    'Check if there is a backslash in the driver's name. If not, add it.
    If WorksheetFunction.Find("\", DriveName) <> 3 Then
        DriveName = DriveName & "\"
    End If
            
    'Use the DriveExists method of FSO to determine if the drive exists.
    If FSO.DriveExists(DriveName) = True Then
        DriveExists = "Drive exists."
    Else
        DriveExists = "Drive does NOT exist."
    End If
    
    'Release the object.
    Set FSO = Nothing
    
    'Return to the normal behavior (just in case the function is called in other subs).
    On Error GoTo 0
    
End Function

The FolderExists function follows:

Option Explicit

Function FolderExists(FolderPath As String) As String

    '-----------------------------------------------------------------------------
    'Determines if the specified folder exists. It returns a string message.
    'The function uses the FolderExists method of File System Object to determine
    'if the folder exists. A valid FolderPath can be for example: "C:\Windows".
           
    'Written by:    Christos Samaras
    'Date:          19/01/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------
    
    'Declare the necessary variable.
    Dim FSO As Object

    'Check if the folder path is not empty.
    If FolderPath = vbNullString Then
        FolderExists = "Folder path is empty."
        Exit Function
    End If
    
    'Create the File System Object.
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'Check if the FSO was created.
    If FSO Is Nothing Then
        FolderExists = "Couldn't create the FSO!"
        Exit Function
    End If
                
    'Use the FolderExists method of FSO to determine if the folder exists.
    If FSO.FolderExists(FolderPath) = True Then
        FolderExists = "Folder exists."
    Else
        FolderExists = "Folder does NOT exist."
    End If
    
    'Release the object.
    Set FSO = Nothing

End Function

Finally, the FileExists function is given below.

Option Explicit

Function FileExists(FilePath As String) As String

    '----------------------------------------------------------------------------
    'Determines if the specified file exists. It returns a string message.
    'The function uses the FileExists method of File System Object to determine
    'if the file exists. A valid FilePath can be for example: "C:\Test.pdf".
           
    'Written by:    Christos Samaras
    'Date:          19/01/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '----------------------------------------------------------------------------
    
    'Declare the necessary variable.
    Dim FSO As Object

    'Check if the file path is not empty.
    If FilePath = vbNullString Then
        FileExists = "File path is empty."
        Exit Function
    End If
    
    'Create the File System Object.
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'Check if the FSO was created.
    If FSO Is Nothing Then
        FileExists = "Couldn't create the FSO!"
        Exit Function
    End If
                
    'Use the FileExists method of FSO to determine if the file exists.
    If FSO.FileExists(FilePath) = True Then
        FileExists = "File exists."
    Else
        FileExists = "File does NOT exist."
    End If
    
    'Release the object.
    Set FSO = Nothing

End Function

The above functions were demonstrated in order to give you some insights about the FSO usage. It should be highlighted, that instead of these functions, a much simpler function can be developed, which will use the Dir function. The Dir function returns a string representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive. The PathExists function that follows implements the Dir function to determine the drive, folder and file existence. Keep in mind that the PathExists function will NOT work correctly if your input is a drive letter (e.g. "G:\") that belongs to an EMPTY USB drive. In cases like this, use the DriveExists function (does not have this limitation).

Option Explicit

Function PathExists(Path As String) As String
   
    '--------------------------------------------------------------
    'Determines if a folder/file path is valid, wihtout using FSO.
    'Instead this function uses the Dir function.
    
    'Written by:    Christos Samaras
    'Date:          19/01/2014
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '--------------------------------------------------------------
    
    On Error Resume Next
    'Check if the path is not empty.
    If Not Path = vbNullString Then
        'Check if the Dir function returns an empty string. If no, the path is valid.
        If Not Dir(Path, vbDirectory) = vbNullString Then
            PathExists = "Path exists."
        Else
            'Empty string, the path is invalid.
            PathExists = "Path does NOT exist."
        End If
    Else
        'The path does not provided (blank/empty path).
        PathExists = "Path does NOT exist."
    End If
    On Error GoTo 0
   
End Function

Two different ways - one specific and one generic - were presented for determining if a drive/folder/file exists. You can choose the function that best fits your needs.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.



FSO related applications



The list below contains various applications that I have developed and were based on the objects, properties and method of the File System Object. You can download them for free:

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info