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
[vba]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[/vba]
4. Choose the file name in ComboBox List
5. Click the Button <Diet>
[vba]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
[/vba] 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
Last edited by Aussiebear; 09-17-2011 at 04:59 PM.
Reason: Added vba tags to code