Finding the "Size" of Each Sheet in a Workbook
 

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

Finding the "Size" of Each Sheet in a Workbook
 

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

Finding the "Size" of Each Sheet in a Workbook
 

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

Finding the "Size" of Each Sheet in a Workbook
 

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 Williams" wrote in message
...
???????????????????? maybe you want something a bit more tested
??????????????
- watch for line wrap

Option Explicit
Sub SheetSizer()

' determine the filesize of each sheet in a workbook
' Charles Williams 25/7/2003

Dim oBk As Workbook
Dim oSht As Worksheet
Dim strSheetname As String
Dim jSize As Long

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.xls")
jSize = FileLen(oBk.FullName)

Debug.Print "Book " & CStr(jSize \ 1024) & "KB"

On Error Resume Next
For Each oSht In oBk.Worksheets
strSheetname = oSht.Name
oSht.Delete
oBk.Save
If oBk.Worksheets.Count 1 Then
Debug.Print "Sheet " & strSheetname & " " & CStr((jSize -
FileLen(oBk.FullName)) \ 1024) & "KB"
End If
jSize = FileLen(oBk.FullName)
Next oSht

Debug.Print "Sheet " & strSheetname & " " & CStr((jSize) \ 1024) &
"KB"

Set oSht = Nothing
Set oBk = Nothing
End Sub


regards
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
"Tim Childs" wrote in message
...

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????

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

?????????????????????????????????????????????????? ??????????????????????????

?????????????????????????????????????????????????? ??????????????????????????
?????????????????????????????????????????????????? ????????

Finding the "Size" of Each Sheet in a Workbook
 

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