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!
I can’t get this to work with AutoOpen in VB Express 2010.
When I break the code in debug and step into the line “XL = ExcelDna.Integration.ExcelDnaUtil.Application” the cursor disapears.. Obviously there’s some kind of exception occuring without me being able to see what..
The same thing happens every time I try to use ExcelDna.Integration.ExcelDnaUtil.Application anywhere(!)
This is my code:
Imports ExcelDna.Integration
Imports Microsoft.Office.Interop.Excel
Public Class StartUp
Implements IExcelAddIn
Public Sub AutoClose() Implements IExcelAddIn.AutoClose
‘Fires when addin is removed from the addins list
‘but not when excel closes – this is to
‘avoid issues caused by the Excel option to cancel
‘ out of the close ‘after the event has fired.
MsgBox(“Bye bye, from DNA”)
End Sub
Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
XL = ExcelDna.Integration.ExcelDnaUtil.Application
MsgBox(“AutoOpen: you are using ” & XL.Version)
End Sub
End Class
Public Module Globals
Friend XL As Application
End Module
Hi,
Try turning it off before debugging it, like this
‘Imports Microsoft.Office.Interop.Excel
Public Module Globals
‘ Public XL As Application
Public XL As Object
End Module
Change the XL var back to object and comment out the imports and the as Application …
let me know if this works.
thanks
Ross
Hi Ross, thanks for the reply;
This didn’t help me much. I don’t think my problem is related to the Interop thingy, I think it’s related to the ExcelDna.Integration library.
Consider the following code (this is all code in the project) the first msgbox is shown, the second is not. Must be something wrong with my setup. I’ve been following your DNA posts and have successfully set up the hello world example without any problems, just can’t do the ExcelDna.Integration part..
Imports ExcelDna.Integration
Public Class StartUp
Implements IExcelAddIn
Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
Dim obj As Object
MsgBox(“This will show”)
obj = ExcelDnaUtil.Application
MsgBox(“This won’t show”)
‘Got same result using
‘obj = ExcelDna.Integration.ExcelDnaUtil.Application
End Sub
Public Sub AutoClose() Implements IExcelAddIn.AutoClose
‘don’t care
End Sub
End Class
What version of DNA are you using?
Hi again, sorry for being so slow on replies. I’ve been a bit indisposed.
I’m currently using:
- DNA v0.27.0.1
- Excel 2007 12.0.6545.5000 SP2 MSO (12.0.6535.5002)
- VS 2010 10.0.30319.1 (VB Express)
- .NET 4.0.30319
In compile settings for my object I’ve chosen Target framework to be “.NET 3.5 Client profile”. I’ve also tried with 2.0, 3.0 and 4.0 without any luck.
I see no build errors in VS after build.
After running in debug I get the following in output window:
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\Windows\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll’, Skipped loading symbols. Module is optimized and the debugger option ‘Just My Code’ is enabled.
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘ExcelDna.Loader’
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll’, Skipped loading symbols. Module is optimized and the debugger option ‘Just My Code’ is enabled.
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘ExcelDna.Integration’
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\Windows\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll’, Skipped loading symbols. Module is optimized and the debugger option ‘Just My Code’ is enabled.
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\Windows\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll’, Skipped loading symbols. Module is optimized and the debugger option ‘Just My Code’ is enabled.
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll’, Skipped loading symbols. Module is optimized and the debugger option ‘Just My Code’ is enabled.
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll’, Skipped loading symbols. Module is optimized and the debugger option ‘Just My Code’ is enabled.
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\UserData\Work\Excel_xll\ExcelDNA_vs\proj\LeifLib\bin\Debug\LeifLib.dll’, Symbols loaded.
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\Windows\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll’, Skipped loading symbols. Module is optimized and the debugger option ‘Just My Code’ is enabled.
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘C:\UserData\Work\Excel_xll\ExcelDNA_vs\proj\LeifLib\bin\Debug\ExcelDna.Integration.dll’
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘ExcelDna.DynamicDelegateAssembly’
‘EXCEL.EXE’ (Managed (v2.0.50727)): Loaded ‘DynamicDelegates’
A first chance exception of type ‘ExcelDna.Integration.XlCallException’ occurred in ExcelDna.Integration.dll
The program ‘[6556] EXCEL.EXE: Managed (v2.0.50727)’ has exited with code 0 (0×0).
I guess the part about “A first chance exception of type” would be a clue to what’s going on..
Hi,
No: “A first chance exception of type” is not really a problem.
I’ll see if I can get the same behaviour
Thanks
Ross
Hi Peder,
The best place to ask for Excel-Dna support is on the Google group at http://groups.google.com/group/exceldna.
In this case you have an extra copy of the ExcelDna.Integration.dll assembly in your output directory. I think the version you have does not match the version of the ExcelDna.xll file you copied and renamed, causing the problem. You never need to have a copy of the ExcelDna.Integration.dll file when deploying or running. The assembly is used as a reference assembly when you are compiling your add-in, and a copy of it is embedded in the .xll and automatically referenced at runtime. However, if you have a copy of the file next to the .xll it is sometimes loaded from there, and if the version is out of sync with the .xll you get such errors.
I suggest you just delete the ExcelDna.Integration.dll in your Debug output directory, and then set the reference in SharpDevelop to “Copy Local: False” (in the properties windows when you select the ExcelDna.Integration reference).
I hope this sorts out your problem. Else the Google group of contacting me directly is likely to get you the quickest response.
Cheers,
Govert
Hi Govert, you nailed it, thanks a lot :-)
I’ve now got everything working as intended. Will remember to post my questions in the Excel-Dna google group next time also.
[...] 3: Adding Intellisense to a DNA solution http://www.blog.methodsinexcel.co.uk/2010/10/28/ading-intellisense-to-a-dna-solution/ Download the PIA .Zip file from the link in that post and extract to your hard disk. Setup up a DNA [...]