Saving xlsb with VB HUGE FILE SIZE

craig80

Board Regular
Joined
May 18, 2010
Messages
50
Hello All,

I have created a little bit of code that opens, calculates and resaves a few reports.

These newly calculated wbs are then saved to a communial network drive. as .xlsb.

All easy up to now, the machine i was using to do this, was on 2003. I am using 2007 to create all macros. I thought this was the reason the 11mb files were saving as 140mb. So, i made a request for 2007. Duely installed by IS, great it though... ran my code. Boom... 146mb now.

Has anyone any experience in this?

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=" "C:\Documents and Settings\Craig\Desktop\Master Report " & Format(Now() - 1, "dd mmm yyyy") & ".xlsb", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
ActiveWorkbook.Close
Application.DisplayAlerts = True

Original files are all xlsb also, so i am not sure what is going on.

Any help would be greatly appreciated.

Thanks

Craig
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Gixerstu

New Member
Joined
Oct 19, 2010
Messages
35
Go through each of your sheets and check to see what area is used.

I had a problem with this a while ago and it turned out that I had inadvertantly formatted some cells all the way to the bottom meaning that excel thought that the entire sheet was being used giving me massive file sizes.

The quick way to do it is to look at the scroll bar on the right, If the indicator is big then you should have no problem but if it is tiny then you have formatted a lot of cells you may not need.

Clear any formatting, conditional formatting and data validation in unneeded areas, save the file and you should be OK. Alternatively delete the rows.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,108
Messages
5,835,431
Members
430,357
Latest member
RadStorm20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top