File (7MB) takes 25s to save each time (?)

[d0rian]

I don't remember just when this happened (otherwise i could probably troubleshoot), but my file now takes 20-25s to fully complete the Save process every time I Ctrl-S save. It's a large-ish file (7-MB) with a lot of formulas spread out over a dozen sheets, but I don't remember it ever being this bad. And I have files twice as big that save in under 3 seconds (15-MB, albeit with nowhere near as many formulas)

1) What's best way to troubleshoot this?
2) Are there certain processes that are known to slow down the saving process that I should look to remove? (I thought maybe all the calculations were the culprit, but I turned off Auto-recalc and the save takes just as long...)

 

[rlv01]

Beyond the advice you've already received, here are a few more avenues that may or may not help.

1. Review 3rd party add-ins. In the past I've had some 3rd party add-ins have a severe effect on workbook closing and file saving.

2. Benchmark the file save performance on another PC.

3. See if Save-As instead of save makes a difference.

4. Save-as to binary file format (.xlsb), compare save time for binary version to the original format.

5. If your workbook contains macros, then use a code cleaner to clear out the "junk" that degrades performance in long-used programs. The manual way to do this is to export all forms, code module, worksheet code in the VBA project, then do a save-as to an .xlsx (macro free) format, then reimport all the code modules.

 

[Fluff]

A popup box with the number zero in it (which I can close by pressing the OK button on it.)

(Both you and RoryA seemed to ask about the Named Ranges issue...is that something that conceivably might be contributing to this? -- notwithstanding the fact that there don't appear to be any Named Ranges in the doc anymore...)

Sometimes you can get named ranges that don't appear in the Name manager. As the msgbox said 0 then that's not an issue in this case.

 

[RoryA]

If you’re ok with emailing it to me, send me a PM. otherwise I can’t think of much that’s completely sheet independent.

 

[SpillerBD]

Are there any Data Connections still listed?

 

[Taul]

Hi
I suspect there are hidden objects, usually command buttons that are being duplicated. Try this,
On the Home tab, go to Find and in the drop down list, select “Selection Pane”
This will list hidden object (if any)
You can purge them with Document inspector.
It is worth a try.

regards
Paul.

 

[d0rian]

Are there any Data Connections still listed?

Bingo!!

So in more troubleshooting, I isolated the start of this problem to around the time I added code given to me in this other thread to pull in data from a closed CSV file on my desktop. Very coincidentally, I actually made a follow-up post in that thread this morning, upon realizing that every time I ran that code, it was creating a new Named Range (with several hundred of such zombie named ranges in my Name Manager, and I didn't understand why that was happening so asked in that thread this a.m.)

Not sure if that Named Range issue is related to this Data Connection thing, but I opened the seemingly-empty but super-slow file, and in the Data Connections dialogue, there were ~320 different Workbook Connections...it seems like each time I run that code (from the other thread) it was creating a new Connection, with a Properties dialog box that looks like the image below.

I deleted the ~320 Connections and that solved the slow open/save issue (Thank you!!!). Now I just need to figure out whether that code from the other thread that imports data from my other file needs to create a new Connection every time it runs...I don't see why it would, nor why having several of those connections would create such bloat since I didn't think they were ACTIVE connections...I thought that each time I ran the code, it simply did a one-time grab of the data from the other file, but didn't maintain any sort of active connection...

 

[SpillerBD]

Bingo!!

I am not sure if MS made that change prior to 2013, but I noticed it would create the connection by default. For most users the "old" method is still a one-and-done process. It's nice that it can create a data connection, but as your case demonstrates it has a negative surprise.
I think I first noticed when receiving Workbooks from outside that still had the connection in them.

In the last step of the of data import, go through the Options box. There you can keep Excel from creating a Connection.