Deploy multiple Office Solutions in a single setup

How to deploy multiple Office Solutions in a single setup

After creation of all your MS-Office solutions, they can be configured to be deployed all at once. The steps below walk through how to configure one setup for all MS-Office solutions using a Click Once installer for the .NET Framework:

Step 1: Create a sample Word Add-In project using VS

(File->New Project->Office/SharePoint->Office Add-ins)

Step 2: Add Projects to the Solution

Add all the projects into this solution where the sample add-in is created.

Step 3: Publish project one at a time to the common folder

(For Ex. ‘C:/Publish/’). Publish the sample add-in project last, when you have finished publishing the other projects . (Note: Ensure that publish settings are set correctly. Add the necessary prerequisites to each project, select install prerequisites as the same location as my application. By selecting this one must ensure that all prerequisites are at this location ‘C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\Bootstrapper\Packages’) in the appropriate folder.

Screen-Shot: Publishing to common place folder:
Multiple Office AddIn1

Screen-Shot: Here we have taken ‘SetupForAddin’ folder (as the common location) to publish all add-in projects:
Multiple Office AddIn2

Step 4: Modifying the application manifest

  1. From the c:\publish\Application Files folder, move the contents of the {EXCEL_PROJECT}_1_0_0_0 and {WORD_PROJECT}_1_0_0_0 or {PPT_PROJECT}_1_0_0_0 directories to the {SAMPLE_PROJECT}_1_0_0_0 directory.
  2. Open the {SAMPLE_PROJECT}.dll.manifest, {WORD_PROJECT}.dll.manifest, and {EXCEL_PROJECT}.dll.manifest, {PPT_PROJECT}.dll.manifest files in an XML editor.
  3. From the {WORD_PROJECT}.dll.manifest file, copy all install and file dependencies including {WORD_PROJECT}.dll. That is, copy all dependencies that start with
  4. In the {SAMPLE_PROJECT}.dll.manifest file, paste the {WORD_PROJECT}.dll install dependency at the end of the dependency section.
  5. Repeat the previous #3 and #4 for {EXCEL_PROJECT} and {PPT_PROJECT}.

Screen-shot: move files from the add-in projects to our last published sampled project directory. (Files to move from each project are ‘.vsto’, ‘dll.deploy’,’dll.manifest’):

Multiple Office AddIn3

 

Screen-Shot: Copied all the element from all word, excel, PowerPoint add-in projects to sample projects {SAMPLE_PROJECT}.dll.manifest file:

Multiple Office AddIn4

Step 5: Adding the Entrypoints

  1. In the {SAMPLE_PROJECT}.dll.manifest file, remove the text between the and elements.
  2. From the {WORD_PROJECT}.dll.manifest file, copy the text between the and elements.
  3. In the {SAMPLE_PROJECT}.dll.manifest file, paste the code after the element.
  4. Add the id attribute to the element to differentiate this entrypoint from others.
  5. Repeat previous points #2, #3 and #4 for {EXCEL_PROJECT} and {PPT_PROJECT}.

Screen-Shot: Copied all the element from all word, excel, PowerPoint add-in projects to sample projects {SAMPLE_PROJECT}.dll.manifest file.

Multiple Office AddIn5

Step 6: Adding Assemblies

To add the assemblies to the vstov4 namespace in the application manifest

  1. In the {SAMPLE_PROJECT}.dll.manifest file, remove any text between the and elements.
  2. In the {WORD_PROJECT}.dll.manifest file, copy the text between the and elements.
  3. In the {SAMPLE_PROJECT}.dll.manifest file, paste the code after the element.
  4. Add the id attribute to the element to differentiate this customization from others. This id is the same id that was added to the element in the previous step 5.

Screen-Shot: Copied all the element from all word, excel, PowerPoint add-in projects to sample projects {SAMPLE_PROJECT}.dll.manifest file.

Multiple Office AddIn6

Step 7: Sign manifests

To re-sign the application and deployment manifests

  1. Copy the {SAMPLE_PROJECT}_TemporaryKey.pfx certificate file from the {SAMPLE_PROJECT} solution directory into the c:\Publish\Application Files\{SAMPLE_PROJECT}_1_0_0_0directory.
  2. Open the Visual Studio command prompt.
  3. Change to the c:\Publish\Application Files\{SAMPLE_PROJECT}_1_0_0_0 directory.
  4. Sign the modified application manifest with the following command:
    mage -sign ContosoInstaller.dll.manifest -certfile ContosoInstaller_TemporaryKey.pfx

The message “{SAMPLE_PROJECT}.dll.manifest successfully signed” appears.

  1. Change to the c:\Publish directory.
  2. Update and sign the deployment manifest with the following command:

mage -update ContosoInstaller.vsto -appmanifest “Application Files\ContosoInstaller_1_0_0_0\ContosoInstaller.dll.manifest” -certfile “Application Files\ContosoInstaller_1_0_0_0\ContosoInstaller_TemporaryKey.pfx”
The message “{SAMPLE_PROJECT}.vsto successfully signed” appears.

Copy the {SAMPLE_PROJECT}.vsto file to the c:\publish\Application Files\{SAMPLE_PROJECT}_1_0_0_0 directory.

Commands to execute: (Use ‘Developer Command Prompt for VS2013’ command prompt from Visual Studio Tools)

  1. mage -sign XYZOfficeAddIn.OfficeAddInSetup.dll.manifest -Publisher “XYZ Products” -Password xyz@123 -certfile XYZOfficeAddIn.OfficeAddInSetup_TemporaryKey.pfx
  2. mage -update XYZOfficeAddIn.OfficeAddInSetup.vsto -Publisher “XYZ Products” -Password xyz@123 -appmanifest “Application Files\ XYZOfficeAddIn.OfficeAddInSetup_1_0_0_3\ XYZOfficeAddIn.OfficeAddInSetup.dll.manifest” -certfile “Application Files\ XYZOfficeAddIn.OfficeAddInSetup_1_0_0_3\ XYZOfficeAddIn.OfficeAddInSetup_TemporaryKey.pfx”

Screen-Shot: Run the ‘Developer Command Prompt for VS2013’ command prompt from VS tools:

Multiple Office AddIn7

Screen-Shot: Re-sign the application and deployment manifests:

Multiple Office AddIn8

Now your final setup is ready. You can run to install. You should then see the Add-Ins configured; for Word, Excel and PowerPoint

creating an MS Office Add-In using Visual Studio IDE

Process for creating MS Office Add-In from Visual Studio IDE

Step 1: To start constructing an Office add-in, we are going to create a separate project for each Office application i.e. Word, Excel, PowerPoint etc. Let’s start with the MS-Word add-in. Open Visual studio with the IDE option FILE -> New -> Project:
OfficeAddin1

Step 2: Now select an appropriate template (i.e. Visual Basic, Visual C# etc) to develop an add-in project. We will select Visual Basic for this, Go to Templates -> Visual Basic -> Office/SharePoint.
OfficeAddIn2

Step 3: Now select the appropriate add-in project to develop as shown in the screenshot below. Let’s select the MS-Word add-in project, for example.

OfficeAddIn3

Step 4: Name the project and select the ‘Location’ folder to save the project work. Click ‘OK’ button to create the project.

OfficeAddIn4

Here’s what it looks like after the ‘Ok’ button is clicked:

OfficeAddIn5

Step 5: Now we have to add a ribbon to this newly created project which will then be customized further. To add a ribbon, right click on the project, Go to Add -> New Item… Then select ‘Ribbon (Visual Designer)’ component as shown below:

OfficeAddIn6

Select ‘Ribbon (Visual Designer)’ ->

OfficeAddIn7

Here’s how it looks after adding the ribbon into the project:

OfficeAddIn8

Step 6: Now that the ribbon add step is done, we will proceed with customizing the ribbon. To design/customize the ribbon, we use the ‘Toolbox’ controls as shown in below screen shot.

OfficeAddIn9

After customization of the ribbon, we have added one button with an image and label ‘Upload File’. We can then trigger the opening of a form on detecting the click of this button.

OfficeAddIn10

Step 7: Now we will add a form for processing our logic to upload the file from the user’s machine to the server. To add a windows form, right click on the project, Go to Add -> New Item… Then select ‘Windows Form’ component as shown below screen-shots:

OfficeAddIn11

Below is the Design form for the move file action using toolbox controls:

OfficeAddIn12

Step 8: Write logic to process/move the opened file to the server on the click of the ‘Move Document’ button. Process followed in current Office add-in is as below:

1. User will open his/her document in the MS-Office application
2. After the document is opened, if the user wants this to transfer to a website location, the User can go to the Add-In section and click the ‘Move Document’ button.
3. From step 2, a form will ask for the ‘site URL’. Based on this we can find the service location from the server which is located at ‘http://{siteurl}:{port}/Services/UploadService.svc’
4. After click on the ‘Move Document’ from form –
4.1. Copying current/active file opened in Word (or Office) application into the TEMP location of the user’s machine to read all file contents in bytes. This is because we can’t read in the stream of bytes directly if the file is currently in use.
4.2. Next we read the contents from the file newly located in the TEMP location. Pass this bytes[] to the service method UploadDocument(fileContent, newFileName) in fileContent parameter and the file name us generated to process in the UploadDocument method.
4.3. After bytes[] stream is passed to the service method, it will write to the file in the TEMP location on the server which will be used to process or attach that document to the record based on the selection.

To add the service, right click on the project, Go to Add -> Service Reference…

OfficeAddIn13

Configuring service reference in the add-in project:

OfficeAddIn14

The Code below is added on the click action of the ‘Move Document’ button from our windows form:

OfficeAddIn15

Office Add-In Installation

To install the MS Office add-in, Go to your setup location folder and click on setup.exe. This setup will install add-ins for Word, Excel, and PowerPoint applications:

OfficeAddIn16

Screen-Shot: After installation of add-in setup:

OfficeAddIn17

Screen-Shot: Form to move the document to the server:

OfficeAddIn18

Press the “Move” button and it will open the site associated with the entered URL in the browser. The other upload document process will handle the entered website.

For MS-Excel and MS-PowerPoint we can follow same steps.

Working around the SharePoint document parser for Office 2003 documents

Working around the SharePoint document parser

This blog entry is dedicated to a solution for Cristina, a long suffering user who had to re-enter document properties within Excel for many dozens of documents every month.

If you programmatically upload an old-style Office 2003 document into SharePoint 2003, you’ll find setting the document properties doesn’t avoid Office from prompting for the properties when re-opening the document.

Let’s define a HashTable for assigning the metadata tag(s) which streaming the document into SharePoint:

$HT = @{}
$HT["DocTypeAcc"] = $RuleDocType
$x = $web.Files.Add($NewFName,$FTPFiles[$fi].OpenRead(),$HT, $false)  #stream add the file, with hashtable properties
try {	$x.CheckIn($null); } catch{} #force the checkin

Note we avoid needing to assign properties and do a systemUpdate() which is nice and efficient.

The problem is if you examine the SPItem’s properties, you’ll see an interesting value:
vti_error0 Could not process the file library/file.xls as a Microsoft Office document.
this error summarized inside this property (vti is a holdover from the old Vermeer Technologies, purchased by Microsoft to acquire FrontPage, which grew to be SharePoint Designer, although the vti properties and functions remain. Of course this is a valid Office document, but the parser does not handle these documents.

Here’s how to get the properties:

$docurl = "ht tp://sharepointdev/site/library/folder/file.xls"
$site = New-Object Microsoft.SharePoint.SPSite($docurl) 
$web = $site.OpenWeb() 
$item = $web.GetListItem($docurl)
$item.properties

What occurs is the front page RPC call “put document” makes a direct call to the author.dll uploading the file binary and the corresponding properties you wish to populate. However, uploading Office 2003 files using this method causes an error, and the properties are not set.

How to solve? Actually the problem is quite simple. Just disable the parser! Here’s how:

$web.set_ParserEnabled($false)
$web.Update()

MS-Office Integration with SharePoint

I always assumed Microsoft’s strength was integration between its products, so I assumed that there would be good integration between SharePoint and Microsoft Outlook and Microsoft Office generally.  It was only after watching end-users struggle to navigate basic document operations in MS-Office and Outlook that I came to realize there’s a huge gap in that integration and the resulting user experience.  SharePoint at best appears as flat list of Recent Places shortcuts, leaving MS-Office and Outlook users flummoxed as they try to find or file a document within SharePoint.  Worse, metadata support is very limited; Office 2007 products don’t support new metadata fields such as Hierarchical Taxonomy (Managed Metadata Service) fields or External Content Types.  Even in Office 2010, the DIP (Document Information Panel) by default provides a random dump of the metadata fields and is far from user-friendly.  The DIP consumes valuable real-estate by default, with an unintuitive user interface for hiding/exposing it, and an unclear relationship between Document properties and SharePoint metadata fields.

As I explored add-on software to address these issues, I first came across and ultimately deployed a nifty product produced by Colligo.  It is highly focused on the Outlook end-user experience.  It provides drag-and-drop, full rich metadata support, and the ability to set up favorite SharePoint sites.  It works great at first, but there are some real issues to be aware of before committing to it:

  • Stability
    In my experience with this add-on, new releases often create problems as they fix other issues.  Before deploying, make sure the version intended for deployment is well-tested before deploying.  Once corrupted, we had to uninstall and then reinstall on the client desktop too often.
  • Caching
    Watch out for caching.  Changes to sites such as metadata don’t get reflected until a full synchronization.  When caching, a huge volume could get stored on the client desktop.  It blew up several of our thin clients, causing us to have to uninstall and reinstall the product, on more than one occasion, a user ran out of disk space and corrupted Colligo.
  • No site hierarchy

    Favorites need to be hand-entered in the 4.x version, and appear as a list of sites.  There’s no way to see the sites and libraries as a hierarchy, which is what users expect.

  • Colligo adds extra folders to the Mail Folders to represent the structure of Favorite sites. This can cause issues with the MAPI tree if the Favorite site has a lot of sub-sites and libraries.

Colligo has a 5.x version in the wings that offers two much-needed capabilities:

  • Central Administration of favorites
    You can now “push” favorites out to end users centrally.
  • Eliminating of the Site Caching
    Going to real-time is a smart move that goes to the heart of some of the problems in the prior version.

However, in testing, this version is not yet stable enough to recommend.  There are also real issues in the scrolling and display of metadata fields, preventing users from reaching some fields.

A far more comprehensive product line is offered by MacroView.  Like Colligo Contributor,  MacroView DMF (stands of Document Management Framework apparently) also provides drag-and-drop, full rich metadata support and the ability to set up favorite SharePoint sites.  In contrast to Colligo, MacroView shows the full site hierarchy in Outlook, and allows search views that can be customized to reflect the company metadata.  The MacroView menu and configuration options are very comprehensive. It already has the capability to push out Favorites and does not do any caching of file or metadata. MacroView offer an optional add-on that allows users to override and set security at the individual document level (which is another thing that is surprisingly difficult with the out-of-the-box SharePoint UI).  The SharePoint navigation and metadata experience with MacroView is consistent across Outlook, Word, Excel PowerPoint and the desktop client – even in Adobe Reader.  Lastly, the MacroView software is rock-solid reliable in tests and demos.

Customizing item-level security

Individual document security permission overrides carry its own challenges.  Each time permission is “broken” or customized in a document library, a new security scope is created.  Industry estimation is a performance hit of 20% is experienced at 1,000 security scopes in a library.  Microsoft defaults to a configurable maximum of 50,000 security scopes per library, with a recommendation to actually lower this to 5,000.

A product from a company called Titus offers a well thought out rules based security system that sets the security within the SharePoint model based on the metadata, using simple rules you defined.  While it has been the perfect product for some problems, we’ve had some reliability issues with it as well that we are working with Titus to resolve, where some document uploads fail the first time.