For many years, the only way to add interactivity to an Office workbook or document was to use VBA Macros. However, in more recent years, Microsoft has offered a newer .NET developer tool called Visual Studio Tools for Office (VSTO). To customize a particular document or workbook using VSTO, you can use a project type called “document-level customization.” While the end-user functionality of these solutions overlap, they have very different development processes and practical implementations. In this article, we will discuss how to use VSTO document-level customizations and how they differ from their predecessor.

VSTO in a Nutshell

VSTO offers a developer two different project types: document-level customizations and application-level add-ins. Document-level customizations are projects based around a single workbook or document. Application-level add-ins, on the other hand, are add-ins affecting the UIs of Microsoft Word, Microsoft Excel, Microsoft PowerPoint, Microsoft Outlook, or Microsoft InfoPath applications themselves. In this article, we will talk about document-level customizations.

By leveraging the .NET framework, VSTO can turn a document into a full-fledged application that must be installed onto an end user’s computer. While using .NET offers a programmer nearly limitless possibilities and integration with many other services and libraries, it also means that it requires Visual Studio and strong coding skills to develop the customizations. Updates to a customization will be issued from a  server or disk where the customization is deployed. However, the updates are not without limitations. VSTO updates the code attached to a document, but it does not update the visual formatting done to workbook via Visual Studio’s Designer or Excel. All visual formatting must be done in code to be included in automatic updates. A second limitation is that a user will not be able to have more than one version of the customization on their machine at one time.

Compared with VBA

VBA has been around since 1993 and is the simplest way to add interactivity to an Office document. The basic advantages and disadvantages of using VBA are outlined below:

Creating a Document-Level Customization with VSTO

Before you can use VSTO, please make sure you have all of the developer and client requirements.

1. Open Visual Studio.
2. Click New Project.
3. Expand either the C# or VB menu.
4. Expand the Office menu.
5. Click Excel Workbook/ Word Document.
6. VS will now ask you if you want to create a new workbook from scratch or use an existing Excel document as a template for your new workbook. Select what is appropriate.
7. The project will be created:
   1. The design view of your project will now be shown.
   2. If you are creating an Excel workbook, the default host application, workbook, and worksheet objects will have been auto-generated.
   3. If you are creating a Word document, the default host document object will have been auto-generated.

Developing Your Customization

The most fundamental libraries you will use in your VSTO customization are the native Excel and Word object models and the VSTO Host Items.

Native Microsoft Excel and Word Object Models

• Excel has native classes that make up the object model and they are part of the Microsoft.Office.Interop.Excel and Microsoft.Office.Interop.Word namespaces.
• Excel Model: Application is the parent class and Workbook, Worksheet, Range are its children.
• Word Model: Application is the parent class, and its children are Document, Range, Selection, and Bookmark.

VSTO Host Items

• These are extensions of the native Microsoft Word and Excel objects.
• The host items are part of the VSTO Microsoft.Office.Tools namespace and exhibit almost identical structure, methods, and properties as the native classes.
• The extensions include several additions, such as databinding.
• When you create document-level implementations with VSTO, you will mostly be using host items.

Debugging Your Customization

• After you press F5 to build and debug your project, you must remember to go to the Build menu in Visual Studio and click Clean Solution. This is because ClickOnce, which deploys the solution so you can build it, will store the project in its cache. The cache must be cleared or the project will run every time you open Excel or Word.

Deploying Your Customization to a Server with ClickOnce

The simplest way to deploy your project is by using ClickOnce, which allows users to automatically check for updates to their add-on.

Option 1: Microsoft has a great video that explains the deployment process using the simple Publish method accessed by right-clicking your solution.

Option 2: For the most customization options, we will publish from the Project menu:

1. Go to the Project Menu and click “<project name> Properties”
2. On the side menu click “Publish.”
3. Select a publishing folder location: a disk path, file share, or website to publish the project to.
4. Select the Installation Folder URL: the URL, UNC path, or CD-ROM/DVD-ROM where the .VSTO (VSTO Deployment Manifest) will live on the server. This is the filepath that will store the .exe that will distribute the download to clients. Its important to set this URL because this is where OneClick will check for updates.
5. Under Install Settings, you can set installation prerequisites, update options, and other advanced options.
   1. Click the Updates button.
   2. In the dialog, select how often you want updates to be checked for.
6. Under Publish Version, make sure the Automatically increment revision with each releases is checked.
   1. Click Publish Now.

The published project will now be located in the folder you selected to publish to. In order to complete deployment for an application deploying to the web:

1. Open IIS Manager.
2. Add your project as an application to a site on the IIS server. Be sure the location of your .exe matches what you specified as the installation path when you published the project.
3. You can now distribute your document just as you would any other Excel or Word file.

What Happens When End Users Open a VSTO Customization

When users open the VSTO document customization you created for the first time, the document will connect to the server/disk location where the .exe is installed and begin installing the VSTO code. Each time they open the customization after that, the document will determine whether it should check for a new .exe version based on the update frequency you checked off when you published. For more information on the issues that can happen with updates, refer to this article.

Follow

Related posts: