can't delete old solver data. can't save file where solver was reset.

%3CLINGO-SUB%20id%3D%22lingo-sub-94918%22%20slang%3D%22en-US%22%3Ecan't%20delete%20old%20solver%20data.%20can't%20save%20file%20where%20solver%20was%20reset.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-94918%22%20slang%3D%22en-US%22%3E%3CP%3EI%20work%20with%20workbooks%20having%20multiple%20tabs%20and%20about%20200%2C000%20cells%2C%2090%25%20of%20which%20have%20formulas.%26nbsp%3B%26nbsp%3B%20I%20use%20the%20Solver%20addin%20for%20these%20workbooks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20run%20into%20two%20bugs%20in%20Excel%20365%2C%2064%20bit%20which%20is%20preventing%20me%20from%20using%20Solver%2C%20and%20preventing%20me%20from%20saving%20a%20worksheet%20which%20took%20hundreds%20of%20hours%20to%20create%20and%20refine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20have%20a%20small%20table%20containing%20the%20list%20of%20variables%20for%20the%20entire%20worksheet%2C%20on%20worksheet%20%22A%22%20and%20cut%2Fpaste%20that%20table%20to%20an%20empty%20space%20in%20worksheet%20%22B%22%2C%20open%20solver%2C%20hit%20the%20%22reset%20all%22%20button%20and%20then%20enter%20new%20data%2C%20constraints%20etc.%2C%20Solver%20will%20rename%20some%20of%20the%20constraints%20to%20some%20kind%20of%20code%2C%20rather%20than%20the%20expected%20formulation%20(e.g.%20%22B5%26gt%3B%3D1%22).%26nbsp%3B%26nbsp%3B%20Solver%20will%20instead%20save%20that%20as%20something%20like%3CEM%3E%20solver_lhs5%20%26gt%3B%3D1%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20this%20screenshot%2C%20and%20notice%20last%20two%20constraints.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.dropbox.com%2Fs%2Fns4bd6w2n6nmy7n%2FScreenshot%25202017-08-09%252014.01.16.png%3Fdl%3D0%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.dropbox.com%2Fs%2Fns4bd6w2n6nmy7n%2FScreenshot%25202017-08-09%252014.01.16.png%3Fdl%3D0%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20try%20the%20%22reset%20all%22%20button%20again%2C%20the%20problem%20is%20replicated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorse%2C%20if%20I%20hit%20%22reset%20all%22%20button%20and%20try%20to%20save%20the%20workbook%20I%20get%20this%20message%3A%26nbsp%3B%20%22Errors%20were%20detected%20while%20saving%20___%20.%20Microsoft%20Excel%20may%20be%20able%20to%20save%20the%20file%20by%20removing%20or%20repairing%20some%20features.%20To%20make%20the%20repairs%20in%20a%20new%20file%2C%20click%20Continue...%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20click%20%22Continue%22%20and%20get%20another%20error%20message%20saying%20%22document%20not%20saved.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20were%20a%20simple%20worksheet%20I'd%20just%20copy%20it%20over%20to%20a%20new%20one.%26nbsp%3B%20But%20this%20is%20a%20complex%20workbook%2C%20and%20in%20the%20past%20when%20I've%20tried%20to%20copy%2Fpaste%20pages%20from%20it%2C%20the%20new%20workbook%20will%20contain%20references%20in%20its%20formulas%20back%20to%20the%20orignial%20workbook.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20That%20means%20I%20have%20to%20discover%2C%20find%2Freplace%20those%20references.%26nbsp%3B%20A%20tedious%20task!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS%2C%20when%20I%20first%20contacted%20Microsoft%20about%20this%20I%20was%20first%20connecteed%20to%20a%20tech%20who%20could%20not%20solve%20the%20problem.%20%26nbsp%3B%20As%20a%20last%20resort%20she%20suggested%20doing%20an%20%22online%20repair%22%20which%20I%20did.%20%26nbsp%3B%20After%20the%20online%20repair%2C%20the%20suite%20notifed%20me%20that%20my%20software%20wasn't%20activated.%20%26nbsp%3B%20But%20when%20trying%20to%20activate%20it%2C%20I%20was%20presented%20with%20a%20window%20saying%20that%20the%20page%20could%20not%20be%20found.%20%26nbsp%3B%20I%20contacted%20Microsoft%20again.%26nbsp%3B%20A%20new%20tech%20suggested%20remote%20access.%26nbsp%3B%20He%20managed%20to%20screw%20up%20the%20VPS%20on%20which%20I%20was%20running%20office%2C%20leaving%20me%20unable%20to%20connect%20to%20it.%20%26nbsp%3B%20I%20got%20that%20resolved%20on%20my%20own.%20%26nbsp%3B%26nbsp%3B%20In%20any%20case%2C%20this%20issue%20is%20not%20confined%20to%20this%20one%20computer%20or%20workbook.%26nbsp%3B%20It%20occurs%20on%20another%20computer%20with%20another%20version%20of%20my%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20might%20be%20more%20efficient%20to%20resolve%20this%20issue%20if%20someone%20could%20tell%20me%20how%20to%20copy%2Fpaste%20a%20complex%20workbook%20to%20a%20new%20one%2C%20in%20a%20way%20which%20does%20not%20refer%20formulas%20back%20to%20the%20original%20one.%26nbsp%3B%20Then%20in%20the%20future%2C%20I%20just%20won't%20try%20to%20move%20my%20%22solver%22%20inputs%20to%20another%20worksheet!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-94918%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-95130%22%20slang%3D%22en-US%22%3ERe%3A%20can't%20delete%20old%20solver%20data.%20can't%20save%20file%20where%20solver%20was%20reset.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-95130%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20thanks%2C%20I%20did%20try%20removing%20solver%20and%20rebooting%20but%20that%20didn't%20work.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20posting%20in%20other%20forums%20for%20help%20and%20working%20on%20other%20ideas...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-95094%22%20slang%3D%22en-US%22%3ERe%3A%20can't%20delete%20old%20solver%20data.%20can't%20save%20file%20where%20solver%20was%20reset.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-95094%22%20slang%3D%22en-US%22%3E%3CP%3EWalter%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20it%20always%20will%20be%20here.%20Another%20story%20if%20nobody%20answers%20in%20few%20days%20there%20are%20low%20chances%20it'll%20be%20answered%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGroup%20Edit%20mode%20-%20that%20means%20few%20or%20all%20of%20your%20sheets%20are%20selected.%20I%20guess%20Solver%20shall%20not%20to%20work%20in%20such%20mode%20by%20design.%20By%20the%20way%2C%20i%20forgot%20there%20is%20Select%20All%20%2F%20Ungroup%20in%20right%20click%20menu%20for%20sheets%20tabs.%20Ungroup%20them%20before%20doing%20something%20with%20Solver.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20one%20point%20more%20-%20did%20you%20try%20to%20disable%20Solver%20Add-in%20in%20Excel%20Options%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-95045%22%20slang%3D%22en-US%22%3ERe%3A%20can't%20delete%20old%20solver%20data.%20can't%20save%20file%20where%20solver%20was%20reset.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-95045%22%20slang%3D%22en-US%22%3E%3CP%3EThanks.%26nbsp%3B%20This%20is%20my%20first%20time%20in%20this%20foum.%20Will%20my%20post%20remain%20%22alive%22%20until%20someone%20else%20sees%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-95032%22%20slang%3D%22en-US%22%3ERe%3A%20can't%20delete%20old%20solver%20data.%20can't%20save%20file%20where%20solver%20was%20reset.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-95032%22%20slang%3D%22en-US%22%3E%3CP%3EWalter%2C%20sorry%2C%20but%20i%20never%20worked%20with%20Solver%2C%20can't%20help%20with%20fixing%20of%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20in%20case%2C%20some%20ago%20i%20had%20an%20issue%20with%20%22file%20can't%20be%20saved%22%2C%20fixed%20that%20by%20switching%20off%20Autosave%20option.%20That's%20not%20your%20case%2C%20but%20who%20knows.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-95029%22%20slang%3D%22en-US%22%3ERe%3A%20can't%20delete%20old%20solver%20data.%20can't%20save%20file%20where%20solver%20was%20reset.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-95029%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20but%20this%20led%20to%20another%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20first%20tried%20your%20suggestion%2C%20but%20the%20Solver%20issue%20reappeared%20in%20the%20new%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I%20decided%20to%20%22reset%20all%22%20in%20Solver%20%3CEM%3Ebefore%3C%2FEM%3E%20copying%20the%20sheet%20to%20a%20fresh%20book.%20%26nbsp%3B%20When%20I%20tried%20that%2C%20I%20was%20presented%20with%20a%20new%20error%20message%20%22Cannot%20do%20this%20command%20in%20Group%20Edit%20Mode.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20googled%20a%20bit%20trying%20to%20find%20a%20simple%20fix%20but%20the%20articles%20I%20pulled%20up%20had%20more%20to%20to%20with%20setting%20up%20group%20mode%20than%20removing%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3Bso...%20what%20next%3F%26nbsp%3B%20Thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-94941%22%20slang%3D%22en-US%22%3ERe%3A%20can't%20delete%20old%20solver%20data.%20can't%20save%20file%20where%20solver%20was%20reset.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-94941%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Walter%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDidn't%20test%20right%20now%2C%20what%20could%20work%3C%2FP%3E%3CP%3E-%20open%20old%20and%20new%20workbooks%3C%2FP%3E%3CP%3E-%20select%20ALL%20worksheets%20in%20first%20workbook%20(click%20on%20first%20sheet%20tab%2C%20press%20Shift%2C%20click%20on%20last%20tab)%3C%2FP%3E%3CP%3E-%20from%20right%20click%20Move%20and%20Copy%2C%20select%20copy%2C%20select%20second%20workbook%20from%20drop%20down%20menu%20and%20copy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I work with workbooks having multiple tabs and about 200,000 cells, 90% of which have formulas.   I use the Solver addin for these workbooks

 

I've run into two bugs in Excel 365, 64 bit which is preventing me from using Solver, and preventing me from saving a worksheet which took hundreds of hours to create and refine.

 

If I have a small table containing the list of variables for the entire worksheet, on worksheet "A" and cut/paste that table to an empty space in worksheet "B", open solver, hit the "reset all" button and then enter new data, constraints etc., Solver will rename some of the constraints to some kind of code, rather than the expected formulation (e.g. "B5>=1").   Solver will instead save that as something like solver_lhs5 >=1

 

See this screenshot, and notice last two constraints.

https://www.dropbox.com/s/ns4bd6w2n6nmy7n/Screenshot%202017-08-09%2014.01.16.png?dl=0

 

If I try the "reset all" button again, the problem is replicated.

 

Worse, if I hit "reset all" button and try to save the workbook I get this message:  "Errors were detected while saving ___ . Microsoft Excel may be able to save the file by removing or repairing some features. To make the repairs in a new file, click Continue..."

 

So I click "Continue" and get another error message saying "document not saved."

 

If this were a simple worksheet I'd just copy it over to a new one.  But this is a complex workbook, and in the past when I've tried to copy/paste pages from it, the new workbook will contain references in its formulas back to the orignial workbook.    That means I have to discover, find/replace those references.  A tedious task! 

 

PS, when I first contacted Microsoft about this I was first connecteed to a tech who could not solve the problem.   As a last resort she suggested doing an "online repair" which I did.   After the online repair, the suite notifed me that my software wasn't activated.   But when trying to activate it, I was presented with a window saying that the page could not be found.   I contacted Microsoft again.  A new tech suggested remote access.  He managed to screw up the VPS on which I was running office, leaving me unable to connect to it.   I got that resolved on my own.    In any case, this issue is not confined to this one computer or workbook.  It occurs on another computer with another version of my workbook.

 

It might be more efficient to resolve this issue if someone could tell me how to copy/paste a complex workbook to a new one, in a way which does not refer formulas back to the original one.  Then in the future, I just won't try to move my "solver" inputs to another worksheet!

 

Thanks.

 

6 Replies

Hi Walter,

 

Didn't test right now, what could work

- open old and new workbooks

- select ALL worksheets in first workbook (click on first sheet tab, press Shift, click on last tab)

- from right click Move and Copy, select copy, select second workbook from drop down menu and copy

 

 

Thanks, but this led to another problem.

 

I first tried your suggestion, but the Solver issue reappeared in the new workbook. 

 

So I decided to "reset all" in Solver before copying the sheet to a fresh book.   When I tried that, I was presented with a new error message "Cannot do this command in Group Edit Mode."

 

I googled a bit trying to find a simple fix but the articles I pulled up had more to to with setting up group mode than removing it.

 

 so... what next?  Thanks

 

Walter, sorry, but i never worked with Solver, can't help with fixing of it.

 

Just in case, some ago i had an issue with "file can't be saved", fixed that by switching off Autosave option. That's not your case, but who knows. 

Thanks.  This is my first time in this foum. Will my post remain "alive" until someone else sees it?

Walter,

 

Yes, it always will be here. Another story if nobody answers in few days there are low chances it'll be answered at all.

 

Group Edit mode - that means few or all of your sheets are selected. I guess Solver shall not to work in such mode by design. By the way, i forgot there is Select All / Ungroup in right click menu for sheets tabs. Ungroup them before doing something with Solver.

 

And one point more - did you try to disable Solver Add-in in Excel Options?

Yes, thanks, I did try removing solver and rebooting but that didn't work. 

 

I'm posting in other forums for help and working on other ideas...