---

Drives

Having written and run our first macros, we can now look at the different components of the FSO in more detail. At the highest level of the file system, of course, is the drive. This can be a hard disk, a network drive, a floppy, a local RAM drive and so on. Each of these different physical units is a member of the 'drives' collection, which has two properties, Count and Item. Each of the drives, however, has a number of different properties and methods associated with it. In addition to the FreeSpace property we looked at, other properties include: DriveType (which allows us to work out what type of drive we are dealing with), FileSystem, TotalSize, VolumeName, RootFolder and ShareName, amongst others.

If we are interested in a specific drive, rather than in cycling through the entire drives collection, we can access it directly by setting a variable to point to it. For example if we want to find out the FileSystem and TotalSize of the C:\ drive we could point to the drive simply by creating a drive variable (drv, for example), and the using the following command to set it:

Set drv = fso.Drives.Item("C:\")

We can then access the properties we want simply by using the standard Visual Basic dot notation. For example the FileSystem can be returned simply as:

drv.FileSystem

Alternatively we can use the GetDrive method of the FSO to set the variable as follows:

Set drv = fso.GetDrive("C:\")

Again once the variable has been set we can use the dot notation to return its properties.

Other drive-level methods of the FSO include DriveExists, which returns a Boolean result to indicate whether a given drive exists or not. This can be useful to check whether a given network drive is defined or mapped, for example.

Folders

Having established a way of using the FSO to find out about drives, we can use this to start looking at the next level up the hierarchy - at folders. As you would expect there is a 'folders' collection which has properties of Count and Item, as well as a single method - Add - which can be used to add a new folder to the collection. To enumerate all of the folders on a given drive we can use a 'For Each' loop in a macro. Obviously each folder in the collection has many more properties and methods than the collection. Again using the Object Browser we can list the various properties and methods for the folder object.

By their nature folders are part of a hierarchy, and so there are properties of ParentFolder and SubFolders. We can find out whether a folder IsRootFolder, we can get a folder's Path and the list of Files it contains. Additionally we can find out the DateCreated, DateLastModified and DateLastAccessed. We can also get a folder's Size, and it is this last property that we'll use in our next macro - an Excel macro to list the size of all the first level folders on the C:\ drive. Note that this property is recursive in the sense that the size reported for a folder is based not just on the size of the files in it, but includes the files in all of the sub-folders and their sub-folders and so on.

In order to make our macro work we need to set a variable to contain the SubFolders property of the "C:\" folder. Once this has been done we can then cycle through the SubFolders and get the Name and Size property for each folder:

Sub test4()
	Dim fso As New FileSystemObject
	Dim flds As Folders
	Dim strText As String
	Dim i As Integer
	
	Set flds = fso.GetFolder("C:\").SubFolders
	i = 1
	
	For Each f In flds
	    strText = f.Path & " - " & f.Size
	    Worksheets("Sheet1").Cells(i, 1) = strText
	    i = i + 1
	Next
End Sub

Note how the dot notation was used to set the flds variable directly, there was no need to define a drive object in order to access the folders.

With the 'For Each' loop we can use the dot notation to access lower levels of information for each folder. For example to find out how many subfolders are contained within each of our folders we can simply use the expression:

f.SubFolders.Count

To count the number of files:

f.Files.Count

Using the FSO we can do more than simply interrogate the file system, however. The 'folder' object supports a number of methods, including functions which can be used to Move or Delete folders. To create a new folder we use the Add method of the 'folders' collection. The code to create a folder called 'TechBookReport' on the root directory of the C:\ drive is as follows:

Set flds = fso.GetFolder("C:\").SubFolders
flds.Add ("TechBookReport")

Note that you cannot create a second level folder ("\test1\test2") without having first created the first level ("\test").

The 'folders' collection does not support a 'Delete' method, to do that you must address the folder directly. To delete the "TechBookReport" folder we can use the following code snippet (note that the variable fld is a drive, not a drives collection):

Set fld=fso.GetFolder("C:\Pan")
fld.Delete

Moving or copying a folder is simply a case of using the Move or Copy method, however there is no Rename method. To rename a file you simply change its name property. For example to change the folder from 'John' to 'Doe' you can execute the following:

Set fld=fso.GetFolder("C:\John")
fld.Name = "Doe"

Files

Of course ultimately folders are only there to contain files, which is the next object to look at. Predictably there is a 'files' collection which has properties of Count and Item only, there is no Add method, however. The properties and methods for operating on files are associated with the 'file' object. Let's first look at producing a simple listing of every file in a given folder. Again we'll use Excel to host the macro, though it is a trivial task to amend the code for Word. Here we will write a macro to list the file name, date and size for every file in the \WINDOWS\SYSTEM folder:

Sub test5()
	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"
	    For Each f In fls
	        .Cells(i, 1) = f.Name
	        .Cells(i, 2) = f.Size
	        .Cells(i, 3) = f.DateLastModified
	        i = i + 1
	    Next
	End With
End Sub

The macro uses a variable called fls of type 'files', which is then set to point to the files collection of the \Windows\System folder. Once this has been done the macro uses the With command as a short-hand to refer to Sheet1 of the current Excel workbook. A simple header is created in the first row of the sheet. A 'For Each' loop is then used to cycle through the list of files, writing out the file name, size and date to columns 1 through 3 of each row.

Note that of the three different date properties for the file object, the macro chooses to use the DateLastModified rather than DateCreated or DateLastAccessed. This is because some files may not actually have a DateCreated set for them in older vesions of Windows, but all files have a DateLastModified. Adding more information, such as the file type is a simple matter of adding another column to the header and then writing out the property - f.Type - to the next column.

Although the files collection is an extremely useful way of processing through a set of files in a folder, it does have one fairly fundamental drawback - it does not support the use of wildcards to limit the selection of files. There is no way of using a files collection to pick all the *.pdf files in a folder, for example. If Microsoft added this functionality to the FSO it would make it even more useful.

In the mean-time, how can we work around this limitation? What if we have a specific requirement to perform some operation, such as copying or deleting, on a set of files specified by a wild-card such as *.pdf? In this sort of situation there are two alternatives. The first is to use the files collection and then to use a 'For Each' loop to test whether the individual file matches the wild card, or else to use one of the FSO methods that can support wild-cards within the 'For Each' loop. A faster solution is to use the FSO methods and properties to get the paths and folders, and then within each folder use the older Visual Basic Dir() command with the wildcard to cycle through only those files which we are interested in.

The various methods supported by the 'file' object means that copying, moving and deleting are easily accomplished. Renaming a file is a simple matter of writing a new value back to the file's Name property.

What about creating a new file? This is where the FSO betrays its origins, unfortunately. Currently the only file type that can be created using the FSO is a text file. There are no methods available for the creation of binary files, nor can existing binary files be written to or read. This means that any operations required on binary files must be performed using the older VB and VBA commands.

Text files, however, are called TextStreams, and are represented as a specific object within the FSO hierarchy.

Return to home page