After listening to the Hanselminutes episode on Microsoft Solver Foundation (MSF) I decided it’s time to give it a shot today. Solver Foundation seems to be a solution to a set of constrained problems I sometimes face:
- Sharing costs and calculating minimal money transfers after trips with my friends, where each friend spent some money.
- Giving out questions to attendees of our User Group “Boot Camps”: Speakers prepare ~20 questions, ranging from easy to moderate levels. We assign each attendee an easy question and one to chew a bit upon. Further, every question should be given out to two attendees, so in case someone doesn’t make it to the meeting we’re still able to cover the question.
Something I don’t remember Scott Hanselman and his guest talking about is that Solver Foundation comes with an Excel Add-In that is supposed to make creating models
easy easier, no code needed. Along with the “Solver Foundation for Excel Primer” document that is installed along with the binaries I figured Excel would be a good way to start looking into Solver Foundation.
After the MSI ran, I started Excel but didn’t find the Solver Foundation tab that’s advertised in the primer. The COM Add-Ins dialog said something about that the Add-In could not be loaded. Nice! Luckily the Event Viewer was more helpful in terms of error messages where I found this beauty of an exception:
Customization could not be loaded because the application domain could not be created.
---> System.IO.FileLoadException: Could not load file or assembly 'MicrosoftSolverFoundationForExcel, Version=220.127.116.1190, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies.
The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
My first guess was that an old MSF assembly was referenced and I decided to go with an assembly binding redirect for excel.exe. Didn’t help. The next step was to get into the innards of VSTO deployment. What I found in the MicrosoftSolverFoundationForExcel.vsto and MicrosoftSolverFoundationForExcel.dll.manifest files wasn’t surpising: Several references to old versions of MSF. None of which were deployed by the MSI installer, so Excel trying to load such dependencies failed.
<assemblyIdentity name="MicrosoftSolverFoundationForExcel" version="18.104.22.16848"…
<assemblyIdentity name="MicrosoftSolverFoundationForExcel" version="22.214.171.12490"…
I updated both references to the match the installed version 126.96.36.19932, just to find myself faced with another error saying that the manifest’s digital signature is broken.
Now was time to contact my friend Lars Keller who is an expert in VSTO development. Lars told me that I would have to re-sign the .vsto and .manifest files to make the signature reflect my changes. The Office Development with Visual Studio blog has the full details.
- I had to create a certificate that can be used for code signing:
makecert -r -pe -n "CN=Your Name" -b 01/01/2010 -e 01/01/2099 -eku 188.8.131.52.184.108.40.206.3 -ss My
- Export the certificate as a PFX file using certmgr.msc
- Create a backup copy of the MSF Excel Add-In .manifest and .vsto files
- Open a Visual Studio Command prompt and navigate to the manifest's location
- Make edits to the manifest file correcting the assembly versions of MicrosoftSolverFoundationForExcel to 220.127.116.1132
- Update the digital signatures for both the manifest and the VSTO file:
mage.exe -update MicrosoftSolverFoundationForExcel.dll.manifest -CertFile <your-cert.pfx> -Password <cert-export-password>
mage.exe -update MicrosoftSolverFoundationForExcel.vsto -appmanifest MicrosoftSolverFoundationForExcel.dll.manifest -CertFile <your-cert.pfx> -Password <cert-export-password>
- Restart Excel, the Solver Foundation tab should be on the ribbon
(Tested with Office 2010 beta.)