VBA: Workbook size unusually large

  • I have an Excel workbook with approx 20 worksheets that is approx 14 Meg in size. Each of these worksheets has approx 250 rows and 90 columns. This is a consolidation worksheet and, as such, consolidates data from multiple workbooks/worksheets in each of these tabs. This is done through embedded links in many of the cells of each of the worksheets. These links are updated as needed when there have been changes in any of the underlying linked workbooks. This is done through macros. As a result, data is stored in this workbook when the links have been first updated.


    I have two questions related to this workbook:


    1. The size of this workbook seems very large considering the size of each related worksheet. Is this due to the embedded links? Is there any way to compress this workbook and reduce any artificial size?


    2. Is there a way to "zero out" the data in the workbook so that only a template with all the defined links is left (i.e., no updated links to bring in any data)?


    Thanks.

  • Hi Starman,


    Your summation that the links are causing the size of the spreadsheet to be monsterous is correct. I'm not sure of a way to reduce the size but for your second question you could set up a list of all of the tabs on a seperate sheet (controlsheet) that have links. then in the column next to the list insert either a 0 or 1 if you want the data shown on the tabs. Then go to the tabs and where you have a link * that by the 0 or 1 that is on the controlsheet. by doing this you can then control what data is reported and what is omitted.


    HTH


    Graeme

  • Thanks Dave & Graeme.


    I tried doing some of the recommendations from the article you mentioned Dave. It didn't seem to help unfortunately. Size really didn't decrease.


    Each of these worksheets has probably hundreds of links to other worksheets. An I really can't delete them since the system is based on those links. What I'm understanding from Graeme's comment is that this in itself may result in the file being so large. Is this the case?


    I would like to create just a template file from this file (i.e., one that does not contain any data but just the link references). I'm assuming that if I can do that, the resulting template file would be signficantly reduced in size. Is this a correct assumption?


    Assuming this is the case, re: your suggestion Graeme:


    "...you could set up a list of all of the tabs on a seperate sheet (controlsheet) that have links. then in the column next to the list insert either a 0 or 1 if you want the data shown on the tabs. Then go to the tabs and where you have a link * that by the 0 or 1 that is on the controlsheet. by doing this you can then control what data is reported and what is omitted. "


    I'm confused on how to actually implement this. Can you clarify this further for me? How would I apply this logic to the hundreds of link references in each related spreadsheet?


    Thanks.

  • I've had this problem quite often.
    it is possible for workbooks size to become large. The following things are ususally what I do to try to tone down the size.


    First i usually try to copy and paste special into a new book. By using paste special i would only copy the values and format of the certain function. Secondly if you have a lot of blank rows that is unecessary on the work book just delete it it would greatly help reduce the size.


    Any reduction of formatting help. Instead of painting the book white i usually just go to Tool --> Option --> View --> Window Option and uncheck the Gridline column instead of painting the whole worksheet white.


    Thanks hope this helps !!

  • Hi Starman


    I often find that file size problems can be due to one corrupt Worksheet. The way to tell is to save a copy, then with that copy delete one sheet at a time, save then go to File>Properties and chck the size. You may well find that a particular sheet, once deleted, will decrease the size a lot more than the others.

  • I've had success with reducing file size for my addin by opening and then saving the file in a different version of Excel. For instance, I have Excel 2000 on one machine and Excel XP on the other. Whichever system I am developing on, I open the fiole in the other system and save it and it removes the bloat. My addin file can get up to 2 MB and then after this it goes down to 900KB.


    HTH

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!