The Windows Script Host Object Model has many useful properties and methods for dealing with files, folders, disks, etc. One common use is for checking the amount of available space on a drive before creating a large file. The example function below illustrates how to use the
FileSystemObject along with the
Drive objects to iterate through all drives on your system.
To use this example in your macros you must add a reference to the Windows Script Host Object Model library. To do this select Tools > References, then scroll toward the bottom of the list and select Windows Script Host Object Model.
' 'This function requires a reference to: 'Windows Script Host Object Model (C:\Windows\System32\wshom.ocx)" ' Public Sub MyMacro() Dim objFSO As FileSystemObject Dim objDrive As Drive 'Set new instance if File System Object Set objFSO = New FileSystemObject 'A UserForm with a ListBox control has been setup 'to display the results With UserForm1 'Iterate through all drives For Each objDrive In objFSO.Drives .ListBox1.AddItem "Drive – " & objDrive.DriveLetter & ":" 'If the drive is ready get the information 'Size and space values are returned in bytes If objDrive.IsReady Then .ListBox1.AddItem "Free Space = " & cstr(objDrive.FreeSpace) .ListBox1.AddItem "Avail Space = " & cstr(objDrive.AvailableSpace) .ListBox1.AddItem "Total Size = " & cstr(objDrive.TotalSize) Else .ListBox1.AddItem "Drive is not ready" End If .ListBox1.AddItem "" Next .Show End With End Sub