Get File Size (Number of Bytes) of Active Workbook

[Guest]

I need to populate a cell with the current file size of the active workbook.
I have tried:

Function FileSize(I As Integer)
FileSize = ThisWorkbook.BuiltinDocumentProperties(I)
End Function

and called the function in the sheet with:
=FileSize(22) ' 22 being the code for "Number of Bytes"
I get a #VALUE! error.

But if I use:
=FileSize(3) '3 being the code for "Author"
it works properly.

Any ideas?

Thanks,
Dave

 

[Bob Phillips]

Firstly,

it is not a good idea to use the index, you cannot rely on it always being
the same, better to use the property name. But in this case it seems to make
no difference.

Some of the properties apply to some products, n umber of slides is clearly
PowerPoint, so maybe Number of Bytes does not apply to Excel.

 

[Chip Pearson]

Not all BuiltInDocument properties are maintained by Excel. For
file size, try

Function FileSize()
FileSize = FileLen(ThisWorkbook.FullName)
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[Guest]

Thanks Chip,
That worked perfectly. This is probably a stupid question, but any idea how
to force the file size value to refresh periodically or after the most recent
save?

 

[Chip Pearson]

Add

Application.Volatile True

as the first line of code in the function. This will cause the
function to refresh every time a calculation is made.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message

 

[Guest]

Thanks again for your help. You solved my problem.