||Home| |New Reviews| |Software Methodologies| |Popular Science| |AI/Machine Learning| |Programming| |Java| |XML| |Software Tools| |Web| |Tutorials| |Other| |All By Date| |All By Title||
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:
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|
|System||System file. vbSystem|
|Volume||Volume label. vbVolume|
|Directory||Directory or folder. vbDirectory|
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...