A few people have asked how to add intellisense to a DNA solution. Obviously for someone like me who knows the object model so well and has perfect spelling etc.* its not needed, but for lesser mortals here’s how its done!

Setup up a DNA project in the normal way, then download the PIA of your choice.

Wooo, hang on what the hells a PIA?!?!.

A PIA, or Primary Interop Assembly, is a dot Net assembly that lets you use  COM object “stuff” in/from managed code. They are generally considered a pain in the posterior, hey maybe that’s really what PIA stands for…Pain In Ar…! Anyway, see here for a bit more detail.

I’ve saved you some time, by collecting all the available ones here. But you might already have them on your PC, if you do they will be in C:\WINDOWS\assembly\, called something like  “Microsoft.Office.Interop.Excel.XX.dll” where XX is the office version number…

Oh, more pain… now the thing is there’s a PIA for each version of Office/Excel. Each PIA supports the OM for a specific version, so using the 2007 one and deploying the solution against a 2003 version of Excel might cause a problem. DNA does not need the PIA as it uses the C API to talk to Excel, but to get intellisense we need to use a PIA. Oh, and theres no PIA for Excel 2000, but you can roll you own (add a reference to the Excel.exe, and the IDE will make a “IA” , the “Primary” is dropped as its not from the vendor, it gets messy here, lets move on – quickly!)

OK, so, setup up a DNA project in the normal way, download the above .Zip file and extract to your hard disk. Go to the “Project” menu, “Add reference”, then “.Net assembly Browser” then go and find the PIA folder you just extracted, and pick the right version for your needs. Here I’m targeting Office XP!

Notice that a reference is added to the project for the PIA, and can be seen in the project viewer.

Now, how to get it to work. Well the exact way is up to you, but if your using a module to hold a global reference to the Excel application, like we suggested before, then in that module change to code to something like this:

Imports Microsoft.Office.Interop.Excel
  Public Module Globals
      Public zXL As Application
  End Module

Then back in you class module you can use intellisense, ace.

Before you build the final version, make sure you remove the reference to the PIA, you can just delete it from the project viewer, and then delete the dll from the debug/release directory (i.e….\\Intellisense Example\bin\Debug\)

And there you go!

One other thing to note is that it works a little bit better in Visual Studio, SharpDevelop also exposes events, which you can’t use in every content, obviously, but its no big deal!

* vbg – on both counts!