formula to lookup file size and # of sheets in workbk

[CA_User]

Hello

is there a formula i can use that tells me how large my file is and how many sheets I have in the file? Thanks

 

[DominicB]

Good evening CA_User

Two really quick'n'dirty functions which will do the job, there isn't a native Excel function.

Function CountShts()
CountShts = Worksheets.Count
End Function

Function FileSize()
FileSize = (FileLen(ActiveWorkbook.FullName) / 1024)
End Function

Copy the code above into a module in the VBE, and you will have two new functions to use :

=CountShts()
Will return the number of sheets in the workbook.

=FileSize()
Will return the size of the workbook in Kb. This function will return an error if the file is not saved.

HTH

DominicB

 

[HalfAce]

Hello CA_User,

is there a formula i can use that tells me how large my file is and how many sheets I have in the file?

As far as I know there is no native function for this but here's something you can try.
Put these UDFs into a standard module.

Public Function SheetCount() As String
Application.Volatile
SheetCount = "There are " & ThisWorkbook.Sheets.Count & " sheets in this workbook."
End Function

Public Function FileSize() As String
Application.Volatile
FileSize = "The file size of this workbook is " & FileLen(ActiveWorkbook.FullName) / 1024 & " KBytes."
End Function

Then - after making sure you have saved the workbook - (ie, given it a name),
you can use these functions like so:
In one cell: =SheetCount()
And in another: =FileSize()

 

[CA_User]

this works great! Is there a way to store this in my personal workbook or excel startup template where I can have it all the time instead of having to save it to each workbook? Thanks!

 

[Norie]

You can find the no of sheets without VBA.

Goto Insert>Name...Define..., enter a name say NoSheets.

Then enter this in the refers to box and hit Add

=GET.WORKBOOK(4)

You can now enter =NoSheets in the worksheet to get the no of worksheets.

I'm still trying to see if there's a similar approach for file size, but I fear not.

 

[CA_User]

wow, you guys are awesome!

I have another post out there but have not received any solutions...

If any of you have answers, please lease please let me know how! Thanks

http://www.mrexcel.com/board2/viewtopic.php?t=236059