PDA

View Full Version : Simple way to reduce the size of the Excel file



jack nt
09-17-2011, 09:06 AM
It is very simple when using codes of autor RDJ in below thread to reduce size of an excel file.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

In some cases, it is not very convenience to insert codes to an excel file (there's no code in it, for example). And maybe some one dislikes to do so.

I use a tiny workbook to do it without inserting the codes to my excel file .

1. Open an (big) excel file (to be reduced the size)

2. Open file attached ExcelDiet.xls

3. Choose the ComboBox named cboWorkbook, event Gotfocus occurs

Private Sub cboWorkBooks_GotFocus()
Dim wb As Object
cboWorkBooks.Clear
For Each wb In Workbooks
If UCase(wb.Name) <> "PERSONAL.XLS" _
And wb.Name <> ThisWorkbook.Name Then
cboWorkBooks.AddItem wb.Name
End If
Next
cboWorkBooks.Text = "Choose WorkBook..."
End Sub
4. Choose the file name in ComboBox List

5. Click the Button <Diet>

Private Sub cmdDiet_Click()
Dim wb As Object
For Each wb In Workbooks
If wb.Name = cboWorkBooks.Text Then
ExcelDiet wb
Exit For
End If
Next
End Sub
It calls Sub ExcelDiet(MyWorkBook As Workbook)
(a little difference from original codes of DRJ)

6. Waite some seconds

7. Save and close the excel file and check the size (It may reduced about 5~10 times)

Thank RDJ for your very useful thread.
Jack NT