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

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.

--
HTH

Bob Phillips

"dflayfield" wrote in message
...
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

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


"dflayfield" wrote in
message
...
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

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

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


"dflayfield" wrote in
message
...
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

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



"dflayfield" wrote in
message
...
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" wrote:

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


"dflayfield" wrote in
message
...
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

Thanks again for your help. You solved my problem.

"Chip Pearson" wrote:

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



"dflayfield" wrote in
message
...
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" wrote:

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


"dflayfield" wrote in
message
...
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