Excel Suddenly slow saving

%3CLINGO-SUB%20id%3D%22lingo-sub-3016252%22%20slang%3D%22en-US%22%3EExcel%20Suddenly%20slow%20saving%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3016252%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%20for%20Office%20365%2016.0.12527.22049%2064-bit%3C%2FP%3E%3CP%3EWindows%207%20and%20Windows%2010%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20two%20workbooks%20that%20are%20templates.%20One%20is%20used%20for%20around%20150%2B%20projects%20a%20year.%26nbsp%3B%20This%20workbook%20has%20the%20issue.%20The%20second%20workbook%20is%20a%20similar%20template%20that%20is%20used%20for%20about%2050%20projects%20a%20year.%26nbsp%3B%20This%20workbook%20does%20not%20have%20the%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%3A%3C%2FP%3E%3CP%3EWhen%20saving%20a%20workbook%20using%20the%20one%20template%20that%20is%20used%20for%20150%2B%20projects%2FYr%2C%20the%20save%20now%20takes%207%20to%2010%20minutes%20with%20the%20%22Saving%20(Not%20Responding)%22%20message.%20This%20just%20started%20on%20Friday%2011%2F26%2F2021.%20The%20day%20before%20there%20were%20no%20issues.%20Workbooks%20from%20this%20template%20that%20had%20previously%20saved%20quickly%20no%20longer%20save%20quickly.%26nbsp%3B%20Workbooks%20from%20the%20other%20template%20continue%20to%20work%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20does%20not%20appear%20to%20have%20been%20any%20updates%20applied%20to%20the%20system.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20happening%20to%20all%20users%20with%20workbooks%20based%20on%20the%20one%20template%2C%20but%20not%20the%20other.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20workbooks%20started%20from%20similar%20designs%20and%20use%20similar%20operations.%20They%20both%20have%2075%2B%20very%20hidden%20data%20tables%20used%20for%20dropdowns.%20The%20data%20tables%20query%20a%20SQL%20DB.%20HLookups%20and%20VLookups%20are%20used.%20There%20are%20several%20hundred%20field%20names.%20There%20is%20an%20Excel%20Add-in%20containing%20numerous%20functions.%20There%20is%20also%20a%20third-party%20VSTO%20add-in.%20This%20cannot%20be%20turned%20off%20(except%20maybe%20running%20in%20save%20mode).%20There%20are%20no%20external%20linked%20files.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20tried%3A%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3ETurning%20off%20Automatic%20workbook%20calculation%20and%20turned%20off%20Recalculate%20workbook%20before%20saving%20%E2%80%93%20No%20help%3C%2FLI%3E%3CLI%3EDisabled%20add-ins%20%E2%80%93%20no%20help%3C%2FLI%3E%3CLI%3EMoved%20project%20files%20to%20local%20drive%20%E2%80%93%20no%20help%3C%2FLI%3E%3CLI%3ERan%20Excel%20in%20Safe%20Mode%20-%20No%20help%3C%2FLI%3E%3CLI%3EWorkstations%20and%20Server%20have%20been%20restarted%20-%20No%20Help%3C%2FLI%3E%3CLI%3EDeleted%20tabs%20until%20problem%20went%20away.%20Did%20not%20get%20narrowed%20down%20to%20a%20single%20tab.%20Seems%20to%20be%20a%20combination%20somewhere%20among%20about%2015%20tabs.%3C%2FLI%3E%3CLI%3EMoved%20project%20files%20to%20local%20drive%20and%20removed%20workstation%20from%20network%20%E2%80%93%20problem%20goes%20away.%20The%20remaining%20tabs%20use%20a%20lot%20of%20the%20same%20advanced%20formulas.%20Since%20the%20problem%20goes%20away%20when%20not%20attached%20to%20the%20network%2C%20I%20did%20not%20attach%20the%20workbook.%20The%20workbook%20is%20basically%20the%20work%20product%20for%20our%20business%2C%20so%20the%20whole%20thing%20is%20pretty%20proprietary.%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3CP%3EAt%20a%20complete%20loss%20as%20to%20what%20to%20look%20at%20next.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3016252%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3018098%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Suddenly%20slow%20saving%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3018098%22%20slang%3D%22en-US%22%3EDownload%20my%20name%20manager%20from%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fjkp-ads.com%2Fofficemarketplacenm-en.asp%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjkp-ads.com%2Fofficemarketplacenm-en.asp%3C%2FA%3E%20and%20run%20it%2C%20check%20for%20range%20names%20with%20external%20references.%20It%20may%20be%20some%20point%20to%20drives%20that%20are%20no%20longer%20available.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3018181%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Suddenly%20slow%20saving%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3018181%22%20slang%3D%22en-US%22%3EI%20have%20noticed%20similar%20issues%20over%20the%20past%20week%20with%20our%20complex%20model%20though%20less%20sophisticated%20than%20yours.%20Have%20there%20been%20changes%20to%20Excel%20that%20might%20have%20triggered%20this%20%5Bnot%20responding%5D%20error%20message%20I%20wonder%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3018195%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Suddenly%20slow%20saving%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3018195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1232969%22%20target%3D%22_blank%22%3E%40KTerrell32707%3C%2FA%3E%26nbsp%3B1.%20How%20%22clean%22%20is%20your%20file%3F%202.%20Any%20VBA%20code%20running%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Excel for Office 365 16.0.12527.22049 64-bit

Windows 7 and Windows 10

 

We have two workbooks that are templates. One is used for around 150+ projects a year.  This workbook has the issue. The second workbook is a similar template that is used for about 50 projects a year.  This workbook does not have the issue.

 

Problem:

When saving a workbook using the one template that is used for 150+ projects/Yr, the save now takes 7 to 10 minutes with the "Saving (Not Responding)" message. This just started on Friday 11/26/2021. The day before there were no issues. Workbooks from this template that had previously saved quickly no longer save quickly.  Workbooks from the other template continue to work correctly.

 

There does not appear to have been any updates applied to the system. 

 

This is happening to all users with workbooks based on the one template, but not the other. 

 

The workbooks started from similar designs and use similar operations. They both have 75+ very hidden data tables used for dropdowns. The data tables query a SQL DB. HLookups and VLookups are used. There are several hundred field names. There is an Excel Add-in containing numerous functions. There is also a third-party VSTO add-in. This cannot be turned off (except maybe running in save mode). There are no external linked files.

 

Have tried: 

  • Turning off Automatic workbook calculation and turned off Recalculate workbook before saving – No help
  • Disabled add-ins – no help
  • Moved project files to local drive – no help
  • Ran Excel in Safe Mode - No help
  • Workstations and Server have been restarted - No Help
  • Deleted tabs until problem went away. Did not get narrowed down to a single tab. Seems to be a combination somewhere among about 15 tabs.
  • Moved project files to local drive and removed workstation from network – problem goes away. The remaining tabs use a lot of the same advanced formulas. Since the problem goes away when not attached to the network, I did not attach the workbook. The workbook is basically the work product for our business, so the whole thing is pretty proprietary. 

At a complete loss as to what to look at next.

 

7 Replies
Download my name manager from here: https://jkp-ads.com/officemarketplacenm-en.asp and run it, check for range names with external references. It may be some point to drives that are no longer available.
I have noticed similar issues over the past week with our complex model though less sophisticated than yours. Have there been changes to Excel that might have triggered this [not responding] error message I wonder

@KTerrell32707 1. How "clean" is your file? 2. Any VBA code running?

@Jan Karel Pieterse. Yes, there is VBA running. It is all in an Excel add-in (Macros in an excel file that is added as an add-in). A third party that wrote VSTO code that disables menus, adds a tab to the ribbon and does not allow the workbook to be macro enable, so there are no macro's local to the workbook, only in the add-in. The add-in is an Excel add-in. We unchecked it under add-ins (disabled) and still have long saves.

 

As for clean, this happens to all workbooks that are based on this template workbook (copy of) and on this master.  

Thank you. I had already checked for external links. Your tool makes it a whole lot easier. Thanks for that. However, there was on external link that was to a location no longer accessible, but cleaning that up did not help. Your tool helped me clean that up.
I'd be happy to have a look at the file. I'll sign an nda if you want. My email address is at the footer of my website.
This won't help with any underlying issues but I've started to use .xlsb files for everything. It's a binary format that cuts file size and load/save time by half. Macro enabled too so no issues there.