Conditional Formats

For some days ago I received an e-mail about creating conditional formatting through code which I sent a reply on. The next e-mail asked specifically about when to create these formats via code.

I can only answer for myself and the below list gives some indication when I use it, i e creating conditional formattings, via code:

• To highlight various groups of data in pivottables, lists and tables. Usually the raw data is retrieved from database(s).
• Creating standalone workbooks and/or templates through code where it’s necessary to highlight data in specific areas.

In general I try to avoid other situations then above as I find it complicated to work, through code, with conditional formatting under other circumstances.

With Excel 2007 we now have several new conditional formats to use.  However, the new conditional formats also bring ‘new rules’ for working with them in code. Therefore we need to be aware of it and, of course, know how to work with them.

The following code example shows how we work with one of the old conditional formats, adding a conditional formatting based on an expression, in VSTO:

The most interesting aspect in the above sample is the declaration of the variable p_cfOld as an Excel.FormatCondition’s object.

The following code example shows how we work with one of the new conditional formats, adding a conditional formatting based on the Icon Sets, for Excel 2007 in VSTO:

 

As the above sample shows, we now use the Icon Set’s object instead of the general FormatCondition’s object. If we should try to use the FormatCondition’s object it would end with an exception.

The new conditional format’s objects seem not to be part of the general FormatCondition’s object. Instead the new conditional formats have their own collections and therefore we need to explicit declare the format type(s) involved for specifik tasks. However, as the above samples show, they seem at least to be part of the range object’s conditional format collection.

Kind regards,
Dennis

Advertisement

Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part VII

To read previously blogposts on the subject please see:

• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part I
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part II
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part III
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part IV
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part V
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part VI 

Deployment
MSFT has recently updated their articles about deployment of VSTO solutions:

• Part I
• Part II

It’s good that the articles have been updated to review Office 2007 and VSTO 2005 SE. In my opinion it would have been even better if they had included the difficulties and shortcomings that actually do exist (see MSDN’s VSTO forum).

When it comes to deployment of managed VSTO created COM add-ins we have two alternatives to consider. The add-ins can be stored on the local hard disks or in network-accessible locations (although the later require some additional work).

If we develop general free and commercial add-ins the only option is to install them on the targeting computer’s disks. For custom specific solutions network-accessible locations in the intranet can be considered.

In the following we will create the deployment package for the VSTO project. The first decision we need to make is about the prerequisites and where these should be available. I prefer to make them available as:

• Part of the deployment package or
• Downloadable from a web location controlled by me.  

The above assures that the correct versions of the prerequisites are always available for my add-ins. In addition, the PIAs can’t be downloaded and installed from a web location. As for the VSTO project in the series the prerequisites would be part of the deployment package.

Next we need to work with the Setup project itself. This is easy as the Setup project was created and added to the VSTO project when we created it. What we need to do in order to create the release version of the add-in is the following:

1. Right click on Setup project and select the menu item ‘Properties’.
2. In the dialog form click on the button ‘Configuration Manager’.
3. In the field for ‘Active solution configuration’ select the ‘Release’ option.
4. Close the dialogform.
5. Right click on the Setup project and select the menu item ‘Build’.
6. Done!

After these steps we have a deployment package to distribute that includes the following:

  

As we can see from this screenshot we have both an exe file as well as a msi file. When executing the setup.exe file everything works as expected and if the prerequisites don’t exist on the targeting computers they will be installed first and then the add-in.

If we execute the msi file the solution will be installed but it will not check if the prerequisites exist or not on the targeting machines. That’s why the above mentioned MSFT’s articles discuss launch conditions when msi files are being executed. On the other hand, in a 100 % controlled environment msi files are preferred to use.

Q: Are there any other aspects to consider before we start to distribute the installation package?
A: Yes…

Windows Vista & Error 2869

Believe or not but it exist a major issue when it comes to Windows Vista and installing VSTO solutions – The Error 2869:

 

I have spent considerable time to figure out how to solve it as I initially thought it was related to my VSTO setups. It finally turned out to be related to VSTO and Windows Vista.

Instead of discuss it more in detail here I refer to the following sources:

• VSTO Add-ins and Vista
• Windows Installer fails on Vista with 2869 error code

Instead of downloading the whole Windows SDK kit the necessary file can be downloaded here.

If You experience this problem then see if it can be resolved by downloading and installing the Visual Studio 2005 SP-1 Update for Vista. In the next release of Visual Studio.NET this issue will be solved.

Finally
This series has been trying to give more information on using the latest technology – VSTO – to create and distribute managed COM add-ins.

Being an early adopter comes with a price in terms of:

• Heavy time consumption with no or little outcome 
• Lack of relevant and updated documentation
• Bugs
• Frustration

Q: Is VSTO created COM add-ins ready for production?
A: In a 100 % controlled environment where the add-ins leverages the core and workable part of VSTO then the answer is ‘yes’. For other scenarios the answer will still be ‘no’.

But it should not prevent us from explore and discuss VSTO!

Kind regards,
Dennis

Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part VI

To read previously blogposts on the subject please see:

• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part I
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part II
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part III
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part IV
• Creating and Deploying Managed COM Add-ins with VSTO 2005 SE – Part V

The hard part – Code Access Security Policy
Code Access Security (CAS) is the most difficult and the most challenge part of creating workable VSTO setups. It’s also the most confusing part of .NET, especially for those of us that come from a pure VBA environment. I’m the first to admit that writing this post is also difficult due to the subject.

In order to fully understand it we need to have deeper knowledge about:

1. .NET Framework,
2. .NET Framework’s security model,
3. VSTO’s security model,
4. How to work with different kind of manifests and
5. How to work with the available tools to create and control the security part of setups.

This blogpost only cover CAS as part of VSTO created managed COM Add-ins’s setups. For obvious reasons it’s not possible to cover it all and the blogpost should be considered as an introduction to VSTO’s security. Compared with workbook level solutions (workbooks and templates) the security aspect for managed COM Add-ins is, in my opinion, less complex.

For a general introduction about CAS please see the following online resources:

• Understanding .NET Code Access Security
• Code Access Security (CAS) and Design Pattern
• Managing .NET Code Access Security (CAS) Policy

As for the VSTO’s security model it exist one important aspect that we need to be aware of:

• Whenever a VSTO customization is loaded it must be granted ‘full trust’. 

When we create a new VSTO project on the developing machine the project gets the CAS status of ‘full trust’. It means that the project will run as expected on the developing machine without any restrictions which is fine.

In view of the above and the fact we use a lot of wizards on .NET platform I wonder why the creation of basic CAS policy setups has not yet been implemented as part of the ‘wizard’ when creating VSTO projects.

In my opinion that would be an excellent solution and it would also make it more possible to create VSTO’s setups that work.

For a general start and introduction about VSTO’s security please see Security in Office Solutions and the article Security Requirements to Run Office Solutions gives a good picture of the requirements.

As for the series VSTO Case project we can grant security trust to it in the following ways: 

• Using .NET Framework 2.0 Configuration tool with which we can configure a CAS policy. The CAS policy can either be installed as a standalone or part of a VSTO setup. This is typically used within corporates with a high control of the targeting machines, i e the configuration of the targeting computers is identical.
• Creating batch scripts where the caspol.exe is used to change the security policy. This is also a typical corporate approach.
• Create from scratch our own CAS policy project in VB.NET or C#.
• Add the SetSecurity Project from the online available VSTO2005 Windows Installer Sample to the project. 

The section “Walkthrough: Enhancing the Outlook Add-in Setup Project in the article Deploying Visual Studio 2005 Tools for Office Solutions Using Windows Installer: Walkthroughs (Part 2 of 2) gives the necessarily details to create a working CAS setup.

The following are the details for the VSTO Case:

A screenshot of the project in the Solution Explorer when the SetSecurity project has been added:

The following screenshot shows the Custom Actions Editor where we add the wanted Action Data:

The following string (added in one line) is added to the property CustomActionData of the Install method:

/assemblyName=”VSTOCase.dll” /targetDir=”[TARGETDIR]”
/solutionCodeGroupName=”MyCompanyName.VSTOCase”
/solutionCodeGroupDescription=”Code group for VSTOCase”
/assemblyCodeGroupName=”VSTOCase”
/assemblyCodeGroupDescription=”Code group for VSTOCase” /allUsers=[ALLUSERS]

The following string is added to the property CustomActionData of the Rollback method: 

/solutionCodeGroupName=”MyCompanyName.VSTOCase”

The following string is added to the property CustomActionData of the Uninstall method: 

/solutionCodeGroupName=”MyCompanyName.VSTOCase”

In the next blogpost in the series I will end the case study. Meanwhile I would like to see that MSFT update their referred articles here as soon as possible to also include Office 2007.

Finally, if You’re looking for an introduction for developing VSTO solutions then Creating Office Solutions in Visual Studio may be a good place to start with.

Kind regards,
Dennis