PDA

View Full Version : Excel 2010 huge file size



ropro
09-27-2013, 11:39 PM
Hi,

I created excel sheet with VBA from Excel 2010. When I saving the file it ask me to save it as "Macro - Enabled work book" otherwise VBA part is not working. So I saved the file as Macro - Enabled one. But the problem is that the file size is large (Around 45 MB) .
I can't place this size of file in our office network. Does anyone knows how to reduce the file size. Thanks in advance. :eek:

rollis13
09-28-2013, 02:07 AM
Start a new book, copy only what you really need from the maxi-file (without using the macro) and see if it saves in smaller size. Then check your macro work and see if the size grows.

SamT
09-28-2013, 12:38 PM
Look at any controls or buttons on the sheet(s) and drag them away. Are there any controls hiding under them. Delete the hidden ones.

Do you really need to save any Filters on any sheet?

Do you have any images in the workbook? Did you resize the images smaller, use fewer colors, and convert them to a smaller file format before loading them?

Do you use images on buttons? Name the buttons and copy them by name to create new buttons with different names.

Do you have any formatting you don't really need?

Look at the size of the scroll bar. Drag it to the bottom of the scroll area. Are you below or to the right of any Data? Find the real last used cell at the bottom and right of the data and delete all columns and rows that are to the right of and or below the real last used cell. Save immediately after each Delete operation and before the next one.

Use Name Manager (http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp) to remove any unused Names.

Read Excel Pivot Table -- Clear Old Items (http://www.contextures.com/xlPivot04.html) if you have any in the workbook.

Finally. save it as xlsb, then open the xlsb and save that as xlsm.

Paul_Hossler
09-29-2013, 04:11 PM
When that happens to me, I rename the .xlsm as .zip and open it

I don't understand a lot of the structure and XML, but many times I get enough insight as to where are the MB's are going to help.

That usually helps narrow down the detective work

Could be something like a worksheet the just 'looks too big' for example. Lots of time just copy / pasting JUST THE CELLS I WANT into a new sheet and deleting the original will shrink it.

Rob Bovey's Code Cleaner (great tool) might help also

http://www.appspro.com/Utilities/CodeCleaner.htm


Paul