Determining hard drive space remaining

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 here:

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" <Bullfrog1870@discussions.microsoft.com> wrote in message
news:B04644FE-A54C-4719-A0A7-DC00FA0CC58E@microsoft.com...
> 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 here:
>
>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" <nbhymsjxdgcn@mailinator.com> wrote in message
news:jsm6p15029bed6tqg183unboi5i33pt22d@4ax.com...
> 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 here:
> >
> >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
>

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.

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"

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" <nbhymsjxdgcn@mailinator.com> wrote in message
news:t1o6p1hni154qifb00ilfv5otg98ckrlnp@4ax.com...
> 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"