vba code to display total size of a directory

[Rudi GOUSSEY]

Does anybody knows the VBA-code which shows the total size of a directory (subdirectories included).
Thanks

 

[SIXTH SENSE]

Hi!
try this one!


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> ShowFreeSpace()
drvPath = "c:\"
    <SPAN style="color:#00007F">Dim</SPAN> fs, d, s
    <SPAN style="color:#00007F">Set</SPAN> fs = CreateObject("Scripting.FileSystemObject")
    <SPAN style="color:#00007F">Set</SPAN> d = fs.GetDrive(fs.GetDriveName(drvPath))
    s = "Drive " & UCase(drvPath) & " - "
    s = s & d.VolumeName & vbCrLf
    s = s & "Free Space: " & FormatNumber(d.FreeSpace / 1024, 0)
    s = s & " Kbytes"
    MsgBox s
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Richie(UK)]

Hi Rudi,

Try this:

Sub FolderSize()
    Dim fso As Object, fsoFolder As Object
    Const strFolderName As String = "C:\My Documents"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(strFolderName)

    MsgBox fsoFolder.Size & " bytes"

    Set fsoFolder = Nothing
    Set fso = Nothing

End Sub

HTH

 

[Rudi GOUSSEY]

Thank you for these excellent working codes.
Rudi

 

[SaladProblems]

If you just had c:\windows\windows.exe in one cell, could you have a formula that would return the file size, assuming of course that you have access to that directory?

 

[tusharm]

Wouldn't it be faster to just try it rather than ask a question and wait for an answer? {grin}

If you just had c:\windows\windows.exe in one cell, could you have a formula that would return the file size, assuming of course that you have access to that directory?

 

[SaladProblems]

It works perfectly for directories, but not for specific files, at least not if I was using it correctly.

 

[tusharm]

Ok, so you have successfully answered your own question!

It works perfectly for directories, but not for specific files, at least not if I was using it correctly.

 

[SaladProblems]

Ok, so you have successfully answered your own question!

I asked:

If you just had c:\windows\windows.exe in one cell, could you have a formula that would return the file size, assuming of course that you have access to that directory?

It doesn't seem as though this particular macro will do exactly that, so therefore I have proven it's impossible to return the size of a file via a UDF?

Upgrade me to MVP status, I've reached the boundaries of Excel!

 

[Blade Hunter]

You mean like this?

Public Function FileSize(FileName As String)
Dim FSO As Object
Set FSO = CreateObject("scripting.filesystemobject")
Set file = FSO.Getfile(FileName)
FileSize = file.Size
Set FSO = Nothing
End Function

Put the filename in the formula (including the drive and directory) and it will return the size in bytes