PDA

View Full Version : Reduce size of Excel file



nals14
08-10-2007, 05:43 PM
Hi,
I have an excel file with 9 sheets and 2 Userforms. The sheets have been formatted and coloured, but nothing more. I do have a lot of code to validate the data entry and manipulate the data.

The size is around 850MB. I have to mail these out to the users in the field. If they have a shabby connection the file would take forever to e-mail.

Is there anyway to reduce the size of the file? Should I have my code as an addin. The code is invoked for almost every single data entry the user does. Is it advisable to use add-ins in such a case?

Is there any other option available?

Appreciate your help.

Thanks:hi:

YellowLabPro
08-10-2007, 07:26 PM
First let me ask you are you positive it is 850mg, maybe not 85mg... that is bigger than a fully filled Cd.
But if that is the case, are you zipping the file to see how that will reduce it down?
There is a macro, Excel Diet that will work on reducing the size of the file, if you can't find it w/ a search, I will post the code for you.
If your file is full of formulas, you could change these to values, leaving a copy of the formula in a row above the first used cell and then write a bit to fill down if the user you are mailing it to needs to have formulas in the sheet.

Those are the first things that come to mind....

Paul_Hossler
08-11-2007, 08:11 AM
I've seem WBs grow to a totally unreasonable size when they get corrupted somehow, or when the LastUsed is off to the far lower right, even if there's no data in that cekk any more.

1. Try starting with a new, blank WB, and Copy&Paste each WS over (not copying the WS), but doing it the hard way.

Then do the same for module code. UFs will probaly have to be exported and then imported.

2. Rob Bovey has a Excel Code Cleaner that I really like that does a great job of cleaning up heavily edited VBA code (http://www.appspro.com/Utilities/CodeCleaner.htm)

I don't think it cleans WSs, but it does solve a lot of VBA problems for me (or least the one I don't cause for myself :( )

Between the two, it might solve the Workbook bloat

Paul

nals14
08-11-2007, 09:22 AM
Hi,
I feel so incredibly stupid. Sorry guys yes it was a typo. The size is 850KB and don't want the size to get to 1MB!

I don't have forumals as such. It's just macro code and formatting of the sheet.

Thanks for all your help.

Regards,
Nalini

YellowLabPro
08-11-2007, 09:28 AM
Nalini,
Not to worry, but 1mg is now very small comparatively speaking. If you want it smaller to email, you could still use the first suggestions... zip, or run this code prior, then zip.

Sub ExcelDiet()

Dim x As Integer
Dim LastRow As Long
Dim LastCol As Integer

Application.ScreenUpdating = False

On Error Resume Next

For x = 1 To Sheets.Count
Sheets(x).Activate
LastRow = Cells.Find(What:="*", after:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", after:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete
Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete
Next x

On Error GoTo 0

Application.ScreenUpdating = True
[a1].Activate

End Sub

nals14
08-13-2007, 09:47 AM
Thank you very much for your suggestion. Will zip the file after using the given subroutine.

Really appreciate your help.

Thanks,
Nalini


Nalini,
Not to worry, but 1mg is now very small comparatively speaking. If you want it smaller to email, you could still use the first suggestions... zip, or run this code prior, then zip.

Sub ExcelDiet()

Dim x As Integer
Dim LastRow As Long
Dim LastCol As Integer

Application.ScreenUpdating = False

On Error Resume Next

For x = 1 To Sheets.Count
Sheets(x).Activate
LastRow = Cells.Find(What:="*", after:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", after:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Range(Cells(1, LastCol + 1), Cells(65536, 256)).Delete
Range(Cells(LastRow + 1, 1), Cells(65536, 256)).Delete
Next x

On Error GoTo 0

Application.ScreenUpdating = True
[a1].Activate

End Sub