I have a workbook with 5 sheets
External CSV data files created daily by another application
The Summary/control sheet, which has the VBA code, allows user to specify dates - when start or end date cell is changed, the code loads the external .csv file into the appropriate start or end worksheet.
A fourth Report sheet has references to both the start and end sheets to perform calculations. (The fifth sheet contains one line summary of data for each day that updates when another button is clicked to transfer the day summary data.)
On the summary sheet, I added a button 'Next day' that when clicked calls code that copies all the data from the End Date to the Start Date sheet, increments the end date by 1, and calls the sub that loads the external .csv data into the End Date sheet.
So, the start and end date cells can be changed either by user typing in a date, or by clicking the button. Everything works.
So the number of cells used in the workbook does not change - data is being replaced.
When the data is changed by typing in new dates in the date cells (which causes Start or End Date sheet data to be replaced by loading the .csv file as a connection), and the workbook is saved, the files size grows by about 1kb. If we start at day 1, the file size is about 250Kb. Each .csv file is about 44kb.
But, after the button is clicked that runs the code to automatically change the dates, when the file is saved, the file size grows by 80kb. Since the idea is to use this everyday, basically forever, before too long the file size is into the Mbs, and there comes a point where the Excel task is basically running a core of the processor at 100% and responsiveness to cursor movements etc is very very slow.
What I found is, if I totally delete the Start Date sheet from the workbook, and then insert a new blank Start Date sheet, the file size goes back to the original 250kb or so. (Deleting the sheet takes a LONG time if the file has grown into the MBs.) I have to fix #REFs in other sheet when I do this as cells that reference data from StartDate are affected when the sheet is deleted.
So, stuck on WHY this is happening!
This is the code to COPY all the data from EndDate sheet to StartDate sheet (which replaces all the existing cell data in StartDate), and the file size grows:
Doing this causes Excel file size to increase by 80kb. Apparently, in the Excel file itself, all the old/replaced cell data is stored in the file. And is never deleted. And is not visible anywhere, nor is there any way to access or use it that I know of - it shouldn't be there!
As I was composing this message to ask for help, happened to find a thread with similar problem on another forum, which stops the file size from growing, but breaks the functionality of the workbook
If I add
before the copy one problem is solved - the file size does not grow - BUT, that code changes all the cell references in the other sheets that reference the StartDate data to #REF!
So, it's not really a practical solution, because the sheet that calculates the differences between start/end is undone & broken.
Now, my temporary solution is to not try to copy 'End Sheet' to 'Start Sheet', but to just call the code that loads the .csv file into Start Sheet with the date from the End Sheet.
Is there a way to copy the data in one sheet to the other without losing the references and without the file size growing?
Maybe this is already covered here, but I couldn't find with search of forum.
The other sub that loads the .CSV file into Start or End Date sheet doesn't cause Excel file size to explode, and replaces the old data without the other sheets losing their references - using this code:
(I tried putting the UsedRange.Clear before the copy in the first code above, but it doesn't fix the file size growing.
Also, found that the code that loads the .csv file (strFile) creates a connection that gets saved - not needed - and the list of these connections grows each time a file is loaded.
So, I added code in ThisWorkbook module to delete all the connections every time the file is saved:
Even with these methods, the file size still grows by 1KB with every date re-load. I guess can live with that, but there must be some way to do this better....?
Bookmarks