.NET4Office

Office Development While You Workout

Harry Miller has done a really cool series of podcasts that you can download and listen to while you workout, drive, etc.

See http://blogs.msdn.com/vsto/archive/2008/04/17/office-development-audio-series-1-is-now-available-harry-miller.aspx

Super cool.

Inter-AddIn Communications

This is an important article to read if you've every wondered about how to get add-ins you've written to talk to other add-ins loaded in the same Office process.

http://blogs.msdn.com/vsto/archive/2008/05/07/calling-into-a-vsto-add-in-from-a-com-smart-tag.aspx

Basically, it involves the callee Add-in overriding RequestComAddinAutomationService to provide an object that caller add-ins can talk to.  Then the caller add-in uses the ComAddins collection off of Office Application object to find the callee add-in it wants to talk to, calls .Object off of ComAddin, and the caller add-in then gets the object provided by the calee Add-in's implementation of RequestComAddinAutomationService.

Getting My Head Around Outlook 2003 to Outlook 2007 Object Model Changes

I'm trying to get my head around all the changes in the Outlook 2007 object model. Some good resources are found on MSDN:

Also, this may be helpful to someone out there.  I created two idl files, one with the Outlook 2003 object model and one with the Outlook 2007 object model.  I sorted the idl files so all the enums are listed first in alphabetical order, then all the interfaces in alphabetical order, then all the coclasses in alphabetical order--this gives you a nice view when you use windiff or some other differencing tool to compare the two object models.  Note that these idl files have some objects removed--specifically, I removed most of the objects having to do with built in outlook controls you can use in a form region because my focus is using managed controls in form regions.

Outlook 2003 sorted IDL file

Outlook 2007 sorted IDL file

VBA and VSTO Can Be Friends (Part III)

When we last left our heroes:

Let's now add some VSTO code to vstoandvba.docm and see how VSTO can call VBA code.

Launch Visual Studio 2008 and choose File, New, Project...  Then, go to the Visual Basic node, then Office, then 2007 and pick a "Word 2007 Document".  We'll call the project WordDocument1 out of utter lack of creativity.  Also important is the location of the project.  We set the location of the project to c:\vstotemp because we've already added that as a trusted location in Office.  The actual project will be created in a subdirectory called WordDocument1 since we've checked the "Create directory for solution" box.  Remember in Part 2 of this series we trusted c:\vstotemp and all subdirectories.  So the project and its outputs will be truested

When you click OK, a dialog will appear to select a document to be used for the project.  Click on the "Copy an existing document" radio button.  Then browse to the vstoandvba.docm file you created in Part 2 of this article series.  You will need to change the filter when browsing for the document so it shows docm files.  Once you've located the vstoandvba.docm file, the dialog will look like this:

Click the OK button and the project will be created for you.  Right click on ThisDocument.vb and choose View Code.  Then in the ThisDocument_Startup method, we'll add some code to call from VSTO the VBA macro we recorded earlier called "MyMacro". 

Private Sub ThisDocument_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    Globals.ThisDocument.Application.Run("MyMacro")
End Sub

The code we put here is a little more verbose than necessary (Run("MyMacro") would actually work in the ThisDocument project item), but it has the advantage that you can use this code from anywhere in the project--for example in an actions pane control you add to the project.

Now, if you F5 the project, the macro you recorded earlier "MyMacro" will run when the document opens and it is called from VSTO.

Next time:

- How to call a VBA macro from VSTO that is recorded in a template and how to disambiguate macro names with some gotchas.

- How to call a parameterized macro from VSTO

Deploying VSTO 2008 add-ins to all users (continued)

Misha blogs again, this time about how to install a VSTO 2008 add-in to all users on a machine.

Deploying your VSTO 2008 Add-In to All Users (Part III)

VBA and VSTO Can Be Friends (Part II)

VBA code can easily be called from VSTO.  Here are the basic steps.

1. Create a document with VBA code in it.
2. Declare a public function or subroutine in the VBA code behind the document.
3. Figure out a trusted location to create a VSTO project from
4. Create a new VSTO project using the document with the VBA code in it--be sure to create the project in a trusted location.
5. From VSTO code, write the code "Me.Application.Run("MethodName", param1, param2, ...)

So let's walk through this in more detail.

First, launch Microsoft Word 2007 and create a new blank document.  Type some random text into the document.  Then record a vba macro into the word document--something like selecting a word in the document and bolding it.  If you haven't recorded macros in Word 2007 yet, you may be wondering where you start the macro recorder from.  Go to the Office menu (the big round button in the top left corner of the Word window), drop down the menu and click Word Options.  Then from the Popular tab of the Words Options dialog, click the Show Developer tab in Ribbon button.  This shows the developer tab of the ribbon.  On the developer tab of the Ribbon is a Record Macro button.  Click the Record Macro button.  Name the macro "MyMacro" and make sure it records into the current document by dropping down the Store Macros In combo box and picking the active document rather than normal.dot.  Click OK to start recording the macro.

Now do something interesting in the document then click the Stop Recording button in the developer tab of a ribbon.

Now that you have a macro recorded called MyMacro in the current blank document, you need to save the document into a trusted location.  What are trusted locations you might ask?  These are paths that Office trusts and allows macros to run from.  You must save your document to a trusted location if you want to run the macro the next time the document runs.  You must also store a document that has VSTO code in it that talks to VBA in a trusted location or the VBA code won't be allowed to run.  (You don't need to do this in general for documents with VSTO code behind them, only documents that have both VSTO and VBA code in them as our example will have when we are done).

To figure out what the trusted locations are, bring up the Word Options dialog again from the Office menu.  Click on the Trust Center tab on the left then click on the Trust Center Settings button.  In the Trust Center click on Trusted Locations tab as shown below.

Let's add a trusted location where we will create our hybrid VSTO and VBA project.  Click the Add New Location button then create a new trusted location--we'll create a folder called
"c:\vstotemp" and make sure you check the checkbox so that subfolders will also be trusted.  Under this directory, we'll create our VSTO project.

Now that you have a trusted location (make sure that you created a folder called vstotemp or whatever you choose to call it) save your document with macros in it to the root of that directory.  Another new Office 2007 thing to remember is that you can't just save the file in the default "docx" format if it has macros in it.  You need to choose Save As from the Office menu and pick "Other file formats".  Then, in the Save as document, drop down the Save as type to pick Word Macro-Enabled Document as shown below.  We'll call the document vstoandvba.

Now that you've saved the document, close it, then to prove to yourself you did everything right, reopen the document, go to the developers tab and click the Macros button, then rerun MyMacro to make sure it still runs.

So where are we?

• We created a word document, turned on the developer tab, and recorded a macro into the word document called MyMacro
• We worked with the trust center to make c:\vstotemp a trusted location including subfolders.
• We saved our word document to c:\vstotemp\vstoandvba.docm by picking save as and saving in the macro-enabled file format (docm extension)

Next time, I'll show you how to create a VSTO project in the trusted location using the vstoandvba.docm file, and show you that VSTO code can call VBA code associated with the same document.

VSTO 2008 Case Studies

Slavishly copied from John Durant's blog, I hadn't seen these before so I thought I'd pass them one.  They are two case studies showing how CME Group and Dell used Visual Studio 2008 and the new features of VSTO in their solutions.

CME Group Link to Case Study

• Microsoft Visual Studio
  • Microsoft Visual Studio Team System 2008 Team Suite
  • Microsoft Visual Studio 2005 Team Foundation Server
• Microsoft Server Product Portfolio
  • Windows Server 2003 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
• Microsoft Office
  • Microsoft Office Excel 2007
  • Microsoft Office Word 2007
• Solutions
  • Office Business Applications
  • Technologies
  • ClickOnce application deployment technology
  • Language Integrated Query
  • Microsoft .NET Framework 3.5
  • Microsoft SQL Server 2005 Replication Services
  • Microsoft Visual Studio Tools for the Microsoft Office system
  • Windows Forms
  • Windows SharePoint Services

Dell   Link to Case Study

• Microsoft Visual Studio
  • Microsoft Visual Studio Team System 2008 Team Suite
  • Microsoft Visual Studio 2005 Team Foundation Server
• Microsoft Office
  • Microsoft Office Word 2007
  • Microsoft Office SharePoint Server 2007
• Microsoft Server Product Portfolio
  • Windows Server 2008
• Services
  • Microsoft Technology Center
• Solutions
  • Office Business Applications
• Technologies
  • C# 3.0
  • Internet Information Services (IIS) 7.0
  • Microsoft .NET Framework 3.5
  • Visual Studio Tools for Office
  • Windows Presentation Foundation

Video on VSTO Power Tools

There's a nice intro video to the VSTO Power Tools we recently released on Channel 9.  Hopefully when i get a little more time, I'll be able to blog a bit more about these tools.  For now, check out the video intro:

http://channel9.msdn.com/ShowPost.aspx?PostID=397787#397787

VBA and VSTO Can Be Friends (Part I)

One of the cool new features of VSTO 3.0 is better support for what I would term "Extending VBA with VSTO".  Our strategy here and what we recommend to customers is that we aren't going to try to migrate all your VBA code to VSTO--this is a bit of an unsolvable problem to get 100% right.  Instead, what we want to enable is to allow you to take your existing VBA solutions and add to them with VSTO and .NET.

Why would you want to do this?

Well, there are a ton of developer technologies in .NET that would really make your life more productive and your VBA solutions more rocking if you take advantage of them.

As an example, you can extend VBA with VSTO and create new UI for your VBA solutions in WinForms or WPF.  The .NET forms stories are way more powerful than VBA Forms.  Other things you might want to do is use the new WCF support in Visual Studio 2008 to consume Web Services from your VBA solutions.

As further motivation, VSTO provides easier access to Office features like the Ribbon, the custom task pane, Smart Tags, winforms controls that you can put in Office documents, binding .NET data sources to Excel's List object, and the list goes on and on.  You can use these features with your existing VBA solutions

In my next post, I will show first how you can call VBA code from VSTO and in a later post how you can call VSTO code from VBA.

OBA Sample Application for PeopleSoft

During Kurt DelBene’s keynote at Collaborate 08 the OBA Sample Application Kit for PeopleSoft—a new addition to the small family of OBA Sample Application Kits—was announced  This kit was designed for developers and architects to download the kit and learn how they can integrate Outlook 2007 and MOSS 2007 with PeopleSoft. 

You can download the kit directly here: http://code.msdn.microsoft.com/obapsftsak. You can alternatively visit our new OBA Sample Application Kits home-page on MSDN here: http://msdn2.microsoft.com/en-us/office/cc442491.aspx.

Some additional information on the kit is below:

How Do I Get My VSTO Add-in for Office 2007 to Install for All Users?

This is a common question with VSTO 3.0 add-ins.  Office only allows VSTO add-ins to be registered under HKCU in the registry which means they can only be installed for the user that runs your add-in setup on their account, not available to all users on a machine after you run the installer under one account.

There are some reasons for this behavior--in general it is better to not install for all users unless you absolutely have to as it requires an elevation of privilege to write to HKLM and it assumes all accounts that are on a machine need that add-in which may not be a reasonable default.  But you will have cases where you need to get around this limitaiton.

Fortunately, Misha Shneerson has blogged about a workaround to get over this issue.  See his two articles on the topic:

Deploying your VSTO Add-In to All Users (Part I)

Deploying your VSTO Add-In to All Users (Part II)

ApplicationClass Needn't Be Avoided

Geoff Darst--an engineer on our team--sent this to me a while back and I think it would be good to pass it on. I still think in general people avoid using ApplicationClass because it makes your code look like the VBA and VB6 code everyone is familiar with.  But Geoff points out that there is no need to explicitly avoid ApplicationClass.  You may need to revisit the blog post he refers to in order to get the context where his comments make sense.

I just stumbled on your blog post talking about the types in the Office PIAS (http://blogs.msdn.com/eric_carter/archive/2004/05/06/127698.aspx) and I wanted to clear up a misconception you are spreading in your post.  The issue has to do with the way tlbimp deals with CoClasses.  As you know, for CoClass such as Application, it will create an interface named Application and a class called ApplicationClass.  When you suggest users should disregard ApplicationClass in favor of Application, you are offering some potentially bad advice. 

The class interface (i.e. Application) exists solely to match VB6 behavior where you could new up a CoClass and not have to “pick” an interface (by casting).  IOW with COM CoClasses you had to attribute a default interface in the typelib.  A language like VB would grok the typelib for the default and handle doing the QI under the covers for you so you could just new up the type without worrying about interface.  So back to .Net.   The class interface consists of *only* the default interface methods, plus any event hookups and the GUID.  The class on the other hand, (i.e. ApplicationClass) implements the methods on *every* interface (plus event hookups,  the GUID and the ClassInterfaceAttribute).  In general, you would want to use the class interface because that way you get full access to methods without having to cast.  The class interface is great for porting VB6 code, but it isn’t really the best choice outside of that.

How to get VSTO ClickOnce add-ins to "Update Now"

Kristopher Makey, an engineer on my team, blogs about how to add an "Update Now" button to get a VSTO add-in to force an update.  Update: He has a second article which is more complete here. Unfortunately it's a bit of a hack for now, but if you really need to add this functionality (a button the user can use while the app is running to force an update) at least this should get you unblocked.

You can also set the add-in to check for updates every time it is launched by using the Updates.. button in the Publish tab of the project properties.  In the Customization Updates window you can set to check every time the customization runs. 

We didn't pick "check every time the customization runs" as the default in VSTO because we were concerned about performance of Office application startup.  Imagine that you are running Outlook and you have 10 add-ins and they are all checking on startup for updates--going out to web sites, etc.  This could be bad news every time Office starts up.  So our default is to check every week for updates.  If you are in a pretty locked down environment and you know how many add-ins are out there, it might be appropriate to choose "check every time".  Also, for document level customizations in Excel and Word it isn't as big a deal to pick "every time" as it is for application level add-ins.

Microsoft Office Interactive Developer Map v.2

Something to check out if you haven't seen it yet.  This was released earlier this year.

http://msdn.microsoft.com/office/devmap

The Microsoft Office Interactive Developer Map is a Windows Presentation Foundation (WPF) application that helps developers visualize the different programs, servers, services, and tools that will help them build solutions. It allows them to drill down to each product and technology and learn about new features, objects, Web services, namespaces, and schemas required to extend Microsoft Office and build custom Office Business Applications (OBAs). This application also includes links and pointers to online resources available on Office Online, MSDN, TechNet, Channel 9, Channel 10, and OBA Central.

Setting the BackColor to match the Office 2007 color scheme

Daniel Molina, a developer on my team, shared this code which is useful if you want to detect the Office color scheme (e.g. black, silver, blue) and try to match the backcolor of your ActionsPane or other UI you show to match that color.

A couple of caveats:

- The user might change the color scheme while running the Office application and there is no event that is available to tell you this.  So the user would have to restart the Office application to see the new theme color reflected in your custom UI.

-This code is hard coded to Office 2007, and there are no guarantees that it would work in the next version of Office (and it won't work with Office 2003 since there isn't this notion of color schemes).

        public static void GetBackColor(out byte r, out byte g, out byte b)
        {
            const string OfficeCommonKey = 
              @"Software\Microsoft\Office\12.0\Common";
            const string OfficeThemeValueName = "Theme";
            const int ThemeBlue = 1;
            const int ThemeSilver = 2;
            const int ThemeBlack = 3;
 
            using (RegistryKey key = Registry.CurrentUser.
              OpenSubKey(OfficeCommonKey, false))
            {
                int theme = (int)key.GetValue(OfficeThemeValueName);
 
                switch (theme)
                {
                    case ThemeBlue:
                        r = 227;
                        g = 239;
                        b = 255;
                        break;
 
                    case ThemeSilver:
                    case ThemeBlack:
                        r = 240;
                        g = 241;
                        b = 242;
                        break;
 
                    default:
                        var color = System.Windows.SystemColors.ControlColor;
                        r = color.R;
                        g = color.G;
                        b = color.B;
                        break;
                }
            }
        }