VBA File Functions: Get Folder Type and Date Modified in VB6

Looking for simple VBA file functions to work with folders and files? These two VBA file functions will help you determine whether a path is a file or folder and retrieve the date it was last modified. These utility functions are perfect for VBA developers working with file system operations in Excel, Access, or standalone VB6 applications.

VBA File Functions: Identifying Files vs. Folders

The first VBA file function, getType, takes a path as input and returns whether it’s a file, folder, or if an error occurred. This is essential when you need to perform different operations based on the path type.

Function getType(ByRef path As String) As String
    On Error Resume Next
    Dim res As Long
    res = GetAttr(path)
    If Err.Number = 0 Then
        If res And vbDirectory Then
            getType = "folder"
        Else
            getType = "file"
        End If
    Else
        getType = "error"
    End If
End Function

The function uses the GetAttr method to retrieve file attributes and checks if the vbDirectory flag is set. This approach is more efficient than other methods since it uses native VB functions.

VBA File Functions: Getting Last Modified Date

The second VBA file function, getDate, returns the date a file or folder was last modified. It first determines the type using our getType function, then uses the appropriate method to retrieve the date.

Function getDate(ByRef path As String) As Double
    Select Case getType(path)
        Case "file"
            getDate = CreateObject("scripting.filesystemobject").getfile(path).datelastmodified
        Case "folder"
            getDate = CreateObject("scripting.filesystemobject").getfolder(path).datelastmodified
    End Select
End Function

This function leverages the FileSystemObject from the Scripting Runtime library to access file and folder properties. The date is returned as a Double, which is the native VBA date format.

Practical Applications of These VBA File Functions

These VBA file functions can be used in many scenarios:

• Batch processing files in a directory
• Creating file inventory systems
• Implementing file backup solutions
• Validating file paths before operations

When working with file system operations in VBA, always include error handling to prevent your application from crashing when encountering invalid paths or permission issues.

Additional Resources for VBA File Handling

For more comprehensive file handling in VBA, check out these external resources:

• Microsoft’s FileSystemObject Documentation
• Excel Easy’s VBA Files and Folders Tutorial
• MrExcel Forum for VBA File Handling Questions

These VBA file functions are simple yet powerful tools to add to your VBA development toolkit. They demonstrate how to efficiently work with the file system in VBA and VB6 applications.