Function to give workbook size

Mike:

The email I sent provides the SUB syntax. However, if you'd like to do a list, you can turn it into a function like this:

Function FilSize(Flname)
FilSize = FileLen(Flname)
End Function

In one column you would type the full path of the filename, in the other column you type the function name with cell reference.

If cell A2 contains your list of fully qualified filename, in cell B2 type: =FilSize(A2)

Kevin

I use a pop up box, but you may modify this to suit your needs. (This is attached to a button.)

Sub FileSize()
'This macro gives the active file path, name & size
Dim MyFile
Dim MyCount
MyFile = ActiveWorkbook.FullName
MyCount = ActiveWorkbook.Sheets.Count
With Assistant
.On = True
.Visible = True
If Not Sounds Then Sounds = True
.Animation = msoAnimationGetAttentionMinor
End With
MsgBox "The active file is: " & MyFile & (Chr(13)) & (Chr(13)) & _
" The file size is: " & CreateObject("Scripting.FileSystemObject").GetFile(MyFile).Size & " bytes, " & _
CreateObject("Scripting.FileSystemObject").GetFile(MyFile).Size / 1000 & " Kbytes or " & _
CreateObject("Scripting.FileSystemObject").GetFile(MyFile).Size / 1000000 & " Mbytes." & _
(Chr(13)) & (Chr(13)) & " [ " & CreateObject("Scripting.FileSystemObject").GetFile(MyFile).Size & _
" bytes equates to an actual disk file size of: " & _
CreateObject("Scripting.FileSystemObject").GetFile(MyFile).Size / 1024 & " KB of disk space!]" & _
(Chr(13)) & (Chr(13)) & " This file [ The Active WorkBook ] contains a total of: " & MyCount & _
" sheets!" & (Chr(13)) & (Chr(13)) & _
" (Note: A standard floppy disk will hold, 1.44 Mbytes, 1,440 Kbytes or 1,440,000 bytes.)" & _
(Chr(13)) & (Chr(13)) & " ( Two files like this one will require: " & _
CreateObject("Scripting.FileSystemObject").GetFile(MyFile).Size / 512 & " KB of disk space! ]", 0, _
"File Information, Drive, Path, File and Size!"
With Assistant
.On = True
.Visible = True
If Not Sounds Then Sounds = True
.Animation = msoAnimationCharacterSuccessMajor
End With
End Sub

sledge hammers

Whoa! Joe!!

You have things in here I've yet to learn. I'm blown away!

Do you use sledge hammers to kill flies too?

:O)

Just kidding. It just seemed a bit overkill.

Kev

Mike,

I also wrote a function that you could use in a cell. However, this one and the one written by Kevin will not update as the size of the workbook changes. What you would have to do to update is to either enter the formula again, or select the cell that the function is used in, hit F2, and then enter. Maybe someone else out there would know how to better update. Function below:

Function FileLength() As Long

FileLength = FileLen(ActiveWorkbook.Path & "\" & ActiveWorkbook.Name)

End Function

-Russell

Hello,

If you include the line Application.Volatile in your function then it will update each time the workbook is calculated.

Regards,
Dax.

Re: (Over-kill. Try it it works if you have the head room!)

Kev, Give it a try, just copy and paste. You can strip out what you don't need or like. We deal with consultants who try to cram too many files on a disk. This lets you know exactly what your work is doing and where you can go. It is over-kill for most applications, but its nice when you need the info. If the machine will do it and you have the head room let it.

Thanks Dax

Thank you Dax - good to know!