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

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




JFree wrote:

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

--

Dave Peterson

On Aug 26, 1:55*pm, Dave Peterson wrote:
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





JFree wrote:

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

Code:

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

Just curious...

Were you able to test against a UNC path?



mcescher wrote:

<<snipped

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

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" wrote:

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




JFree wrote:

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

--

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:\")



JFree wrote:

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" wrote:

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




JFree wrote:

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

--

Dave Peterson


--

Dave Peterson

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" wrote:

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]

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.

JFree wrote:

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" wrote:

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

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" wrote:

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.

JFree wrote:

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" wrote:

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

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

"Dave Peterson" wrote:

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.

JFree wrote:

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" wrote:

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