Check Drive Space

[Charles]

Hi is it possible to write code to check available space
on a drive from Excel?.
TIA
Charles

 

[Bob Phillips]

Here is a routine posted by Jim Rech a while back

Declare Function GetDiskFreeSpaceExA Lib "kernel32" _
(ByVal lpDirectoryName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfBytes As Currency) As Boolean

Sub ShowDiskSpace()
Dim MyFreeSpace As Currency
Dim MyTotalSpace As Currency
Dim TotalFreeSpace As Currency
If GetDiskFreeSpaceExA("c:\", _
MyFreeSpace, _
MyTotalSpace, _
TotalFreeSpace) Then
MyFreeSpace = MyFreeSpace * 10000
MyTotalSpace = MyTotalSpace * 10000
TotalFreeSpace = TotalFreeSpace * 10000
MsgBox "Total space on drive: " & _
Format(MyTotalSpace, "#,###bytes") & Chr(13) & _
"Free space: " & Format(MyFreeSpace, "#,### bytes")
End If
End Sub

 

[keepITcool]

a bit simpler alternative to using api..

Function FreeKB(DriveSpec As String)
With CreateObject("Scripting.FileSystemObject")
If Not .DriveExists(DriveSpec) Then
FreeKB = CVErr(xlErrRef)
Else
FreeKB = .GetDrive(DriveSpec).FreeSpace \ 1024
End If
End With
End Function



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Bob Phillips]

but depends upon Scripting being allowed, not true in many commercial
environments