0

Working on Windwos 10 Enterprise with Excel from Office 365 version 2204, I have a large .xlsb file (>14MB) and want to determine the size of the single worksheets in kB/MB, optimally saving this information in tabulated form.

I have tried (various versions of these) two approaches without success

  1. zip-File, e.g., described here
    Renaming the file-type to .zip and looking into FILENAME/xl/workbook.xml and the FILENAME/xl/worksheets folder does not work: Whenever I would expect a .xml-file, I find a .bin-file, which I cannot read and furthermore, the sum of single sheet sizes is way too high.
  2. Macros, e.g., here
    There are several differnt versions of macros out there, which all break in my case at a line where they measure the size of current file with the function FileLen(...). I couldn't find out if this error happens due to working in an .xlsb or whether a limit of the function is reached.

How can I determine the size of each worksheet?

Any thoughts appreciated. Thanks in advance!

6
  • Open the file? What do you mean, exactly, by the size of a worksheet? May 6, 2022 at 16:54
  • @cybernetic.nomad The entire .xlsb file is a "workbook", containing several "worksheets" - see screenshot/explanation here: excelnotes.com/…
    – MathProb
    May 6, 2022 at 17:06
  • Thanks, in spite of using Excel for decades now, I had absolutely no idea... May 6, 2022 at 17:51
  • That's not really hope XLSB files work, there's some overhead, even with an empty workbook the file has a particular size, but zipping (compressing) in general works better with similar content, so a workbook with two worksheets that are similar will compress more. The worksheets in separate workbooks would add up to far greater size than the size of a combined workbook. You could estimate based on number of filled cells per worksheet if you want to know what each sheet contributes to the overall size? Any reason for this?
    – pbhj
    May 6, 2022 at 19:08
  • @pbhj Thanks for the background and the next ideas! The reason I am doing this is as follows: The .xlsb file is a excel tool (containing many formulas, (conditional) formatting, references, etc.) which should be used by many people, thus I want to understand how I can scale down on size to make it more usable - to scale down size, I need to know where the size is hidden. [Just for the record: a python based web-app or similar is unfortunately no option]. Since formatting and formulas are a large part of it, only looking at number of cells won't likely be sufficient.
    – MathProb
    May 6, 2022 at 19:35

1 Answer 1

0

The article How to check the size of each worksheet of workbook proposes using a VBA macro for this calculation.

  • Hold ALT+F11 to open the VBA editor

  • Click menu Insert > Module

  • Paste the following code in the Module Window :

      Sub WorksheetSizes()
      Dim xWs As Worksheet
      Dim Rng As Range
      Dim xOutWs As Worksheet
      Dim xOutFile As String
      Dim xOutName As String
      xOutName = "KutoolsforExcel"
      xOutFile = ThisWorkbook.Path & "\TempWb.xls"
      On Error Resume Next
      Application.DisplayAlerts = False
      Err = 0
      Set xOutWs = Application.Worksheets(xOutName)
      If Err = 0 Then
          xOutWs.Delete
          Err = 0
      End If
      With Application.ActiveWorkbook.Worksheets.Add(Before:=Application.Worksheets(1))
          .Name = xOutName
          .Range("A1").Resize(1, 2).Value = Array("Worksheet Name", "Size")
      End With
      Set xOutWs = Application.Worksheets(xOutName)
      Application.ScreenUpdating = False
      xIndex = 1
      For Each xWs In Application.ActiveWorkbook.Worksheets
          If xWs.Name <> xOutName Then
              xWs.Copy
              Application.ActiveWorkbook.SaveAs xOutFile
              Application.ActiveWorkbook.Close SaveChanges:=False
              Set Rng = xOutWs.Range("A1").Offset(xIndex, 0)
              Rng.Resize(1, 2).Value = Array(xWs.Name, VBA.FileLen(xOutFile))
              Kill xOutFile
              xIndex = xIndex + 1
          End If
      Next
      Application.ScreenUpdating = True
      Application.Application.DisplayAlerts = True
      End Sub
    
  • Press F5 key to execute this code

  • A new worksheet named "KutoolsforExcel" will be inserted into the current workbook which contains each worksheet name and file size in bytes:

    enter image description here

1
  • thanks for pointing me to this. Interestingly, this macro breaks without any error message / room to debug. When running the full macro it breaks after some 15 worksheets (Excel just closes the workbook), If I am running the macro just for a single sheet, it breaks immediately. Changing the name of the temporary workbook to TempWB.xlsb increased performance slightly, but didn't change the break without error message. I am really puzzled.
    – MathProb
    May 6, 2022 at 20:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .