Get Sheets Size – Excel 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

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 (or 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.

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

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