Get Sheets Size – Excel Add-In

Share this

July 20, 2014

Get Sheets Size Add In


Introduction


Almost 5 months have passed since the last free add-in that I published. So, today we will see how we can find the largest sheet (in size) from a given workbook. I am sure that some of you may wonder why on earth this information will be useful to someone. Well, the answer is that in a multi-sheet workbook, there are times – usually after some sort of file corruption – where one of the sheets becomes extraordinarily large (i.e. from a few KBs it might become a few MBs). If you had for example 10 or 20 sheets in your workbook, how you would identify the one that causes the size problem? This add-in might give you the answer.

 


Add-in description


Get Sheets Size Ribbon Button

After installing the add-in (see the video below for instructions), in the Home tab of the Ribbon a new button – “Get Sheets Size” – will be shown. If you click this button, the add-in will perform the following actions:

  1. The active workbook will be saved before the actual procedure starts. If for some reason the workbook was not saved before, the add-in will stop and a message box will pop up. In other words, the add-in works with workbooks that have been saved at least once.

  2. After saving the workbook, the add-in exports each sheet into a temporary file. By using the File System Object (FSO), the add-in gets the temporary file size and then the file is deleted.
  3. When the size of every sheet is known, the add-in creates a new single-sheet workbook in which it writes down all the information regarding the individual sheet and the entire workbook. Moreover, it points out the largest sheet in size (in yellow color), as well as the hidden sheets.

Get Sheets Size Sample Results

Note: Bear in mind that in quite large workbooks the add-in might runs even for a few minutes before the report is completed.

 


How to read the sheets sizes from a workbook – demonstration video


The short video below demonstrates the installation and use of the add-in. A multi-sheet workbook is used as a test case.

 


Downloads


Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Page last modified: March 2, 2020

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

  • Christos Spiliopoulos says:

    Thanks, useful add-in

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >