Force RTD server to refresh data

[Johann]

How do I go about forcing my RTD server to refresh specific data.
I want it to work in a similar manor as Excel, ie, hit F2, then hit
enter. Problem is the RTD just returns the "cached" values.

[Govert van Drimmelen]

Hi Johann,

You need to call UpdateNotify() on the IRtdUpdateEvent interface you
are given in ServerStart. Then Excel will call your RefreshData, where
you tell Excel what has changed.

The Excel RTD FAQ here: http://msdn.microsoft.com/en-us/library/aa140060(v=office.10).aspx
is a great introduction, and the Kenny Kerr articles are also very
useful for this - here's one http://weblogs.asp.net/kennykerr/archive/2008/11/13/Rtd3.aspx.

Regards,
Govert

[Johann]

Thanks for the reply Govert.

I would like to explain my current setup a bit more, which I think I
will need to change to get working properly.

I currently have a "Topic" class, which I use to asynchronously fetch
data from a WCF service.
On my RTD server, I have a collection of "Topics" that grows as they
come through from Excel. RTD calls a function on "Topic" to async
fetch the data.

I do not currently have a timer on my RTD server, instead relying on
my "Topic" class to call UpdateNotify() on the RTD server through a
callback delegate, as the data comes back from the WCF service. Not
sure if this is the best thing to do, based on the whole Com
compartment idea, but does work nicely at the moment.

What I am struggling with is forcing my RTD server to update certain
formula values.

What I'm thinking to do is the following:
Lets say I implement a timer on my RTD, which, on tick, will only call
UpdateNotify() if any of the "Topics" in my collection are dirty.
Question is, how do I set a "Topic" as dirty from Excel on my RTD
"Topic" collection.

Is there another solution I'm missing? Could I implement something on
my current solution?

Thanks

On Mar 19, 5:28 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Johann,
>
> You need to call UpdateNotify() on the IRtdUpdateEvent interface you
> are given in ServerStart. Then Excel will call your RefreshData, where
> you tell Excel what has changed.
>
> The Excel RTD FAQ here:http://msdn.microsoft.com/en-us/library/aa140060(v=office.10).aspx
> is a great introduction, and the Kenny Kerr articles are also very

> useful for this - here's onehttp://weblogs.asp.net/kennykerr/archive/2008/11/13/Rtd3.aspx.

[Govert van Drimmelen]

Hi Johann,

The call to UpdateNotify() should be from the main Excel thread, else
you are violating the COM apartment threading rules. Somoe more info
here: http://weblogs.asp.net/kennykerr/archive/2008/12/17/Rtd8.aspx.

> Question is, how do I set a "Topic" as dirty from Excel on my RTD
> "Topic" collection.

You call UpdateNotify whenever some topic in your RTD Server has
changed. Then after a while Excel calls your RefreshData and you
return an array with all the topics that have changed. This is where
you tell Excel which topics are 'dirty'. Excel then fills in the cells
or calls the UDF wrappers as needed.

So your timer tick plan sounds good.

-Govert

[Johann]

I think what I was hoping for, is way the user can manually refresh
the data, and not rely on the RTD server.
My RTD is not subscribed to a feed, as I want the user the refresh
when needed.

Or, if I could somehow disconnect from RTD after the data is
populated.

[Govert van Drimmelen]

Hi Johann,

If the function values will be different for different calculations,
you could mark the function as Volatile:
[ExcelFunction(IsVolatile=true)]. Then the function will behave like
=NOW() or =RAND(), which gets recalculated whenever something on the
sheet changes (the user can press F9 to recalc). If you're worried
about it calculating too often to a slow back-end, put some caching in
place, so that the real calculation only runs when you expect the data
to be different.

If you want to give the user a ribbon button or shortcut key that
would update only that function, you could have the ribbon button
trigger a search-and-replace for your function name, which will
effectively invalidate all the cells with that function in.

Another way is to have an RTD 'feed' in the background that is your
own RTD server, just used to trigger the recalculations. When the
ribbon button is pressed, you get the RTD server to call UpdateNotify
based on the ribbon button press, which will in turn trigger Excel to
recalculate - this should be an elegant solution but a bit tricky to
implement.

So the RTD server's UpdateNotify does not have to be triggered only by
a change in the back-end data, it can also be triggered by your ribbon
button.

Does that help?

-Govert