We had a go last on Friday at porting a small VBA add-in that also uses DAO for data access to VB.NET with Excel-DNA and the NetOffice assemblies. It went pretty well, with no nasty surprises.
We have some questions (we were using NetOffice version 1.3, not the latest sources):
* The Recordset.Update method in DAO takes two optional parameters. In the corresponding NetOffice method the parameters are required. (http://msdn.microsoft.com/en-us/library/bb221108(v=office.12).aspx).
* In Excel, the Worksheet.Range property is an indexed property which NetOffice exposes only as Worksheet.get_Range. I know that C# had issues with indexed properties before C# 4, but there should be no issue from VB.NET. For example Range.Value seems to
* In VBA the DAO library brings a bunch of methods and constants into the global namespace, for example an OpenDatabase method. You can see these from the VBA Object Browser when selecting the DAO library and looking at <globals>. This allows one to
call OpenDatabase directly from the VBA code, without having an object reference. In VB.NET you can do the same with static methods - by 'Importing' a .NET type (instead of a namespace), the static methods of that type become accessible in VB.NET without needing
the type qualifier. So a static method 'OpenRecordset' on some DAO.Globals class would be directly available as from VBA.
Do you think it would be possible to make these <globals> exported from a NetOffice type which could bring them in directly into VB.NET? I'm not sure how the COM coclass / interface story works in the back. The Excel API has the same story, with
a <globals> section containing ActiveSheet, ActiveCell etc.
* One trick was that to instantiate the DAO DBEngine object, we couldn't just use 'New DBEngine()' but actually had to add the ProgId: 'New DBEngine("DAO.DBEngine.36")'. I think this has to do with DAO not registering a version-independent ProgId.
* I know .NET does not support 'default properties', but in some cases it might make for very nice compatibility with VBA if one can simulate the default collections and their indexers. For example, on a DAO recordset one can say:
myRs!MyField = "New Value".
which actually means
myRS.Fields!MyField.Value = "New Value" - or equivalently myRs.Fields("MyField").Value = "New Value"
Note that VB.NET still supports the "!" indexed property dereferencing.
I was wondering if it would make sense to generate an indexed property for the Recordset which shortcuts to the default collection's indexed property. Then one could at least write:
myRS!MyField.Value = "New Value".
(This is not a suggestion yet - just a thought on what might be possible. I think rewriting the VBA / VB.NET to add the default properties explicitly is fine, and is cool since it is compatible with with both VBA and VB.NET. )
We packed the Excel-DNA / NetOffice add-in with the new VB.NET assembly into a single-file .xll using ExcelDnaPack.exe. With the various NetOffice assemblies for Excel and the DaoApi.dll, the resulting packed .xll was about 750 KB.
As a general thought, I was thinking that the initial goal for NetOffice was probably compatibility with the Primary Interop Assemblies. But from my point of view, having NetOffice make the VBA -> VB.NET porting experience better is even more valuable.
And these are not necessarily the same goals.
Thanks again for the great project!