I have created an .xlsm file which imports/copies data from an .xls file, but after working for a while the size of the workbook is enormous (60Mbytes).
I use the Clear method to eliminate the data of the cells every time I close the workbook, but the size keeps incresing. Clearcontents and delete don't help either.
Finally, I created a code to clean the workbook every time I close it:
The admin_Tools_PivotTablesClear refreshes the Pivot Tables once the data has been deleted, so the charts apppear blank.
The other function cleans all the cells responsible for the increse of the size:
This function resets the size to 10Mbytes, which is quiet accceptable, although maybe it's still too much. The Workbook has 6 worksheets and most of them must be empty. The only worksheet which remains with data is Sheet1, that has 4 images, 5 combo boxes, 5 checkboxes, 2 labels, two textboxes and 6 pivot tables + 6 charts.
The cleaning code is quite slow and when I try not to use it and simply cleaning the worksheets which contain the tables for the ranges that the pivot tables use by using .range("A1:BD65530").delete the size remains huge.
==============================================================
EDIT:
In the end it turned out to be a problem of accumulation of formats. I took the code from an Addin in the MSDN Knowledge base and I could turn the 120 MBytes file into a 543 KBytes file.
The code eliminated unexplicably some elements of my workbook, but with some modifications to these elements it works perfectly now.
I hope this may be useful to someone else.
Bookmarks