One commonly asked for feature in VSTO is the ability to use Word document and Excel workbook features in Application-level add-in projects. Starting with Visual Studio 2008 SP1, there is now a mechanism to use many of the document-level features in your application-level add-in projects.
Using this new mechanism, you can get a VSTO host item object for a document, worksheet, or workbook from a PIA object like Microsoft.Office.Interop.Word.Document, Microsoft.Office.Interop.Excel.Worksheet, or Microsoft.Office.Interop.Excel.Workbook. Once you have the VSTO host item object, you can then use features like the Controls collection to add host item controls to the document that can be data bound. You can also use features like smart tags at a document or workbook level.
Preparing a pre-Visual Studio 2008 SP1 project
To prepare an add-in project that was created prior to Visual Studio 2008 SP1 to use document level features, you must first add a reference to Microsoft.Office.Tools.Excel.v9.0 if it is an Excel add-in project and Microsoft.Office.Tools.Word.v9.0 if it is a Word add-in project. This reference contains the definitions for document level types for Excel and Word respectively.
Once you’ve added the correct reference, make sure your ThisAddin.cs file has the following line of code added at the top of the file with the other using statements. For an Excel add-in, this using statement is required:
using Microsoft.Office.Tools.Excel.Extensions;
For a Word add-in, this using statement is required:
using Microsoft.Office.Tools.Word.Extensions;
If you create a new add-in project in Visual Studio 2008 SP1, all these things will be added automatically.
Getting a VSTO Host Item Object for a Document, Worksheet, or Workbook
Once you have the project prepared properly, you now can directly obtain a VSTO host item object from the PIA object for a Document, Worksheet, and Workbook using the method GetVstoObject. VSTO uses a new language feature called extension methods to extend the Microsoft.Office.Interop.Excel.Worksheet, Microsoft.Office.Interop.Excel.Workbook, and Microsoft.Office.Interop.Word.Document with a new method called GetVstoObject. GetVstoObject returns the corresponding VSTO host item object: Microsoft.Office.Tools.Excel.Worksheet, Microsoft.Office.Tools.Excel.Workbook, and Microsoft.Office.Tools.Word.Document respectively.
So to get a VSTO host item object for the Microsoft.Office.Tools.Word.Document object returned by the ActiveDocument property of Word’s Application object, you would write code like this in a Word add-in project:
Microsoft.Office.Tools.Word.Document vstoDocument =
Globals.ThisAddIn.Application.ActiveDocument.GetVstoObject();
To get a VSTO host item object for the Microsoft.Office.Tools.Excel.Workbook object returned by the ActiveWorkbook property of Excel’s Application object, you would write code like this in an Excel VSTO add-in project:
Microsoft.Office.Tools.Excel.Workbook vstoWorkbook =
Globals.ThisAddIn.Application.ActiveWorkbook.GetVstoObject();
To get a VSTO host item object for the Microsoft.Office.Tools.Excel.Worksheet object returned by the ActiveSheet property of Excel’s Application object, you would write the code shown below. The code is slightly more complex because ActiveSheet returns an object that must be cast to a Microsoft.Office.Interop.Excel.Worksheet object before calling GetVstoObject.
Microsoft.Office.Interop.Excel.Worksheet sheet =
Globals.ThisAddIn.Application.ActiveSheet as
Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Tools.Excel.Worksheet vstoWorksheet =
sheet.GetVstoObject();
Getting a VSTO Host Item Control for ListObject and Other Host Item Controls
Visual Studio 2008 SP1 also adds support to get a host item control directly for a Microsoft.Office.Interop.Excel.ListObject object by adding the GetVstoObject method to this type via extension methods. This means you can write code like the following to get a Microsoft.Office.Tools.Excel.ListObject host item control object, assuming that the active worksheet has at least one list object:
Microsoft.Office.Interop.Excel.Worksheet sheet =
Globals.ThisAddIn.Application.ActiveSheet as
Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Tools.Excel.ListObject vstoList =
sheet.ListObjects[1].GetVstoObject();
To get to the other host item controls object, you can first get a VSTO host item object for a Document or Worksheet and use the Controls collection to dynamically create host item controls, e.g.:
Microsoft.Office.Tools.Word.Document vstoDocument =
Globals.ThisAddIn.Application.ActiveDocument.GetVstoObject();
Microsoft.Office.Tools.Word.PlainTextContentControl vstoTextControl =
vstoDocument.Controls.AddPlainTextContentControl(
vstoDocument.Range(ref missing, ref missing), "My Control");
Other Applications and Limitations
Given a host item for a document, worksheet, or workbook you have a lot of VSTO document functionality available to you. You can dynamically create host item controls like NamedRange and ListObject and data bind to those controls. You can add Windows Forms controls or WPF controls to the document. You can also add document level smart tags as described in my previous post.
Also it is important to note the document level features that are not accessible via this feature. It is not possible to create a document level ribbon in an Application add-in or access the document level ActionsPane that document level VSTO customizations enable. It is also not possible to work with controls that cannot be added dynamically to the Controls collection (XMLNode, XMLNodes, and XMLMappedRange).
VSTO’s document-level Smart Tags are great when you want to recognize a term in a particular document or a class of document created from a template. What are your options when you want to recognize a term in all open documents?
I'm going to show two ways--the easy way and the hard way. My first version of this post ignored the easy way--hence the repost. The easy way is the way you will add a smart tag that you want to be active in all documents. The hard way accomplishes the same thing, but is a little more flexible in that you can be selective about what documents have what smart tags--for example you could add a smart tag conditionally to a document based on its content or you could add one type of smart tag to certain documents and another smart tag to other documents. I also want to show the hard way as well because it introduces GetVstoObject which is a way to access document level features at the add-in level.
The Easy Way
Starting with VSTO 3.0 SP1, you can create a VSTO add-in that can access its own VstoSmartTags collection. This now allows you to use VSTO Smart Tags at the add-in level. To use VSTO Smart Tags in an add-in, first make sure you have SP1 of VSTO 3.0 and Visual Studio 2008 installed. Also note that end-users of your solution must have SP1 of the VSTO 3.0 runtime installed.
Next, create a new Word or Excel add-in in Visual Studio 2008 SP1. New add-in projects created with Visual Studio 2008 SP1 have a VstoSmartTags collection as a member of the ThisAddIn class.
The code below shows a VSTO Word add-in that recognizes the term “Mackerel” and “Halibut” in all Word documents by using the new VstoSmartTags collection available to VSTO Word and Excel add-ins.
using System;
using Word = Microsoft.Office.Interop.Word;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Word;
using VSTO = Microsoft.Office.Tools.Word;
using Microsoft.Office.Tools.Word.Extensions;
using System.Windows.Forms;
namespace WordAddIn3
{
public partial class ThisAddIn
{
SmartTag mySmartTag;
VSTO.Action myAction;
VSTO.Action myAction2;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
mySmartTag = new SmartTag(
"http://vsto.aw.com#fish", "Fish Catcher");
mySmartTag.Terms.Add("Mackerel");
mySmartTag.Terms.Add("Halibut");
myAction =
new VSTO.Action("&Fishing///&Catch a fish...");
myAction2 =
new VSTO.Action("&Fishing///&Throw it back...");
mySmartTag.Actions =
new VSTO.Action[] { myAction, myAction2 };
myAction.Click += new
ActionClickEventHandler(myAction_Click);
myAction2.Click += new
ActionClickEventHandler(myAction2_Click);
VstoSmartTags.Add(mySmartTag);
}
void myAction2_Click(object sender, ActionEventArgs e)
{
MessageBox.Show(String.Format(
"You threw back a fish at position {0}.",
e.Range.Start));
}
void myAction_Click(object sender, ActionEventArgs e)
{
MessageBox.Show(String.Format(
"You caught a fish at position {0}.",
e.Range.Start));
}
#region VSTO generated code
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
}
#endregion
}
}
The Hard Way
The "hard" approach accesses the VstoSmartTags collection associated with each document. This approach is instructive because it will help you to understand the new GetVstoObject method which gives you access to document-level features at the add-in level. You might actually use the hard way approach if you wanted to have an add-in that added different smart tags to different documents.
To use document level features in an add-in, first make sure you have SP1 of VSTO 3.0 and Visual Studio 2008 installed. Also note that end-users of your solution must have SP1 of the VSTO 3.0 runtime installed.
Next, either create a new add-in in Visual Studio 2008 SP1 or if you have an existing add-in, add the line “using Microsoft.Office.Tools.Word.Extensions” for a Word add-in and “using Microsoft.Office.Tools.Excel.Extensions” for an Excel add-in. To access the VstoSmartTags collection associated with a Word document or Excel workbook, use the GetVstoObject extension method that is available off of a Microsoft.Office.Interop.Word.Document object or Microsoft.Office.Interop.Excel.Workbook object.
The code below shows a VSTO Word add-in that recognizes the term “Mackerel” and “Halibut” in all open Word documents. In ThisAddIn_Startup, we iterate over any open documents and call our AttachSmartTag method. The AttachSmartTag method initializes a single SmartTag object that is held in the class variable mySmartTag and two Action objects: myAction and myAction2. These 3 objects can be used with multiple documents. Finally, the line of code below adds the mySmartTag object to the VstoSmartTags collection associated with the Word document object doc passed to AttachSmartTag.
doc.GetVstoObject().VstoSmartTags.Add(mySmartTag);
The GetVstoObject extension method returns the Microsoft.Office.Tools.Word.Document object corresponding to the Microsoft.Office.Interop.Excel.Document object. The VstoSmartTags property is then used to get a VstoSmartTags collection and the Add method of the collection is called to add the smart tag .
After the ThisAddIn_Startup method iterates over currently open documents to attach smart tags, the add-in handles the DocumentOpen and NewDocument events so that when new documents are created or documents are opened a smart tag is added to the VstoSmartTags collection associated with these documents as well.
using System;
using Word = Microsoft.Office.Interop.Word;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Word;
using VSTO = Microsoft.Office.Tools.Word;
using Microsoft.Office.Tools.Word.Extensions;
using System.Windows.Forms;
namespace WordAddIn2
{
public partial class ThisAddIn
{
SmartTag mySmartTag;
VSTO.Action myAction;
VSTO.Action myAction2;
private void ThisAddIn_Startup(object sender,
System.EventArgs e)
{
foreach (Word.Document doc in Application.Documents)
{
AttachSmartTag(doc);
}
this.Application.DocumentOpen += new
Word.ApplicationEvents4_DocumentOpenEventHandler(
Application_DocumentOpen);
((Word.ApplicationEvents4_Event)Application).NewDocument +=
new Word.ApplicationEvents4_NewDocumentEventHandler(
Application_NewDocument);
}
void Application_NewDocument(Word.Document doc)
{
AttachSmartTag(doc);
}
void Application_DocumentOpen(Word.Document doc)
{
AttachSmartTag(doc);
}
void AttachSmartTag(Word.Document doc)
{
if (mySmartTag == null)
{
mySmartTag = new SmartTag(
"http://vsto.aw.com#fish", "Fish Catcher");
mySmartTag.Terms.Add("Mackerel");
mySmartTag.Terms.Add("Halibut");
myAction =
new VSTO.Action("&Fishing///&Catch a fish...");
myAction2 =
new VSTO.Action("&Fishing///&Throw it back...");
mySmartTag.Actions =
new VSTO.Action[] { myAction, myAction2 };
myAction.Click += new
ActionClickEventHandler(myAction_Click);
myAction2.Click += new
ActionClickEventHandler(myAction2_Click);
}
doc.GetVstoObject().VstoSmartTags.Add(mySmartTag);
}
void myAction2_Click(object sender, ActionEventArgs e)
{
MessageBox.Show(String.Format(
"You threw back a fish at position {0}.",
e.Range.Start));
}
void myAction_Click(object sender, ActionEventArgs e)
{
MessageBox.Show(String.Format(
"You caught a fish at position {0}.",
e.Range.Start));
}
#region VSTO generated code
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
}
#endregion
}
}
Do you use VBA (or any other extensibility mechanism) in Office and are you headed to TechEd? If so the we would like to talk with you in an "invitation only" meeting. This is your chance to share your feedback and influence the work that Microsoft is doing in the programmability space. You must be available for either:
· TechEd Dev: Held at 4:30 on Thursday, June 5th
· TechEd ITPro: Held at 3:00pm on Monday, June 9th
If interested, please respond to the following questions to amandas@microsoft.com:
· Dev week or ITPro week session?
· Please describe your current role.
· What's the size of your organization? Workgroup?
· How/when do you use VBA?
· Are you familiar with .NET and Visual Studio Tools for Office? If so, do you use those tools? If not, why not?
We want to hear from you -- come and make yourself heard!
Tony Crider blogs on how you can get Visual Studio to stop bugging you to upgrade an Office 2003 project on a machine where you have Office 2007 installed.
A VSTO add-in for Word that hyperlinks all the acronyms you've used in a Word document (and defined in a dictionary file) to Wikipedia or alternate links.
http://blog.focas.net.au/2008/05/11/WikipediaiseACVSTOWordAddin.aspx
I thought of Amanda Silver on my team when I saw this as I recently received a mail from her that would have taken me days to read had I followed all the link-tacularness.
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.
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.
I'm trying to get my head around all the changes in the Outlook 2007 object model. Some good resources are found on MSDN:
New Objects, Collections, and Enumerations New Members and Constants
Object Model Changes Since Microsoft Office 2003
Developer Issues When Upgrading Solutions to Outlook 2007 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
When we last left our heroes:
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) 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
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 code can easily be called from VSTO. Here are the basic steps.
- Create a document with VBA code in it.
- Declare a public function or subroutine in the VBA code behind the document.
- Figure out a trusted location to create a VSTO project from
- Create a new VSTO project using the document with the VBA code in it--be sure to create the project in a trusted location.
- 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.
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
- 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
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
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.
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:
The OBA Sample Application Kit for PeopleSoft (one in a series of kits) is technical guidance. Learn how to integrate PeopleSoft with Outlook 2007 and Microsoft Office SharePoint Server (MOSS) 2007. Included are a whitepaper, installation document, solution walkthrough, and source code. The kit provides developers with information on how to programmatically integrate PeopleSoft with Office. Learn about Visual Studio 2008, Visual Studio Tools for Office 3.0, and the MOSS 2007 Business Data Catalog. Use these technologies to implement Web services integration with PeopleSoft. 
