MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Function to give workbook size


Posted by Mike O'Donnell on May 30, 2001 10:52 AM

Is there a function available (or any other way) to display in a cell the size of the given (saved)workbook in bytes? For example, if the file directory says
MYWKBK.XLS 123,456
the function would return the number 123456.


Posted by Kevin James on May 30, 2001 2:34 PM

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

Posted by Joe Was on May 30, 2001 2:57 PM

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

Posted by Kevin James on May 30, 2001 3:10 PM

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

Posted by Russell on May 30, 2001 4:36 PM

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

Posted by Dax on May 30, 2001 6:17 PM

Hello,

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

Regards,
Dax.

Posted by Joe Was on May 30, 2001 6:26 PM

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.

Posted by Russell on May 31, 2001 8:31 AM

Thanks Dax

Thank you Dax - good to know!