copy

Sunday, 20 July 2014



Get Sheets Size Add-In


Introduction



Almost 5 months have passed since the last free add-in. 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 extraordinary large in size (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. 



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.

Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info