formula to lookup file size and # of sheets in workbk

CA_User

Board Regular
Joined
Nov 5, 2004
Messages
124
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good evening CA_User

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

Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
Code:
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

Board Regular
Joined
Nov 5, 2004
Messages
124
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Forum statistics

Threads
1,141,587
Messages
5,707,252
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top