RTD Server Initial Values plus static class with shared data

[Random123]

Hi.

I am trying to implement an RTD Server and have the requirement that upon opening the workbook the user will be asked "Do you want to refresh data?" and if the user says "No" the RTD Server should return the values already stored in workbook and now try to populate the workbook with new data.

After some searching I have found several links that state the XLCall.RTD doesn't support initial values, because of the way it registers the RTD with random GUIDs

https://groups.google.com/forum/#!topic/exceldna/rWFj70es5T0

instead its recommended to declare the RTD Server with

 [ComVisible(true)]

 [Guid("F9DBD653-57E0-4DDD-9474-440340A6C289")]

 [ProgId(CommonInfo.RtdServerName)]

 [ClassInterface(ClassInterfaceType.None)]

and call the method

ComServer.DllRegisterServer();

on AutoOpen from the IExcelAddIn interface.

And use the

XlCall.Excel(XlCall.xlfRtd,

which translates to the excel function =RTD("").

I have tried this approach and the behavior seems to be different altogether. I was used that EVERY UDF gets evaluated at Workbook open but this doesn't seem to be the case anymore is this a known fact or did I something wrong?

But that's not my main problem, its more the fact that I fear that with those changes I have lost the ability to share data with static classes like stated here:

https://groups.google.com/forum/#!msg/exceldna/hzIK_dHHQ1c/Z3Op8SxdmKgJ

So I wonder what's the current possible solution for my problem.

I would need those 2 things working together

1. Static classes - shared across my UDF/ExcelRibbon/RTD Server (UDF/ExcelRibbon are in same dll, RTD Server is in a separate DLL)

2. RTD Server returning initial values on workbook open

Hope you can help me out.

[Govert van Drimmelen]

I think you have the story right.

One reason why the RTD server might be loading into a different AppDomain is if it is not being hosted by the .xll (thus the registration is with InprocServer32 set to mscoree.dll instead of MyLibrary.xll).

You need to check that the option "Register for COM Interop" is *not* on for the project that builds your .dll. If it was on, you might need to look for your library in the registry and manually clean up, or change the ProgID and CLSID (at least to test).

After that, you should find that the RTD server runs in the same AppDomain as the rest of the add-in (fixing your static variables). You can trace out the AppDomain.CurrentDomain.FriendlyName from the RTD server code and elsewhere in your add-in to check.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Random123 [buzzplay...@gmail.com]
Sent: 08 September 2015 12:29 PM
To: Excel-DNA
Subject: [ExcelDna] RTD Server Initial Values plus static class with shared data

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Random123]

Just to make sure, even though I call the method

ComServer.DllRegisterServer();

which does the same as the option "Register for COM Interop" as I have read some where on the web (maybe this information is wrong).

ExcelDna loads the RTD into the AppDomain of the addin and uses it?

What would happen if someone types in the =RTD function it self into excel is it still in the ExcelDna AppDomain or does Excel than create it a second instance in a different AppDomain?

[Random123]

About the "Register for COM Interop" option, does anything in ExcelDna require this setting?

I have read that the RTD Server doesn't need it, but I have tought the ExcelComAddIn and/or ExcelRibbon will need it.

[Govert van Drimmelen]

Register for COM Interop must always be off for all libraries related to Excel-DNA.

If it was on once when building, there might be some entries left in the registry that can still make a mess.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Random123 [buzzplay...@gmail.com]

Sent: 08 September 2015 01:14 PM
To: Excel-DNA
Subject: Re: [ExcelDna] RTD Server Initial Values plus static class with shared data

About the "Register for COM Interop" option, does anything in ExcelDna require this setting?

I have read that the RTD Server doesn't need it, but I have tought the ExcelComAddIn and/or ExcelRibbon will need it.

--

[Govert van Drimmelen]

"Register for COM Interop" must always be off for all libraries related to Excel-DNA.

If it was on once when building, there might be some entries left in the registry that can still make a mess.

"ComServer.DllRegisterServer()" does _not_ do the same as "Register for COM Interop". The former registers the .xll as the COM class InprocServer32, and will register under the user's key if needed. In the other hand, "Register for COM Interop" will register the .NET runtime as the COM server host (InprocServer32=mscoree.dll) and always registers into the machine hive.

If you've never done "Register for COM Interop" and only done "ComServer.DlLRegisterServer()" then the =RTD will activate the RTD server through the .xll, and it will load into the add-in's AppDomain (even if the add-in was not already loaded as an Excel add-in).

I think one problem is you've run "Register for COM Interop" then there are entries in the machine hive of the registry, and then "ComServer.DllRegisterServer()" might not overwrite those.

To understand the intended behaviour, start with a new project that has a new assembly name, new ProgId and new Guids, never switch on "Register for COM Interop" and experiment with that. It should work exactly as you want it to. Then you can go back to your original project and figure out how to clean up the registry, it's there's still a problem.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Govert van Drimmelen [gov...@icon.co.za]
Sent: 08 September 2015 02:14 PM
To: exce...@googlegroups.com
Subject: RE: [ExcelDna] RTD Server Initial Values plus static class with shared data

[Random123]

Looking good so far, though I am not actually getting the cached value... is there a possibility to somehow read the value?

The only thing I am getting so far is an object of type System.Runtime.InteropServices.ErrorWrapper with the ErrorCode -2146826246

[Random123]

protected override object ConnectData(Topic topic, IList<string> topicInfo, ref bool newValues)

I have already tried calling the base method and checking topic.Value any other ideas how I would read/access the cached value?

To post to this group, send email to exc...@googlegroups.com.

Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

I don't know of any way in Excel to access the cached value for an RTD cell.

Excel might not even store a cached value for the RTD topic, but only for the cell.

If there is any way to get it in Excel, I'd be happy to make it available in Excel-DNA.

If you have a persistently registered RTD server, then setting newValues to false should cause the old cell value to be used. But I haven't tried that for a while.

You might have to consider another approach, either in the custom properties of the workbook, or in a separate file in the computer. It's a messy path to go down....

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Random123 [buzzplay...@gmail.com]

Sent: 30 September 2015 12:21 PM

To post to this group, send email to exce...@googlegroups.com.

[Random123]

If you have a persistently registered RTD server, then setting newValues to false should cause the old cell value to be used. But I haven't tried that for a while.

This works just fine, I just would have needed this cached value for future reference.

Since you mentioned the cell, that was kinda my old approach to solve this problem.

But I just simple faced the problem that I couldn't distinguish what's the value from my UDF and what's random stuff added to my formula.

So for example I have the following formula in my cell:

="Hello World " & MyUdf()

If I would recalculate this formula with F2 + Enter, I would have returned the cell.Value in my excel addin resulting in "Hello World Hello World [ValueOfMyUdf]", which would result in a never ending story every time the user recalcs.

In the end I kinda want an "offline" mode where the user can recalculate as often he wants to, my udfs always and at all time return the value it last fetched in "online" mode.

Would you think its realistic to create like several hundreds (if not thousands) custom properties to store those old values?

[Govert van Drimmelen]

Does the cache really have to live inside the workbook?

I'd just put a little database on the user's machine.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Random123 [buzzplay...@gmail.com]

Sent: 30 September 2015 06:39 PM

To post to this group, send email to exce...@googlegroups.com.

[Random123]

It would be best if so since I would love to have the possibility that User A shares his workbooks with User B and User B should see the values as User A last had it.

[Random123]

The only down side I could think off with custom properties is that the file size would increase or do you have some other concerns about this approach except for the fact that its not really a "nice" approach, the database would have been nice but its just that the workbooks can easily shared across users, where every excel is configured to connect to a different place and User A asks User B too look at the excel without User B actually having the data source at hand.

Maybe you have a different idea when I tell you why I would need the reference at the first place, assume you have the following formula in a cell:

=TEXT(NOW(); "dd.MM.yyyy") + PrintJobName()

Everytime a user opens the workbook this cell gets recalculated with the NOW() is a volatile function but my PrintJobName() function should return the cached value. Upon ConnectData I am static newValues = false but now my C# code in the function kicks in and I need to return something - all I have is the mentioned System.Runtime.InteropServices.ErrorWrapper and the cell with its .Value, .Value2 and .Formula all don't seem to get my anywhere good and of course I could get a new value but that's the least I would want.

[Govert van Drimmelen]

Maybe it’s simpler if you store the data in a hidden sheet?

 

-Govert

To post to this group, send email to exce...@googlegroups.com.

[Random123]

I will think about it, will post back.

[Alex Strickland]

On 2015-10-02 11:44 AM, Govert van Drimmelen wrote:

Maybe it’s simpler if you store the data in a hidden sheet?

Or I have some memory that it is easy to embed XML files.

--
Regards
Alex

[Random123]

@Alex Strickland: thanks for the idea gona have to look into this topic, before I can give any information if this works or not.

I have a follow up question about the permanently registered RTD Server, do I need to call ComServer.DllUnregisterServer() at any time, and what about the ComServer="true" in .dna file do I need to set it? Currently my .dna file looks like this:

<DnaLibrary Name="Excel Addin" RuntimeVersion="v4.0" Language="C#">

<Reference Path="WCFLibary.dll" Pack="true" />

<Reference Path="ServerCore.dll" Pack="true" />

<Reference Path="Core.dll" Pack="true" />

<Reference Path="OfficeHelper.dll" Pack="true"/>

<Reference Path="RTDServer.dll" LoadFromBytes="false" Pack="true" ExplicitExports="true" ComServer="true" />

<ExternalLibrary Path="ExcelAddin.dll" LoadFromBytes="false" Pack="true" ExplicitExports="true" />

<!--//rfa 20150608 - make sure to use the none packed version to debug... to avoid the problem with different dlls getting loaded...-->

</DnaLibrary>

Where the RTDServer.dll holds the type to my RTD-Server and ExcelAddin.dll holds the Excel Functions (UDFs).

[Random123]

Could you also provide information what registry keys are invalid for the permanently registered RTD Server today we had the problem that the instance wasn't created in the current addin, had breakpoint in constructor but it didn't fire correctly.

[Govert van Drimmelen]

For a persistently registered RTD server you do need ComServer="true".

You would call DllUnregisterServer (or RegSvr32 /u ...) only if you need to remove the registration for some reason, e.g. Uninstalling your add-in.

-Govert

[Govert van Drimmelen]

The registration is incorrect if the InProcServer32 key points to mscoree.dll instead of the .xll file.

This can happen if you have the 'Register for COM interop' setting enabled in your project, or if you run some kind of registration (like RegAsm) on your .dll file.

-Govert

On 09 Oct 2015, at 21:42, Random123 <buzzplay...@gmail.com> wrote:

Could you also provide information what registry keys are invalid for the permanently registered RTD Server today we had the problem that the instance wasn't created in the current addin, had breakpoint in constructor but it didn't fire correctly.

--