Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding the "Size" of Each Sheet in a Workbook
?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ????.????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????????????????? ??????????????????????????????????????????
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ... ?????????????????????????????????????????????????? ?????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????? ????????????????????????????????????????????????.? ?????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????? ?????????????????????????????????????????????????? ???????? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
how to increase size of "name box" and "contents of cell " displa. | New Users to Excel | |||
cannot use "Create List" and "Share Workbook" same time | Excel Discussion (Misc queries) | |||
"SHEET TAB FONT SIZE"? | Excel Worksheet Functions |