VBA and VSTO Can Be Friends (Part III)

  • Article

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

image

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:

image

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