In Excel VBA (2002), how can I determine the amount of remaining hard drive
space? C:\ drive and D:\ drive.

Thanks.

You can use the same method as presented he

http://support.microsoft.com/default...b;en-us;225144
HOWTO: Use GetDiskFreeSpaceEx to Retrieve Drive Information

This method is limited to 2GB results:

http://support.microsoft.com/default...b;en-us;153091
How To Find and View the Amount of Free Disk Space on a Drive

--
Regards,
Tom Ogilvy


"Bullfrog1870" wrote in message
...
In Excel VBA (2002), how can I determine the amount of remaining hard
drive
space? C:\ drive and D:\ drive.

Thanks.

Hi everybody,

how about something very oldfashioned:

"c:\Mydir.Bat" containing:
dir c:\dir.txt /b

Plus:

Sub test099881()
Shell "c:\mydir.bat"
Dim sTmp As String
Open "c:\dir.txt" For Input As #1
While Not EOF(1)
Line Input #1, sTmp
Wend
Close #1
sTmp = Right(sTmp, Len(sTmp) - InStr(sTmp, "(s)") - 4)
sTmp = Left(sTmp, Len(sTmp) - 11)
MsgBox CDbl(sTmp)
End Sub

Not meant too seriously, and restricted to english version,
but when doing some kind of statistics
on a server with more than 350,000 files,
the dir-command was the only thing that worked.


--
regards

Helmut Weber, MVP WordVBA

"red.sys" & chr$(64) & "t-online.de"
Win XP, Office 2003





You can use the same method as presented he

http://support.microsoft.com/default...b;en-us;225144
HOWTO: Use GetDiskFreeSpaceEx to Retrieve Drive Information

This method is limited to 2GB results:

http://support.microsoft.com/default...b;en-us;153091
How To Find and View the Amount of Free Disk Space on a Drive

Doesn't that just list the filenames?

Plus a msgbox only accomodates a 255 character string, so I guess you don't
mean the code you provided literally worked with 350,000 files. <g

--
Regards,
Tom Ogilvy

"Helmut Weber" wrote in message
...
Hi everybody,

how about something very oldfashioned:

"c:\Mydir.Bat" containing:
dir c:\dir.txt /b

Plus:

Sub test099881()
Shell "c:\mydir.bat"
Dim sTmp As String
Open "c:\dir.txt" For Input As #1
While Not EOF(1)
Line Input #1, sTmp
Wend
Close #1
sTmp = Right(sTmp, Len(sTmp) - InStr(sTmp, "(s)") - 4)
sTmp = Left(sTmp, Len(sTmp) - 11)
MsgBox CDbl(sTmp)
End Sub

Not meant too seriously, and restricted to english version,
but when doing some kind of statistics
on a server with more than 350,000 files,
the dir-command was the only thing that worked.


--
regards

Helmut Weber, MVP WordVBA

"red.sys" & chr$(64) & "t-online.de"
Win XP, Office 2003





You can use the same method as presented he

http://support.microsoft.com/default...b;en-us;225144
HOWTO: Use GetDiskFreeSpaceEx to Retrieve Drive Information

This method is limited to 2GB results:

http://support.microsoft.com/default...b;en-us;153091
How To Find and View the Amount of Free Disk Space on a Drive

Hi Tom,

Doesn't that just list the filenames?

the filenames in the start directory plus the summary.
The disk space left would be in the last line, like:

Volume in drive C has no label.
Volume Serial Number is 6859-3ECC

Directory of C:\Edit2003

2005-12-04 22:08 <DIR .
2005-12-04 22:08 <DIR ..
[some entries deleted manually]
2005-11-18 12:51 23,552 Word-Edit.doc
2005-11-18 12:51 55,296 Word-Edit.dot
2005-09-24 09:15 88,064 xNormal.dot
2005-11-17 17:38 249,856 _Normal.dot
17 File(s) 1,502,396 bytes
2 Dir(s) 35,565,920,256 bytes free

Plus a msgbox only accomodates a 255 character string, so I guess you don't
mean the code you provided literally worked with 350,000 files. <g

That was another scenario, I shouldn't have mentioned, maybe.
dir *.* /s dir.txt
gives you list of all files, from whereever you start,
plus the summary.
With more than a hundred users collecting all kind
of rubbish for years it may last for hours.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

you can try the code below. But you need to make sure in your reference, you
check the Microsoft Scripting Runtime library:

Sub test()
Dim oFSO As FileSystemObject
Dim strDrive As Drive

Set oFSO = New FileSystemObject
Set strDrive = oFSO.GetDrive("C:\")
MsgBox FormatNumber(strDrive.FreeSpace / 1024, 0)


End Sub

--



"Bullfrog1870" wrote:

In Excel VBA (2002), how can I determine the amount of remaining hard drive
space? C:\ drive and D:\ drive.

Thanks.

Actually, using the /b argument as you suggested suppresses the summary
information of which you speak. Tested in Windows 98 SE and Windows XP to
be sure I am not speaking out of turn. This is also the expected behavior
according to the help on DIR. Perhaps you didn't mean to include the /b.

--
Regards,
Tom Ogilvy

"Helmut Weber" wrote in message
...
Hi Tom,

Doesn't that just list the filenames?

the filenames in the start directory plus the summary.
The disk space left would be in the last line, like:

Volume in drive C has no label.
Volume Serial Number is 6859-3ECC

Directory of C:\Edit2003

2005-12-04 22:08 <DIR .
2005-12-04 22:08 <DIR ..
[some entries deleted manually]
2005-11-18 12:51 23,552 Word-Edit.doc
2005-11-18 12:51 55,296 Word-Edit.dot
2005-09-24 09:15 88,064 xNormal.dot
2005-11-17 17:38 249,856 _Normal.dot
17 File(s) 1,502,396 bytes
2 Dir(s) 35,565,920,256 bytes free

Plus a msgbox only accomodates a 255 character string, so I guess you
don't
mean the code you provided literally worked with 350,000 files. <g

That was another scenario, I shouldn't have mentioned, maybe.
dir *.* /s dir.txt
gives you list of all files, from whereever you start,
plus the summary.
With more than a hundred users collecting all kind
of rubbish for years it may last for hours.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Hi Tom,

I am ashamed, maybe I should finally forget about DOS-times.

Actually, using the /b argument as you suggested
suppresses the summary information of which you speak.

Not if you put the /b switch in the wrong place,
like I did in my first posting in this thread.

Where I put it, it does nothing at all. :-(

Thank you.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"