Persistant Objects in Compiled Assemblies

[David Thompson]

Howzit

I am not quite sure how to phrase this question, so I am just going to say it and hopefully if you can, you will be able to shed some light on this.

We have excel reports at our clients that use pivot tables (there are hundreds of excel files), and they all use a legacy ActiveX dll written in VB6 that contains functionality to connect to our SQL database and perform some operations, as well as returning data to the report. Its very generic and its worked quite well for the last 9 years.

However we have not been able to get it to work on 64-bit versions of Office and it seems to be a show stopper. Management would like the dll to be re-written in .NET to take advantage of the .NET framework etc etc.

I saw Excel-DNA being recommended on stackoverflow.com so I have been looking at it all day and its very impressive.

My query is this: It would seem that your system is limited to calls to static methods only, and there is no persistance of the .NET object in memory. I would like to make multiple calls to my object to query different things but I seem to be limited to just making single calls where I would have to pass in extra parameters so that the object would know what to do. (Do you follow?)

Is there a way to make a call to a persistant object? If not I was thinking of writing some sort of caching mechanism into my class and then I would use a GUID to keep my 'session' - the object would lookup the GUID in the cache and 'remember' what it was doing before.


Thanks

Dave

[Govert van Drimmelen]

Hi Dave,

While there is no built-in support for the kind of object handling you
describe, it is not hard to make a simple implementation yourself. The
best search term to find related discussions is probably 'object
handles', with many pointers to threads for you to look through :
http://groups.google.com/group/exceldna/search?group=exceldna&q=object+handles&qt_g=Search+this+group.

There is quite a sophisticated implementation in the (pretty dormant)
ExcelDna.Contrib project, contributed by Rob Howley:
http://exceldnacontrib.codeplex.com/SourceControl/changeset/changes/47430.
I haven't used it myself, but it might give you a few ideas. Initial
discussions can probably be found on the group too:
http://groups.google.com/group/exceldna/search?group=exceldna&q=howley&qt_g=Search+this+group.

The main issue for a serious implementation is to deal with object
lifetime. Suppose you cache the object in some dictionary, returning a
handle to Excel that can be used for further access. How will your
object handler know that the object should release because it is no
longer referenced in any cell? I suspect the right implementation will
use an RTD server - these have a predictable lifecycle between the
Excel function calls and the RTD Server. Some implementation have
periodic checks for use, or even an 'object inspector' to peek behind
the scenes. But implementing this right is still not trivial.

Still, for porting your VBA add-in you could probably follow whatever
pattern worked there as a start.

Regards,
Govert

[Dave]

Thank you. I will look into your suggestions.

This is an awesome project dude - kudos

On Nov 23, 6:35 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Dave,
>
> While there is no built-in support for the kind of object handling you
> describe, it is not hard to make a simple implementation yourself. The
> best search term to find related discussions is probably 'object

> handles', with many pointers to threads for you to look through :http://groups.google.com/group/exceldna/search?group=exceldna&q=objec....

>
> There is quite a sophisticated implementation in the (pretty dormant)

> ExcelDna.Contrib project, contributed by Rob Howley:http://exceldnacontrib.codeplex.com/SourceControl/changeset/changes/4....

> I haven't used it myself, but it might give you a few ideas. Initial

> discussions can probably be found on the group too:http://groups.google.com/group/exceldna/search?group=exceldna&q=howle....