Check out my new blog at

Wednesday, February 13, 2008

Itty-Bitty Add-In & VBA Project Update Engine

A while back I created an add-in for Microsoft Project 2003 that synchronizes SharePoint list values with custom field values. Since then the add-in has grown to include additional functionality implemented both in C# and VBA. This new functionality is changing daily as I work through short-term migration and conversion challenges (we are moving to Microsoft Project 2007). I needed a quick way to push out new bits to the add-in user base without requiring I come around and install things manually. In particular, I needed a way to push out new VBA modules, classes and forms without the option of using an enterprise global template. In just a matter of a couple of hours, I created a very simple update engine.


  • Update an IDTExtensibility2 add-in automatically when a new version is detected. Implies a bootstrap installation scheme because the add-in itself calls the update code

  • For any given Microsoft Project plan, check to see if new VBA objects are available and update said objects

  • Update metadata stored on the LAN

  • Updates are stored on the LAN as MSI for updating the add-in and ZIP archives for VBA objects

  • Don't take more than a few hours to implement

Implementation: Metadata & Add-In Update

Update information is stored on a network drive as XML. The schema is very simple:

image There There are two types of updates indicated in the above XML: type="1" and type="2", which correspond to Enum values UpdateType.AddIn and UpdateType.VBAProject in the update engine. The name attribute for type UpdateType.AddIn is simply the name of the add-in for which an update might be available. To determine if an update is available for a particular add-in and apply it, do the following

Version latestVersion;
Updater updater = new Updater();
updater.Open(UpdateType.AddIn, "ProjectAssist");
if (updater.IsNewerVersionAvailable(Assembly.GetCallingAssembly().GetName().Version.ToString(), out latestVersion))

Calling updater.Open() will go out to the network location where the update metadata XML is stored and load instance data if there is an section with name="ProjectAssist".

The current version of the add-in assembly is passed to updater.IsNewerVersionAvailable. The assembly version is compared to the "latestversion" attribute and returns true or false based on that comparison. The call to updater.UpdateAddIn() updates the add-in bits by running the installation packaged indicated in the "updatelocation" attribute.

The call to update.UpdateAddIn passes the calling process's ID to the update engine. The add-in installation cannot run while the add-in's host application is running. I wrote a little bootstrap that takes a process ID and a path to an MSI that handles launching the setup as soon as the host application is unloaded.

As long as the setup runs correctly, the new version of the add-in will be installed when the host application (in this case, Microsoft Project) runs next.

Not very difficult. Total implementation time was about 30 minutes of coding and about an hour to get it up and running.

Implementation: VBA Project Update

As mentioned previously, some of the functionality I am pushing out is implemented in VBA as a set of forms, modules and class modules. The add-in mentioned in the previous section not only updates itself, but also includes an update engine for updating VBA components in any Microsoft Project file. The VBA objects are collected and zipped into a file also containing a "manifest" of the VBA objects. The manifest is very simple (remember, I wanted this done and working in a matter of a couple of hours). Below is a sample manifest:




@TrainerWiseFx.bas @Reports.bas

The "@" prefix indicates that the object may already exist in the Microsoft Project file and needs to be updated. An "-" prefix indicates that the object should be removed. Not mentioned above but also implemented is a "+" prefix that asks the engine to add the new VBA object. Updating a VBA project requires knowing both the name of the Microsoft Project file and the VBA project name. Similar to updating the add-in, the following code does the job:

Updater updater = new Updater();
updater.Open(UpdateType.VBAProject, _projectApplication.ActiveProject.Name);
if (updater.ProjectName == null) return;
_projectApplication.VBE.ActiveVBProject = _projectApplication.VBE.VBProjects.Item(updater.ProjectName);
if (updater.IsNewerVersionAvailable(_projectApplication.VBE.ActiveVBProject.Description, out latestVersion))
_projectApplication.VBE.ActiveVBProject.Description = latestVersion.ToString();

The call to updater.Open passes the UpdateType and the name of the currently active Microsoft Project file. The Open method loads up the Update object's instance data if there is and Update matching the name of the MPP file. I need to change the Open method to return bool so I don't have to make the updater.ProjectName call on the following line. If there is an Update that matches the MPP file name and the VBA Project name, I set the ActiveVBProject to the named VBA Project and call updater.IsNewVersionAvailable. If there is, I update the VBA project and then set the description of the VBA project equal to the new version number. I chose to hijack the description field because the MPP file version is used for something else and doesn't correspond to the VBA objects' versions. Also, I control all of the VBA that is pushed using this method and remember I had to get this done in a few hours).

I unpack the zip containing the VBA objects into a temp directory and load the manifest. Line by line I perform the requested actions on the VBA project. Nothing real special here (this is all wrapped in a try{}finally{} to clean up the temp directory and release the stream reader):

manifestReader = new StreamReader(manifest); 
string manifestEntry; 
char manifestEntryType; 
while ((manifestEntry = manifestReader.ReadLine()) != null) 
    manifestEntryType = manifestEntry[0]; 
    manifestEntry = manifestEntry.Substring(1); 
    switch (manifestEntryType) 
        case '+':
            vbProject.VBComponents.Import(Path.Combine(tempPath, manifestEntry)); 
        case '-':DeleteVBComponent(vbProject, manifestEntry); break; 
        case '@':DeleteVBComponent(vbProject, manifestEntry); 
            vbProject.VBComponents.Import(Path.Combine(tempPath, manifestEntry)); break; 
            throw new InvalidOperationException(string.Format("Invalidmanifest entry : {0}", manifestEntry)); 

The update ZIP files are deflated using the ICSharpCode.SharpZipLib library available here.

Wrapping It Up

The itty-bitty update engine works great and took just a few minutes to implement. The only thing I am considering adding is a way to update views, tables, filters, fields, and maps in Microsoft Project where a enterprise global is not possible.

No comments :


Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.