Using The FileSystemObject With VB and VBA
Installing the VB Script Run-time
The VB Script Run-time library is contained in the file SCRRUN.DLL, which should be in the \WINDOWS\SYSTEM32 folder. The file is included as part of the Visual Studio 6.0 release, and is installed along with Visual Basic 6.0 and Visual Basic for Applications 6.0. The file is also included and installed as part of the Internet Explorer 5.0 release, so if you do not have Visual Basic 6.0 installed, you need only install IE5 to gain access to the file and hence the FSO.
Having the file SCRRUN.DLL correctly installed on your system does not mean that the functions it contains are automatically available to Office macros, VB Scripted web pages and so on. When writing a macro, or stand-alone Visual Basic program come to that, a reference to the file must be explicitly made. Within any of the Office applications this can be achieved by loading the Visual Basic Editor (alt-F11 in Word and Excel) and selecting the Tools|References menu item. A listbox of available references will be displayed, with some, such as Visual Basic for Applications already selected by default. If you high-light the 'Microsoft Scripting Runtime' the full name and path of the SCRRUN.DLL file will be displayed below the listbox. Use the check-box to select the reference to the file.
Using The FSO - First Macro
Having selected the scripting run-time, we can now begin to use it in earnest. The first thing that any code has to do is initialise an instance of the FSO, without this there can be no access to the drives, folders and files. There are two methods of performing this initialisation. The simplest is to use the DIM command to create a new instance of the FSO:
Dim fso As New FileSystemObject
The alternative method is to explicitly use the CreateObject command:
Set fso = CreateObject("Scripting.FileSystemObject")
Whichever method is used, once the FSO has been instantiated, we can then get access to the objects which make it up. In our first example we will create a Word macro to list all the available drives on a machine. Load the VB Editor (Alt-F11) and enter the following code:
Sub test() Dim fso As New FileSystemObject Dim drv As Drives Set drv = fso.Drives For Each d In drv strText = "Drive: " & d.DriveLetter Selection.TypeText strText Selection.TypeParagraph Next End Sub
Once the code has been entered, return to Word, create a blank document and the run the new macro by pressing Alt-F8 and entering the macro name of 'test'. The macro will execute and list all the available drives. The macro works by creating an instance of the FSO and then setting the variable drv to contain the 'drives' collection. A 'For Each' loop is then used to cycle through every drive in the drives collection, with the text written out to the document using the TypeText command.
Although this macro is not very useful or interesting, we can use it as the basis of something more useful. Using the object browser we can see that a drive object has a 'freespace' property which returns the amount of freespace on a drive. However to find out this information the drive has to be interrogated, in the case of a floppy drive, for example, a diskette has to be in the drive before we can find out what the free space is. Our macro would therefore have to contend with the possibility of there being no diskette present or a drive being otherwise not available. The 'IsReady' property can be used to check the state of a drive before attempting to gauge its free space. We can therefore amend our macro to do something more useful, as shown:
Sub test2() Dim fso As New FileSystemObject Dim drv As Drives Dim strText As String Set drv = fso.Drives For Each d In drv strText = "Drive: " & d.DriveLetter & " FreeSpace: " If d.IsReady Then strText = strText & d.FreeSpace Else strText = strText & "Drive not ready" End If Selection.TypeText strText Selection.TypeParagraph Next End Sub
This macro works fine in Word, can we copy it directly into Excel and get it to run there? After all the FSO is as available to Excel as it is to Word. The answer is that the FSO portion of the code is fine, it is the output section which causes the problem. The TypeText command is Word-specific, if we wanted to list the drives and the available disk space in a series of cells in an Excel spreadsheet we would have to amend the macro as follows:
Sub test3() Dim fso As New FileSystemObject Dim drv As Drives Dim strText As String Dim i As Integer Set drv = fso.Drives i = 1 For Each d In drv strText = "Drive: " & d.DriveLetter & " FreeSpace: " If d.IsReady Then strText = strText & d.FreeSpace Else strText = strText & "Drive not ready" End If Worksheets("Sheet1").Cells(i, 1) = strText i = i + 1 Next End Sub
Note that this version of the macro includes an integer variable (i) which is used as an index into the cells in Sheet1 of an Excel workbook. Aside from this change for Excel, the rest of the macro is fundamentally the same, indicating that the underlying access mechanism is exactly the same across all Office applications. In development terms it means that the same skeleton code can be used across applications, thereby reducing development time, easing