Network Drive Info Retrieval

J

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

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:
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
Click to expand...
 
M

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.
Click to expand...
Doulas Steele from the Access forum came up with this code:
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
Click to expand...
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.
Click to expand...
Any ideas?
Click to expand...
Thanks,
JFree
Click to expand...

--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

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.
 
J

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

Dave Peterson said:
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:
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
Click to expand...
Click to expand...
 
D

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

Dave Peterson said:
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:
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
Click to expand...
Click to expand...
Click to expand...
 
J

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

Dave Peterson said:
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:\")
Click to expand...
[truncated]
 
D

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

Dave Peterson said:
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:\")
Click to expand...
[truncated]
Click to expand...
 
J

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

Dave Peterson said:
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

Dave Peterson said:
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:\")
Click to expand...
[truncated]
Click to expand...
Click to expand...
 
J

JFree

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

Dave Peterson said:
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

Dave Peterson said:
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:\")
Click to expand...
[truncated]
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Network Drive Free Space 9
I have a problem with the GetDiskFreeSpaceEx 3
GetDiskFreeSpaceEx API call 2
c# Problem: Call to GetDiskFreeSpaceExA API not Working!! 1
ChDrive and ChPath to a network drive 4
pinvoke GetDiskFreeSpaceEx with UNC 2
ID3v2 MP3 Tag Reading Class 2
Can't 'SaveAs' Directly to Network Folder 3

Top