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. Joris Voorn – Ringo
  2. Benn Finn – Beautiful Instant (Beatamines Remix)
  3. The Magician feat. Newtimers – When The Night Is Over (Claptone Remix)
  4. KeeMo & Schild feat. Justin Hopkins – Revolution (Schild Remix)
  5. Nolan feat. Amber Jolene – Everyday & Everynight (KANT Remix)
Audioscrobbler/Last.fm

ClustrMap

Page 1 of 2 in the Office category Next Page

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.)

Outlook Macro: Mark All Items Read In Subfolders

Posted in Office at Thursday, September 18, 2008 5:29 PM W. Europe Daylight Time

Mailing lists Recently my mailing lists subscriptions grew to an extend not seen before, largely due to my grown interest in ALT.NET and open source projects. Having an eye on the dev or user mailing lists is often very valuable to stay on top of recent developments, identify best practices and to post or answer the occasional question.

However, that doesn't come without drawbacks. One increasing hassle is to keep up with the constant flow of information such that I see myself forced to reset once in while. After getting back from a 3-day vacation this summer I had 200+ unread feed items and uncounted unread mailing lists messages. I do not claim to read everything that lands in my inbox(es) so a quick "mark all as read" is a very relieving act. FeedDemon, my feed reader, even has a panic button to support this discomforting situation.

Unfortunately Outlook does not support marking all items read in a certain folder and all of its subfolders. On the other hand, there is this ancient VBA language that you can use to program Outlook…

Sub MarkAllRead()
    Dim resultFolder As folder
    Dim folder As folder
    Dim item As MailItem
        
    Set resultFolder = GetFolder("path\to\your\mailing\lists\root\folder")
    
    For Each folder In resultFolder.folders
        For Each item In folder.Items.Restrict("[unread] = true")
            item.UnRead = False
        Next
    Next
             
    Set resultFolder = Nothing
    Set folder = Nothing
    Set item = Nothing
End Sub

Function GetFolder(strFolderPath As String) As MAPIFolder
    ' strFolderPath needs to be something like
    '   "Public Folders\All Public Folders\Company\Sales" or
    '   "Personal Folders\Inbox\My Folder"
    
    Dim colFolders As Outlook.folders
    Dim objFolder As Outlook.MAPIFolder
    Dim arrFolders() As String
    Dim i As Long
    On Error Resume Next
    
    strFolderPath = Replace(strFolderPath, "/", "\")
    arrFolders() = Split(strFolderPath, "\")
    
    Set objFolder = Application.GetNamespace("MAPI").folders.item(arrFolders(0))
    If Not objFolder Is Nothing Then
        For i = 1 To UBound(arrFolders)
            Set colFolders = objFolder.folders
            Set objFolder = Nothing
            Set objFolder = colFolders.item(arrFolders(i))
            If objFolder Is Nothing Then
                Exit For
            End If
        Next
    End If
    
    Set GetFolder = objFolder
    Set colFolders = Nothing
End Function

Thanks to Lars Keller for the hint to filter unread items with Restrict()!

Office 2007 File Icons for Windows SharePoint Services 2.0 and SharePoint Portal Server 2003

Posted in IIS | Office | SharePoint at Saturday, December 16, 2006 8:30 PM W. Europe Standard Time

As you probably know some applications of the 2007 Office System introduce new file extensions. Word 2007, Excel 2007 and PowerPoint 2007 save their data in a new XML-based format by default. To distinguish between the old (binary) and new (XML) file formats Microsoft has created some four-letter file extensions like docx for Word documents. The XML files are stored in a zip container, if you rename such files to have a zip extension, you can peek inside them with WinZip and other zip-aware tools.

If you upload documents created with the 2007 Office System to your WSS 2.0/SharePoint 2003 server you will see that the file icon in front of the file name is missing. This is because those versions of SharePoint essentially are not aware of the new file types. You'll have to register the new file types with SharePoint and provide icons for them.

  1. Download my set of Office 2007 icons. DOCM DOCX DOTM DOTX POTM POTX PPAM PPSM PPSX PPTM PPTX XLAM XLSB XLSM XLSX XLTM XLTX
    The archive contains 16x16 icons in 32-bits color-depth for all new file types. You'll also find five subfolders with the icons saved in the GIF, JPEG and PNG formats (GIF and JPEG with light and dark backgrounds for transparency). The Photoshop Automation.jsx script can be used to create all five images on the fly using Photoshop's scripting functionality - be sure to install the Photoshop ICO plugin first.
    If you want to extract other icons, these are embedded resources of the executables inside the C:\WINDOWS\Installer\{90120000-0030-0000-0000-0000000FF1CE}\ folder.
  2. Choose an image format appropriate to your SharePoint style – I went with the images from the gif-light folder as these suit the default SharePoint theme.
  3. Copy the images to the C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\IMAGES\ folder on your SharePoint server.
  4. Open the C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\XML\docicon.xml file with an editor. Add the following lines under the ByExtension element. Be sure to change the file extension (Value attribute) based on the image format you chose.
    <Mapping Key="docx" Value="docx.gif" EditText="Microsoft Office Word" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="docm" Value="docm.gif" EditText="Microsoft Office Word" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="dotx" Value="dotx.gif" EditText="Microsoft Office Word" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="dotm" Value="dotm.gif" EditText="Microsoft Office Word" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="xlsx" Value="xlsx.gif" EditText="Microsoft Office Excel" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="xlsm" Value="xlsm.gif" EditText="Microsoft Office Excel" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="xltx" Value="xltx.gif" EditText="Microsoft Office Excel" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="xltm" Value="xltm.gif" EditText="Microsoft Office Excel" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="xlsb" Value="xlsb.gif" EditText="Microsoft Office Excel" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="xlam" Value="xlam.gif" EditText="Microsoft Office Excel" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="pptx" Value="pptx.gif" EditText="Microsoft Office PowerPoint" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="pptm" Value="pptm.gif" EditText="Microsoft Office PowerPoint" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="potx" Value="potx.gif" EditText="Microsoft Office PowerPoint" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="potm" Value="potm.gif" EditText="Microsoft Office PowerPoint" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="ppam" Value="ppam.gif" EditText="Microsoft Office PowerPoint" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="ppsx" Value="ppsx.gif" EditText="Microsoft Office PowerPoint" OpenControl="SharePoint.OpenDocuments"/>
    <Mapping Key="ppsm" Value="ppsm.gif" EditText="Microsoft Office PowerPoint" OpenControl="SharePoint.OpenDocuments"/>
    
  5. Reset IIS by running iisreset.exe.
  6. You can test if the new icons work if you download this zip with sample documents. Upload the files to a document library. If everything worked, you should see something like this:
    Office 2007 sample documents with icons in SharePoint

The next step would be to let SharePoint index the new file types making them searchable. Unfortunately there are publicly available IFilters available yet. Even the new Windows Desktop Search 3.0 doesn't crawl docx and the like. Update: It appears there's a configuration issue with Windows Desktop Search on my machine as WDS just does not crawl my documents folder.

Another Update: A reader asked why documents of the Office 2007 flavor are handled differently by SharePoint. Instead of opening a docx in Word when you left-click the document's name, the browser shows a download dialog. Old-style doc files will be opened instantly by Word (assuming you're using Internet Explorer and have the SharePoint collaboration components installed).

Currently I have no idea how to make the new file types handled the same as the old files. After all, I'm no SharePoint expert. A workaround for this issue is to click the triangle on the right to the document name and choose "Edit in Microsoft Word" from the drop down list of actions.

Another thing that may help is adding the MIME types for Office 2007 documents in IIS. Without them, SharePoint serves 2007-style file types with the "application/octet-stream" generic content-type. The easiest way to do this is stopping IIS and editing the metabase XML file (C:\WINDOWS\system32\inetsrv\MetaBase.xml) using a text editor. Search for the <IIsMimeMap Location="/LM/MimeMap" …> element and append these lines to the MimeMap attribute:

.docm,application/vnd.ms-word.document.macroEnabled.12
.docx,application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotm,application/vnd.ms-word.template.macroEnabled.12
.dotx,application/vnd.openxmlformats-officedocument.wordprocessingml.template
.potm,application/vnd.ms-powerpoint.template.macroEnabled.12
.potx,application/vnd.openxmlformats-officedocument.presentationml.template
.ppam,application/vnd.ms-powerpoint.addin.macroEnabled.12
.ppsm,application/vnd.ms-powerpoint.slideshow.macroEnabled.12
.ppsx,application/vnd.openxmlformats-officedocument.presentationml.slideshow
.pptm,application/vnd.ms-powerpoint.presentation.macroEnabled.12
.pptx,application/vnd.openxmlformats-officedocument.presentationml.presentation
.xlam,application/vnd.ms-excel.addin.macroEnabled.12
.xlsb,application/vnd.ms-excel.sheet.binary.macroEnabled.12
.xlsm,application/vnd.ms-excel.sheet.macroEnabled.12
.xlsx,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltm,application/vnd.ms-excel.template.macroEnabled.12
.xltx,application/vnd.openxmlformats-officedocument.spreadsheetml.template

Be sure to add one MIME type per line. This will make the MIME types globally available on the server. After saving the metabase file, restart IIS.

Now Playing [?]: Zoot Woman – It's automatic

View Source in Outlook

Posted in Office | Tools and Software at Wednesday, May 17, 2006 8:33 PM W. Europe Daylight Time

Thomas Wölfer describes how to view the entire source of e-mail messages in Outlook (in German). This is something I've craving for since I started using Outlook in 2003. Outlook Express provides this functionality for years. I've been suprised that the more complex PIM needs a registry hack to enable it. This may be due to space considerations since the registry key (see below) indicates that Outlook saves things twice.

A quick recap of Thomas' how-to:

  1. Open regedit.exe
  2. Browse to the following key:
    HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Options\Mail
    If you have an older version of Outlook, replace 11.0 with the appropriate version number. I'm not sure if this works on other versions than 2003, but you may give it a try.
  3. Create a new DWORD value named SaveAllMIMENotJustHeaders and give it a value of 1
  4. Close the Registry Editor
  5. Restart Outlook

You'll be able to view the complete source of all e-mail messages received after the registry key has been set. Right-click a mail item and choose "Options..." from the context menu. The source code is displayed in the lower part of the window.

Outlook View Source

Now playing: Madonna - Confessions on a dance floor - I love New York

SharePoint Does Not Index PDFs by Default

Posted in SharePoint | Office at Tuesday, March 07, 2006 10:43 PM W. Europe Standard Time

PDF IconRather by accident I found out that SharePoint Portal Server 2003 does not index PDF documents by default. This may be because PDF is a non-Microsoft format. But PDFs are commonly used when exchanging documents in cooperative work environments like SharePoint  Team Sites.

The steps to take to enable search for PDF documents on a SharePoint Server are pretty straight forward and extensively described. If you are looking for the icon file that is neither included in the PDF IFilter setup nor in SharePoint itself, simply issue an image search.

Now playing: The Cinematic Orchestra - Everyday - All things to all men (feat. Roots Manuva)

Page 1 of 2 in the Office category Next Page