Contact

admin

About Me · Send mail to the author(s) E-mail · Twitter

At GROSSWEBER we practice what we preach. We offer trainings for modern software technologies like Behavior Driven Development, Clean Code and Git. Our staff is fluent in a variety of languages, including English.

Feed Icon

Tags

Open Source Projects

Archives

Blogs of friends

Now playing [?]

  1. The Magician feat. Newtimers – When The Night Is Over (Claptone Remix)
  2. Nolan feat. Amber Jolene – Everyday & Everynight (KANT Remix)
  3. Kyodai Feat. Stee Downes – Music Rises Up (Claptone Remix)
  4. Boy Tedson – Dance On Sand
  5. Sans Souci – Bimmelbommelei
Audioscrobbler/Last.fm

ClustrMap

Using the Microsoft Solver Foundation Add-In for Excel

Posted in .NET | Debugging | Office at Sunday, January 17, 2010 3:52 PM W. Europe Standard Time

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:

Microsoft.VisualStudio.Tools.Applications.Runtime.CannotCreateCustomizationDomainException:
Customization could not be loaded because the application domain could not be created.
---> System.IO.FileLoadException: Could not load file or assembly 'MicrosoftSolverFoundationForExcel, Version=1.0.6.4890, 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="1.0.8.6048"…
<assemblyIdentity name="MicrosoftSolverFoundationForExcel" version="1.0.6.4890"…

I updated both references to the match the installed version 2.0.2.8632, 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.

  1. 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 1.3.6.1.5.5.7.3.3 -ss My
  2. Export the certificate as a PFX file using certmgr.msc
  3. Create a backup copy of the MSF Excel Add-In .manifest and .vsto files
  4. Open a Visual Studio Command prompt and navigate to the manifest's location
  5. Make edits to the manifest file correcting the assembly versions of MicrosoftSolverFoundationForExcel to 2.0.2.8632
  6. 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>
  7. Restart Excel, the Solver Foundation tab should be on the ribbon

(Tested with Office 2010 beta.)

Monday, January 18, 2010 12:00:34 AM (W. Europe Standard Time, UTC+01:00)
Yes, the Excel add-in that ships with Solver Foundation 2.0 doesn't work with Office 2010 beta out of the box. Sorry you had to go through all that trouble to get it working. We're going to be updating our bits soon so that everything works flawlessly with Office 2010 (the same way it does with Office 2007).

Nathan
Monday, January 18, 2010 4:42:58 AM (W. Europe Standard Time, UTC+01:00)
Thanks Alexander, that did the trick.
Sunday, March 21, 2010 12:25:47 PM (W. Europe Standard Time, UTC+01:00)
Love your work! I'm so glad you wrote this post.

This solved my frustrations after my Office 2007 decided not to load MSF anymore... I suspect it may have something to do with installing vs2010 RC.

Cheers!
All comments require the approval of the site owner before being displayed.
(will show your gravatar icon)
 
[Captcha]Enter the code shown (prevents robots):

Live Comment Preview