A few months ago, Andrew Whitechapel wrote an article and sample application that demonstrates combining the native capabilities of an Office client application with the UI capabilities of WPF. Behind the UI, the application is connected to remote data and services via WCF and uses the RAD features of LINQ to manipulate that data.
Some related questions have come up in the forum recently, so McLean Schofield took the sample application and made a short video overview of the WPF control part. You can find the full explanation and get the code from these articles:
Build Office-Based Solutions Using WPF, WCF, And LINQ - MSDN Magazine article by Andrew Whitechapel
http://msdn.microsoft.com/en-us/magazine/cc163292.aspx
Andrew Whitechapel's blog post, which has a link to updated code
http://blogs.msdn.com/andreww/archive/2007/11/26/vsto-wpf-wcf-linq-msdn-article.aspx
Video duration: 4 minutes, 23 seconds
In this episode: a tip about setting the background color of a custom task pane to match the Office theme, and about calling functions in add-ins from other add-ins. Plus, Mary creates an Outlook add-in that helps her remember attachments.
Duration: 5 minutes, 4 seconds
Related resources for more information:
Forum post: Custom task pane back color
Forum post: Use code of one add-in from another add-in
Article: Calling Code in Application-Level Add-ins from Other Office Solutions
Outlook Application.ItemSend Event
Outlook MailItem.Attachments Property
Outlook MailItem.Body Property
Many customers have requested how to specify options like a Product or Publisher Name for Office 2007 VSTO solutions deployed from Visual Studio 2008. However, you cannot specify these properties in the Visual Studio IDE. In this post, I will describe the various other options you have to specify these properties.
Specifically we will be looking at the following properties:
Product Name – This is what the VSTO solution will be called in the Programs and Features (Add Remove Programs Entry)
Publisher Name – The name of the Publisher as displayed in Programs and Features
Figure 1 : Programs and Features Dialog in Vista showing Product Name and Publisher Name
Friendly Name – This is the name of the VSTO solution as it is displayed in the Office Add-ins dialog, the same value is also used in the VSTO trust prompt. Usually this value should be the same as the Product Name.
Office Application Description – The description of the Office Add-in.
Figure 2 : Excel Add-ins Dialog showing Friendly Name and Office Application Description
SupportURL - End Users can click on the product name on the Trust Prompt dialog and visit a custom website related to the Solution.
Figure 3 : VSTO Trust Prompt with Friendly Name and link to SupporURL
The easiest way to specify these properties is to modify the Deployment and Application manifests for the VSTO solution (.vsto and .dll.manifest files).
To specify the Product, Publisher and SupportURL properties
The product name, publisher name and support url properties can all be specified in the deployment manifest, the .vsto file that is generated after publishing the solution. You can open the deployment manifest in a text editor and change the <description> tag to include the properties you want to specify.
<description asmv2:publisher="Visual Studio (BizApps)" asmv2:product="My VSTO Solution" asmv2:supportUrl="http://www.microsoft.com/" xmlns="urn:schemas-microsoft-com:asm.v1" />
Once you have modified the deployment manifest you need to resign it. This can be done using the Manifest Generation and Editing Tool (mage.exe) in the .NET Framework SDK, Windows SDK, or Visual Studio 2008.
Start the Visual Studio command prompt and use mage.exe to resign the deployment manifest:
mage –sign deploymentmanifest.vsto –Certfile Certificate.pfx
Example:
mage –sign MyExcelAddin.vsto –CertFile MyCert.pfx
To specify Friendly Name and Office Application Description:
The Friendly Name and Office Application Description properties can be found in the application manifest under the VSTOV3 namespace.
The application manifest (the MySolution.dll.manifest file) will typically be found under the Application Files\MySolution_X_X_X_X\ folder where the X’s describe the latest published version of the solution.
You can open the application manifest and change these properties:
<vstov3:customization xmlns:vstov3="urn:schemas-microsoft-com:vsto.v3"> <vstov3:appAddIn application="Excel" loadBehavior="3" keyName="ExcelAddIn9"> <vstov3:friendlyName>My VSTO Solution</vstov3:friendlyName> <vstov3:description>A Sample Add-in created to show the add-in properties work</vstov3:description> </vstov3:appAddIn> </vstov3:customization>
After you have changed the application manifest, you need to re-sign the application manifest itself. In addition you will also need to refresh the deployment manifest so that it has the new hash for the application manifest. Lastly you need to re-sign the deployment manifest.
The three steps after editing the application manifest can be performed using mage:
Resign application manifest:
Mage –sign applicationmanifest.dll.manifest –Certfile certfile.pfx Example: Mage –sign “Application Files\MyVsto_1_0_0_0\myvsto.dll.manifest” –Certfile ..\myvsto.pfx
Update deployment manifest:
Mage –update deploymentmanifest.vsto -AppManifest applicationmanifest.manifest Example: Mage –update myvsto.vsto -AppManifest “Application Files\MyVsto_1_0_0_0\myvsto.dll.manifest”
Resign deployment manifest:
Mage –sign deploymentmanifest.vsto –Certfile Certificate.pfx Example: Mage –sign MyExcelAddin.vsto –CertFile MyCert.pfx
Your VSTO solution will now have these properties. The downside of this approach is that you will have to perform these manual steps after every publish. If you want to avoid these manual steps, you can make some changes to the VSTO build targets such that these properties are picked up by Visual Studio during publish. The general idea is that you can specify these properties in your Visual Studio project file (the .vbproj or .csproj) which will in turn be picked up by the build tasks so that they are automatically inserted into the manifest for every publish.
Changing your targets file is a risky scenario; if you change something incorrectly, none of your projects will build. So to be safe, the first thing you should do is make backups of the files that you are going to change.
Also to be safe, we will not be editing the main target file but a copy of it. Any VSTO project that you want to specify the publish properties for will use this new modified targets file.
One more thing to consider before modifying the targets file is that your project may not build correctly if you use the modified targets file and later update the Visual Studio version. If you update Visual Studio, you will have to make the corresponding changes in the new Visual Studio targets file.
Creating a backup and modifying the targets file
The file we will be modifying is: Microsoft.VisualStudio.Tools.Office.Office2007.targets,which can be found in the following directory: %ProgramFiles%\MSBuild\Microsoft\VisualStudio\v9.0\OfficeTools
To open the targets file
3. Open the Microsoft.VisualStudio.Tools.Office.Office2007_Properties.targets file in your favorite text editor.
To change the targets file to support Product Name
In this example, we treat Friendly Name to be the same as Product Name.
To change the targets file to support Publisher Name
To changing the targets file to support SupportURL
To change the targets file to support Office application description
You are done modifying the targets file. The next step is to modify your project file and specify values for these properties. You will have to modify any project that you want to specify these properties for. The Visual Studio projects file is the .vbproj or .csproj file associated with your project. You can open up this file in a text editor.
You will have to modify the project so that it starts using the new targets file which you have created:
Modify project to use new target file
Specify Properties in project file
Inside the first <PropertyGroup> tag you can add the following properties:
<ProductName>My VSTO Solution</ProductName>
<PublisherName>Visual Studio (BizApps)</PublisherName>
<OfficeApplicationDescription>A Sample Add-in created to show the add-in properties work</OfficeApplicationDescription>
<SupportUrl>http://www.microsoft.com</SupportUrl>
Once you have added these properties to the project file you can save the project and open it in Visual Studio. When you open this project you will get a warning stating that the project has been modified to use custom build steps. Choose the option to Load the project normally and proceed. From now onwards, these properties will be automatically be inserted into the manifest during publish. Any updates you make to these properties will also automatically be picked up by the publish system.
Modifying the targets file is a rather long and elaborate procedure, but once you complete it successfully you have the benefit of being able to specify these properties easily for any project.
Hope you find this post useful. Please let us know your comments.
Saurabh Bhatia, Program Manager
In this episode: a tip about saving and restoring the position of custom toolbars in Outlook 2007, and how to copy values from one worksheet and paste them in another. Plus, Mary finishes her Excel application and sends it to her boss in e-mail.
Duration: 4 minutes, 33 seconds
Forum post: Saving toolbar positions in Outlook 2007
How to: Maintain Position Information for Custom Toolbars between Outlook Sessions
Forum post: Excel sheet CopySpecial having problem in xlPaste type
Range.PasteSpecial Method
So you just want to write some code to format a cell in a worksheet. However, there isn't a help topic in sight that shows you how to do it. There may be a topic out there, but you don't have time to sift through the web searching for it. You're not alone. The forums are filled with questions about how to accomplish what might seem like a simple task - like formatting a cell. But the search for the right class, method or property to do it is not so simple.
A great way to get unstuck on some of these small but distracting issues is to use the Excel macro recorder. Start the recorder, pick a cell, format the cell, stop the recorder and then look at the code that Excel generates. The trickiest part is translating the VBA code to C# or VB so that you can use the code in your VSTO project. However, doing this can be a lot easier than searching the web and MSDN for a topic that shows you which specific class or property in the massive Excel object model you need to perform your task.
Let's take a look at an example scenario. All I want to do is format a cell as a percentage with 2 decimal places on the right side. The Macro recorder will show me which method or property I need to use.
To use the Macro recorder in Excel 2007, you have to enable the developer tab in the Ribbon. Click the round Office button and then click "Excel Options". Then select the "Show Developer tab in the Ribbon" as follows.
Start the macro recorder as follows:
Select a cell and then set your formatting options as follows:
Stop the macro recorder. Open the macro in the VBA editor as follows:
Grab the generated VBA. For example the VBA generated by the macro that I recorded looks like this:
Range("B2").Select Selection.NumberFormat = "0.00%"
Ok this is good. Basically the code is selecting cell B2 and then setting the "NumberFormat" property of the selected range to a percentage with 2 right-side decimal positions. I just need to open my VSTO project and add code that gets the range for cell B2, and then sets NumberFormat property of that range to "0.00%" as follows:
[C#]
Excel.Range range1 = this.Range["B2", missing]; range1.NumberFormat = "0.00%";
[VB]
Dim range1 As Excel.Range = Me.Range("B2") range1.NumberFormat = "0.00%"
This assumes that you add this code to a sheet class in a document-level customization project. If you are using an application-level add-in project, you might use something like the following which formats the cell "B2" in the default sheet of the workbook.
Excel.Range range1 = this.Application.get_Range("B2", missing); range1.NumberFormat = "0.00%";
Dim range1 As Excel.Range = Application.Range("B2") range1.NumberFormat = "0.00%"
I might have found a topic that shows me how to format a cell as a percentage, but sometimes using the macro recorder can be a lot faster. Especially if the task you want to perform is something that can be done by using common Excel features.
You can still search for answers in the docs. The topic Excel Object Model Overview is a great place to start. There is a section in that topic named "Using the Excel Object Model Documentation" that lists several resources.
Norm E.
In this episode: a tip about serializing data in a Word document so users can't see it, and why content controls disappear from documents if you add them at run time. Plus, Mary customizes the Ribbon in Excel so her boss Kemp doesn't need to use button controls on the worksheet itself.
Duration: 5 minutes, 29 seconds
Serializing data and recreating content controls
Document variables property and code example
Caching data objects
How to cache data and code example
How to add content controls to documents and code example
AddPlainTextContentControl method and code example