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

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

?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ????.????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ??????????????????????????????????????????