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

d0rian

Active Member
Joined
May 30, 2015
Messages
313
Office Version
  1. 365
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...)
 
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.
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...

yAsnSMH.jpg
 
Upvote 0

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.
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top