Excel file size grows with every save when cell data replaced via VBA

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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

(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:

Please Login or Register  to view this content.

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....?

I don't know if this will help, but "UsedRange" can sometimes behave strangely. Try this approach where you define exactly the range you want to copy. Change the column (in red) to the last used column in bendsht.

Please Login or Register  to view this content.

Try doing the following:

Before changing the data by typing in new dates in the date cells, on the both the Start Date and End Date sheets, place the cursor in cell A1, hold down the CTRL key and press the END key. The cursor will move to the last recognized cell in that sheet. Make note of that cell's address on both sheets. Next, type in a new date to replace the data in the Start or End Date sheets by loading the .csv file. Repeat the CTRL key/END key process for the 2 sheets that now contain the updated data. Are the addresses of the last cells selected the same as the previous ones you recorded?

OK. Post the files and I’ll have a look. Include the macros.

Zip file attached.
The sheets in the Excel file get protected - the unprotect password is in plain text in the code and is 1234

This is a variation of a series of workbooks that provide different views of meter usage data.
The csv files attached have meaningless data, and the input fields are irrelevant for seeing the file size growth.

You'd need to enable the macros, and use the Summary sheet.
Put a valid path in cell E5 to where the .csv files are located (although the code will display a file/open dialog if the path here is not valid).
For purpose of seeing file growth, all you have to do is click the 'Start=End' button, and save the file.
The sub that copies end to start is Sub button1_Click()


For purpose of seeing the file not grow, type in a new date in cell B3 of Summary Sheet.

Oh, and by the way, even easier way of seeing the basic issue:
If comment out the lines in theButton1_Click() sub that increments the date and loads new EndDate file, you can just repeatedly click the 'Start=End' button (which will repeatedly copy the same EndDate data to StartDate sheet), and when you save the file, the growth will be = to the # of times the button was clicked times (27kb) (the file growth with this sample file lower than my earlier post probably because of taking out some info from the .csv file for testing purposes).
The lines to comment out
' sumsht.Range("B5").Value = oday + 1
' Call CSV_Import2

I've been playing with the files for a while and tried different things. Try this version of the Sub button1_Click(). I believe there is still a small increase in the file but it is very small.

Please Login or Register  to view this content.

Originally Posted by Mumps1

I've been playing with the files for a while and tried different things. Try this version of the Sub button1_Click(). I believe there is still a small increase in the file but it is very small.

Please Login or Register  to view this content.

Sorry, didn't have chance to work on this until now and thanks for your suggestion.
The problem with doing it with the code you have - which does solve the growing problem - is the references to cells - the REPORT and FINAL sheets use values from cells on both the start and end sheets. With my code that causes file to grow, the references are intact and the REPORT/FINAL sheets are OK. With this code mod, the file doesn't grow, but all the column A, B and D in REPORT sheet are broken.

This almost seems like bug in Excel to me, and I guess may have to use the backup method of re-loading the .csv file. But it does introduce possibility of using different/modified .csv files which is what I was trying to avoid....

Unit Start Date End Date Begin Meter
#REF! #REF! 11/18/2020 #REF!
#REF! #REF! 11/18/2020 #REF!
#REF! #REF! 11/18/2020 #REF!
#REF! #REF! 11/18/2020 #REF!


OR perhaps is there some way to turn off auto-recalc or whatever needs to be turned off so that the cell references in REPORT and FINAL remain intact while the StartDate sheet is effectively cleared of all data, and then turn it back on after the copy/paste is completed?

i

s there some way to turn off auto-recalc or whatever needs to be turned off

You can try:

Please Login or Register  to view this content.

Keep in mind that whenever you delete rows, ranges or sheets, any formulas that refer to the deleted data will return an error.