Network Drive Info Retrieval

[JFree]

I'm trying to make a spreadsheet that will hold network share names in one
column
(ie: \\drive\folder\) and in adjacent columns show the total size, space
used, and space available for that drive.

Doulas Steele from the Access forum came up with this code:

Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" _
(ByVal lpcurRootPathName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long

Public Function fFreeBytes(NetworkShare As String) As Long
Dim curBytesFreeToCaller As Currency
Dim curTotalBytes As Currency
Dim curTotalFreeBytes As Currency

Call GetDiskFreeSpaceEx(NetworkShare, _
curBytesFreeToCaller, _
curTotalBytes, _
curTotalFreeBytes)

fFreeBytes = CLng(Format$(curTotalFreeBytes * 10000, "###,###,###,##0"))

End Function

I thought I could just drop this code into the VB Editor and call it as a
function in Excel (ie: =fFreeBytes(\\drive\folder) but I am obviously doing
it wrong.

Any ideas?

Thanks,
JFree

 

[Dave Peterson]

I don't have UNC path to test, so maybe that's the problem???

But I'd make sure I passed a string to the function and (from a search of
google) make sure that I finished with a trailing backslash (required in some
versions of windows:

=fFreeBytes(\\drive\folder)
becomes:
=fFreeBytes("\\drive\folder\")

It worked ok when I used:
=fFreeBytes("C:\")

I did change the code slightly...

I used "As Double" and formatted the cell nicely (dropping the clng(format(...))
stuff in the code) and it worked ok for me:

Option Explicit
Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" _
(ByVal lpcurRootPathName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long

Public Function fFreeBytes(NetworkShare As String) As Double
Dim curBytesFreeToCaller As Currency
Dim curTotalBytes As Currency
Dim curTotalFreeBytes As Currency

Call GetDiskFreeSpaceEx(NetworkShare, _
curBytesFreeToCaller, _
curTotalBytes, _
curTotalFreeBytes)

fFreeBytes = curTotalFreeBytes * 10000

End Function

I'm trying to make a spreadsheet that will hold network share names in one
column
(ie: \\drive\folder\) and in adjacent columns show the total size, space
used, and space available for that drive.

Doulas Steele from the Access forum came up with this code:

Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" _
(ByVal lpcurRootPathName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long

Public Function fFreeBytes(NetworkShare As String) As Long
Dim curBytesFreeToCaller As Currency
Dim curTotalBytes As Currency
Dim curTotalFreeBytes As Currency

Call GetDiskFreeSpaceEx(NetworkShare, _
curBytesFreeToCaller, _
curTotalBytes, _
curTotalFreeBytes)

fFreeBytes = CLng(Format$(curTotalFreeBytes * 10000, "###,###,###,##0"))

End Function

I thought I could just drop this code into the VB Editor and call it as a
function in Excel (ie: =fFreeBytes(\\drive\folder) but I am obviously doing
it wrong.

Any ideas?

Thanks,
JFree

 

[mcescher]

I don't have UNC path to test, so maybe that's the problem???

But I'd make sure I passed a string to the function and (from a search of
google) make sure that I finished with a trailing backslash (required in some
versions of windows:

=fFreeBytes(\\drive\folder)
becomes:
=fFreeBytes("\\drive\folder\")

It worked ok when I used:
=fFreeBytes("C:\")

I did change the code slightly...

I used "As Double" and formatted the cell nicely (dropping the clng(format(...))
stuff in the code) and it worked ok for me:

Option Explicit
Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
    Alias "GetDiskFreeSpaceExA" _
        (ByVal lpcurRootPathName As String, _
        lpFreeBytesAvailableToCaller As Currency, _
        lpTotalNumberOfBytes As Currency, _
        lpTotalNumberOfFreeBytes As Currency) As Long

Public Function fFreeBytes(NetworkShare As String) As Double
    Dim curBytesFreeToCaller As Currency
    Dim curTotalBytes As Currency
    Dim curTotalFreeBytes As Currency

    Call GetDiskFreeSpaceEx(NetworkShare, _
        curBytesFreeToCaller, _
        curTotalBytes, _
        curTotalFreeBytes)

    fFreeBytes = curTotalFreeBytes * 10000

End Function

I'm trying to make a spreadsheet that will hold network share names in one
column
(ie: \\drive\folder\) and in adjacent columns show the total size, space
used, and space available for that drive.

Doulas Steele from the Access forum came up with this code:

Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" _
(ByVal lpcurRootPathName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long[/QUOTE]
[QUOTE]
Public Function fFreeBytes(NetworkShare As String) As Long
Dim curBytesFreeToCaller As Currency
Dim curTotalBytes As Currency
Dim curTotalFreeBytes As Currency[/QUOTE]
[QUOTE]
Call GetDiskFreeSpaceEx(NetworkShare, _
curBytesFreeToCaller, _
curTotalBytes, _
curTotalFreeBytes)[/QUOTE]
[QUOTE]
fFreeBytes = CLng(Format$(curTotalFreeBytes * 10000, "###,###,###,##0"))[/QUOTE]
[QUOTE]
End Function

I thought I could just drop this code into the VB Editor and call it asa
function in Excel (ie: =fFreeBytes(\\drive\folder) but I am obviouslydoing
it wrong.

Any ideas?

Thanks,
JFree

--

Dave Peterson- Hide quoted text -

- Show quoted text -

I agree with Dave. I changed the "As Double" to "As Currency" and
then the CLng to a CCur. No error message.

HTH,
Chris M.

 

[Dave Peterson]

Just curious...

Were you able to test against a UNC path?

 

[JFree]

Thank you both for your help!

I'm still stuck. Dave, I copied and pasted your revised code exactly as you
have it here, in a VBAProject in the VB Editor. When I save, enable macros,
and type "=fFreeBytes("C:\")" *without the outside quotes* into cell A1, I
get #NAME?.

I get the same result when I use network drive UNC paths, but I am able to
type them into cells by themselves and it automatically creates hyperlinks to
the drives...

Where am I going wrong?

JFree

I don't have UNC path to test, so maybe that's the problem???

But I'd make sure I passed a string to the function and (from a search of
google) make sure that I finished with a trailing backslash (required in some
versions of windows:

=fFreeBytes(\\drive\folder)
becomes:
=fFreeBytes("\\drive\folder\")

It worked ok when I used:
=fFreeBytes("C:\")

I did change the code slightly...

I used "As Double" and formatted the cell nicely (dropping the clng(format(...))
stuff in the code) and it worked ok for me:

Option Explicit
Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" _
(ByVal lpcurRootPathName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long

Public Function fFreeBytes(NetworkShare As String) As Double
Dim curBytesFreeToCaller As Currency
Dim curTotalBytes As Currency
Dim curTotalFreeBytes As Currency

Call GetDiskFreeSpaceEx(NetworkShare, _
curBytesFreeToCaller, _
curTotalBytes, _
curTotalFreeBytes)

fFreeBytes = curTotalFreeBytes * 10000

End Function

I'm trying to make a spreadsheet that will hold network share names in one
column
(ie: \\drive\folder\) and in adjacent columns show the total size, space
used, and space available for that drive.

Doulas Steele from the Access forum came up with this code:

Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" _
(ByVal lpcurRootPathName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long

Public Function fFreeBytes(NetworkShare As String) As Long
Dim curBytesFreeToCaller As Currency
Dim curTotalBytes As Currency
Dim curTotalFreeBytes As Currency

Call GetDiskFreeSpaceEx(NetworkShare, _
curBytesFreeToCaller, _
curTotalBytes, _
curTotalFreeBytes)

fFreeBytes = CLng(Format$(curTotalFreeBytes * 10000, "###,###,###,##0"))

End Function

I thought I could just drop this code into the VB Editor and call it as a
function in Excel (ie: =fFreeBytes(\\drive\folder) but I am obviously doing
it wrong.

Any ideas?

Thanks,
JFree

 

[Dave Peterson]

Make sure you put the code in a general module of the workbook with that
formula.

You may have put it behind the worksheet--it doesn't belong there.

And make sure that macros are enabled.

If you put it in a different workbook, you have to include the name of the
workbook with the UDF:

='someworkbooknamehere.xls'!ffreebytes("c:\")

Thank you both for your help!

I'm still stuck. Dave, I copied and pasted your revised code exactly as you
have it here, in a VBAProject in the VB Editor. When I save, enable macros,
and type "=fFreeBytes("C:\")" *without the outside quotes* into cell A1, I
get #NAME?.

I get the same result when I use network drive UNC paths, but I am able to
type them into cells by themselves and it automatically creates hyperlinks to
the drives...

Where am I going wrong?

JFree

I don't have UNC path to test, so maybe that's the problem???

But I'd make sure I passed a string to the function and (from a search of
google) make sure that I finished with a trailing backslash (required in some
versions of windows:

=fFreeBytes(\\drive\folder)
becomes:
=fFreeBytes("\\drive\folder\")

It worked ok when I used:
=fFreeBytes("C:\")

I did change the code slightly...

I used "As Double" and formatted the cell nicely (dropping the clng(format(...))
stuff in the code) and it worked ok for me:

Option Explicit
Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" _
(ByVal lpcurRootPathName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long

Public Function fFreeBytes(NetworkShare As String) As Double
Dim curBytesFreeToCaller As Currency
Dim curTotalBytes As Currency
Dim curTotalFreeBytes As Currency

Call GetDiskFreeSpaceEx(NetworkShare, _
curBytesFreeToCaller, _
curTotalBytes, _
curTotalFreeBytes)

fFreeBytes = curTotalFreeBytes * 10000

End Function

I'm trying to make a spreadsheet that will hold network share names in one
column
(ie: \\drive\folder\) and in adjacent columns show the total size, space
used, and space available for that drive.

Doulas Steele from the Access forum came up with this code:

Declare Function GetDiskFreeSpaceEx Lib "kernel32" _
Alias "GetDiskFreeSpaceExA" _
(ByVal lpcurRootPathName As String, _
lpFreeBytesAvailableToCaller As Currency, _
lpTotalNumberOfBytes As Currency, _
lpTotalNumberOfFreeBytes As Currency) As Long

Public Function fFreeBytes(NetworkShare As String) As Long
Dim curBytesFreeToCaller As Currency
Dim curTotalBytes As Currency
Dim curTotalFreeBytes As Currency

Call GetDiskFreeSpaceEx(NetworkShare, _
curBytesFreeToCaller, _
curTotalBytes, _
curTotalFreeBytes)

fFreeBytes = CLng(Format$(curTotalFreeBytes * 10000, "###,###,###,##0"))

End Function

I thought I could just drop this code into the VB Editor and call it as a
function in Excel (ie: =fFreeBytes(\\drive\folder) but I am obviously doing
it wrong.

Any ideas?

Thanks,
JFree

 

[JFree]

I opened Excel, Alt-F11, double-click "ThisWorkbook", and paste the code into
the window. Then I save as "test.xls", close excel, re-open, enable macros,
and write in A1:

=fFreeBytes("C:\")

(or =fFreeBytes("\\drive\folder\") using a legit path)

and I get #NAME? in the cell. When I click the "=" sign next to the formula
bar, it opens a window showing the argument I am passing (in this case "C:\")
= #VALUE!

Undefined...

Does it matter that I am in W2K running Excel 2000? It seems odd that we
are using the same code (in presumably the same manner) and getting different
results.

Thanks again for your help.
JFree

Make sure you put the code in a general module of the workbook with that
formula.

You may have put it behind the worksheet--it doesn't belong there.

And make sure that macros are enabled.

If you put it in a different workbook, you have to include the name of the
workbook with the UDF:

='someworkbooknamehere.xls'!ffreebytes("c:\")

[truncated]

 

[Dave Peterson]

It doesn't belong in the ThisWorkbook module either.

Put it in a general module.
Inside the VBE
Insert Module
Then move the code into that module.

Then back to excel and reenter that formula.

=========
I used xl2003 and winXP home for my tests. From the googling I did, it looks
like this would work going all the way back to win95.

If it doesn't work after you make these changes, you may want to ask in the same
forum where you found the API code.

I opened Excel, Alt-F11, double-click "ThisWorkbook", and paste the code into
the window. Then I save as "test.xls", close excel, re-open, enable macros,
and write in A1:

=fFreeBytes("C:\")

(or =fFreeBytes("\\drive\folder\") using a legit path)

and I get #NAME? in the cell. When I click the "=" sign next to the formula
bar, it opens a window showing the argument I am passing (in this case "C:\")
= #VALUE!

Undefined...

Does it matter that I am in W2K running Excel 2000? It seems odd that we
are using the same code (in presumably the same manner) and getting different
results.

Thanks again for your help.
JFree

Make sure you put the code in a general module of the workbook with that
formula.

You may have put it behind the worksheet--it doesn't belong there.

And make sure that macros are enabled.

If you put it in a different workbook, you have to include the name of the
workbook with the UDF:

='someworkbooknamehere.xls'!ffreebytes("c:\")

[truncated]

 

[JFree]

BINGO!

That was my problem. Now I just need to format the Bytes to TB and I'm up
and running. I really appreciate your help here Dave. This has been beating
me up!

JFree

It doesn't belong in the ThisWorkbook module either.

Put it in a general module.
Inside the VBE
Insert Module
Then move the code into that module.

Then back to excel and reenter that formula.

=========
I used xl2003 and winXP home for my tests. From the googling I did, it looks
like this would work going all the way back to win95.

If it doesn't work after you make these changes, you may want to ask in the same
forum where you found the API code.

I opened Excel, Alt-F11, double-click "ThisWorkbook", and paste the code into
the window. Then I save as "test.xls", close excel, re-open, enable macros,
and write in A1:

=fFreeBytes("C:\")

(or =fFreeBytes("\\drive\folder\") using a legit path)

and I get #NAME? in the cell. When I click the "=" sign next to the formula
bar, it opens a window showing the argument I am passing (in this case "C:\")
= #VALUE!

Undefined...

Does it matter that I am in W2K running Excel 2000? It seems odd that we
are using the same code (in presumably the same manner) and getting different
results.

Thanks again for your help.
JFree

Make sure you put the code in a general module of the workbook with that
formula.

You may have put it behind the worksheet--it doesn't belong there.

And make sure that macros are enabled.

If you put it in a different workbook, you have to include the name of the
workbook with the UDF:

='someworkbooknamehere.xls'!ffreebytes("c:\")

[truncated]

 

[JFree]

I forgot to mention: This works great with UNC paths too!

It doesn't belong in the ThisWorkbook module either.

Put it in a general module.
Inside the VBE
Insert Module
Then move the code into that module.

Then back to excel and reenter that formula.

=========
I used xl2003 and winXP home for my tests. From the googling I did, it looks
like this would work going all the way back to win95.

If it doesn't work after you make these changes, you may want to ask in the same
forum where you found the API code.

I opened Excel, Alt-F11, double-click "ThisWorkbook", and paste the code into
the window. Then I save as "test.xls", close excel, re-open, enable macros,
and write in A1:

=fFreeBytes("C:\")

(or =fFreeBytes("\\drive\folder\") using a legit path)

and I get #NAME? in the cell. When I click the "=" sign next to the formula
bar, it opens a window showing the argument I am passing (in this case "C:\")
= #VALUE!

Undefined...

Does it matter that I am in W2K running Excel 2000? It seems odd that we
are using the same code (in presumably the same manner) and getting different
results.

Thanks again for your help.
JFree

Make sure you put the code in a general module of the workbook with that
formula.

You may have put it behind the worksheet--it doesn't belong there.

And make sure that macros are enabled.

If you put it in a different workbook, you have to include the name of the
workbook with the UDF:

='someworkbooknamehere.xls'!ffreebytes("c:\")

[truncated]