+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    11-20-2020
    Location
    USA
    MS-Off Ver
    Excel Version 2002 (12527.21330 Click-to-Run)
    Posts
    5

    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....?
    Last edited by claxa; 11-20-2020 at 04:22 PM.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,209

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

    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.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    11-20-2020
    Location
    USA
    MS-Off Ver
    Excel Version 2002 (12527.21330 Click-to-Run)
    Posts
    5

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

    Quote Originally Posted by Mumps1 View Post
    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.
    Thank you for the suggestion - the copy works with the method you suggest (I specified the end cell as the .csv file for each day is always the same size in rows/columns - bendsht.Range("A1:H395").Copy Destination....
    but, it does not change the growth of the Excel file when the workbook is saved.
    (My original post says it grows by about 80kb, but with the test file I'm using now the file is growing by 48kb. The .csv file is 41kb.)

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,209

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

    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?

  5. #5
    Registered User
    Join Date
    11-20-2020
    Location
    USA
    MS-Off Ver
    Excel Version 2002 (12527.21330 Click-to-Run)
    Posts
    5

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

    Thanks for trying to help Mumps1!
    Yes - the last cell remains the same (H395) before and after changing the date.
    I don't know if this would help, but I'm willing to cleanse any identifying data and post both the sheet and 3 .csv files if you'd like to give it a try.
    Every time the copy is done - even if it is replacing the values with the same values - the file size grows. (In other words, if I change the 'End Date' to match the 'Start Date', and run the Range().Copy the file size grows.)

    Quote Originally Posted by Mumps1 View Post
    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?

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,209

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

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

  7. #7
    Registered User
    Join Date
    11-20-2020
    Location
    USA
    MS-Off Ver
    Excel Version 2002 (12527.21330 Click-to-Run)
    Posts
    5

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

    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
    Attached Files Attached Files
    Last edited by claxa; 11-22-2020 at 06:04 PM.

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,209

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

    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.

  9. #9
    Registered User
    Join Date
    11-20-2020
    Location
    USA
    MS-Off Ver
    Excel Version 2002 (12527.21330 Click-to-Run)
    Posts
    5

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

    Quote Originally Posted by Mumps1 View Post
    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?
    Last edited by claxa; 11-25-2020 at 03:27 PM.

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,209

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Delete Columns; File Size Grows?
    By ptmuldoon in forum Excel General
    Replies: 1
    Last Post: 11-19-2020, 05:48 PM
  2. [SOLVED] Excel table size grows as another table grows
    By JimDandy in forum Excel General
    Replies: 0
    Last Post: 03-08-2017, 04:25 AM
  3. File size grows when using Macro to copy/paste to new Workbook
    By JabeSmith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2014, 03:04 AM
  4. Replies: 0
    Last Post: 05-18-2011, 07:43 AM
  5. Excel file size grows when copied
    By EsAssistant in forum Excel General
    Replies: 2
    Last Post: 12-10-2010, 05:53 AM
  6. File size grows to unrealistic proportions
    By kevsvette in forum Excel General
    Replies: 1
    Last Post: 06-06-2007, 09:20 PM
  7. [SOLVED] Temp file grows(no-end)when Excel97 attempts to save to net-2003Se
    By t.staudle in forum Excel General
    Replies: 0
    Last Post: 11-08-2005, 04:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1