Finding the "Size" of Each Sheet in a Workbook

  • Thread starter Thread starter Tim Childs
  • Start date Start date
T

Tim Childs

Hi

Has anyone got some code for finding the underlying size
of each sheet in a workbook. I have in mind a piece of
code to output each sheet to a separate file in the "Temp"
directory to determine its size.

I looked in the archives but the search returned responses
regarding the output of modules and forms.

Thanks

Tim
 
Hi Tim,

You could save as a different name, then loop on:
- measure file size
- delete a sheet
- save

something like (not tested)


application.screenupdating=false
application.calculation=xlcalculationmanual
Application.calculatebeforesave=false
application.displayalerts=false
activeworkbook.savecopyas filename:="BigBook.Bak"
activeworkbook.saveas filename="BigBook.xls"
set oBk=Workbooks("bigBook")
debug.print FileLen(oBk.FullName)

on error resume next
for each oSht in oBk.worksheets
osht.delete
obk.save
debug.print FileLen(oBk.FullName)
next osht

FastExcel uses a similar approach for determining worksheet memory.


regards
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
 
????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????.?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????>
 
Charles

I must apologise for that e-mail sent on Friday with all the question marks:
I don't how it ended up like that: most bizarre. (Like I went to sleep
pressing the "?" key !)

Many thanks for coming back with version 2 - it is really good. Much
appreciated.

Regards

Tim
 
Charles

The other thing I learned from your post was the use of the operator "\" as
opposed to "/".

That's very neat and helpful rather than having to use a function to convert
to integer format.

THANKS

Tim
 
Back
Top