TechBookReport logo

File Attributes In VBA


By Pan Pantziarka


File Attributes

Previous articles on TechBookReport have shown how to use the FileSystemObject API from within VBA to access files, directories, text streams and so on. One area not explored in those tutorials was how access to file attributes - in other words how to identify whether a file is flagged by the operating system as hidden, or as a system file and so on. This short how-to will show how to access the information using the FileSystemObject (FSO) API, with Excel as the application platform - though the core of the code will work in any of the applications that support Visual Basic.

If you're unfamiliar with FSO then the first thing to do is to go to the start of the extended TechBookReport FSO Tutorial for an introduction.

In terms of the FSO model, the attributes belong to the file object. So the first thing that your code has to do is to get a reference to a specific instance of the file object, lets call it fl. Once that's done then the attributes are returned in the attibutes property, like this:

                     fl.Attributes

Note the plural - a file can have multiple attibutes. The value returned is a bit mask, with different bits indicating the different attributes. These attributes are:

Attribute name Description and vbName
ReadOnly Read only. vbReadOnly
Hidden Hidden. vbHidden
System System file. vbSystem
Volume Volume label. vbVolume
Directory Directory or folder. vbDirectory
Archive vbArchive




How do we access these in practice? We have to take the numeric value returned and then test specific bits to see if they are set and therefore included in the attribute.

First let's have example of some code that lists all of the files in a given directory:

Sub file_attrib1()
    Dim fso As New FileSystemObject
    Dim fls As Files
    Dim strText As String
    Dim i As Integer
    
    Set fls = fso.GetFolder("C:\Windows\System").Files
    
    i = 2
    
    With Worksheets("Sheet1")
        .Cells(1, 1) = "File Name"
        .Cells(1, 2) = "File Size"
        .Cells(1, 3) = "Date"
        .Cells(1, 4) =  "Attributes"	
        For Each f In fls
            .Cells(i, 1) = f.Name
            .Cells(i, 2) = f.Size
            .Cells(i, 3) = f.DateLastModified
            .Cells(i, 4) = f.Attributes
            i = i + 1
        Next
    End With
End Sub

This subroutine will list all of the files in the C:\Windows\System folder, showing file name, size, date last modified and the attributes value in different columns on an Excel sheet. Doing this you'll see lots of attributes listed as 32 or 36. How do we interpret these values? By testing each for each of the attributes in turn.

The following function tests for each of the attributes in turn and builds up a string that represents them in the same way that a DOS dir command would. For example a hidden file is flagged as "H", system files are flagged as "S" etc.

Function listAttributes(a As Integer) As String

    listAttributes = ""
    If a And vbReadOnly Then
        listAttributes = "R"
    ElseIf a And vbHidden Then
        listAttributes = listAttributes & "H"
    ElseIf a And vbSystem Then
        listAttributes = listAttributes & "S"
    ElseIf a And vbVolume Then
        listAttributes = listAttributes & "V"
    ElseIf a And vbDirectory Then
        listAttributes = listAttributes & "D"
    ElseIf a And vbArchive Then
        listAttributes = listAttributes & "A"
    End If
    
End Function

Changing the previous subroutine so that the attributes value is replaced with something more meaningful is now as simple as substituting this line:

              .Cells(i, 4) = listAttributes(f.Attributes)

Finally, how can you use the attributes value to test for a specific attribute? For example how can you amend our original routine to ignore hidden files? Easy, test for the specific attribute and if the value is greater than zero you've identified it correctly. So, our original subroutine becomes:

Sub file_attrib2()
    Dim fso As New FileSystemObject
    Dim fls As Files
    Dim strText As String
    Dim i As Integer
    
    Set fls = fso.GetFolder("C:\windows").Files
    
    i = 2
    
    With Worksheets("Sheet1")
        .Cells(1, 1) = "File Name"
        .Cells(1, 2) = "File Size"
        .Cells(1, 3) = "Date"
        .Cells(1, 4) = "Attributes"
        For Each f In fls
            If Not ((f.Attributes And vbHidden) > 0) Then
                .Cells(i, 1) = f.Name
                .Cells(i, 2) = f.Size
                .Cells(i, 3) = f.DateLastModified
                .Cells(i, 4) = listAttributes(f.Attributes)
                i = i + 1
            End If
        Next
    End With
End Sub

That's all there is to it...

Return to home page

Contents copyright of Pan Pantziarka. If you like it link it, don't lift it. No copying for commercial use allowed. Site © 2012.