Excel RTD tweaking

[Deepak Krishna]

Im using Excel RTD for all my formula. The reason being i have to batch the requests to the server to minimize the server load and the other option of AsyncBatch is too slow compared to the RTD implementation. 

And None of these formula update once the value is set the first time after a nework call. Now this network call may fail or user want to refresh the cell for any other reason. I don't see any option to refresh a RTD cell like other normla udfs.

If I change any parameter in RTD formula, the topic gets disconnected. So for implementing a refresh button I did this.

                List<string> formulae = new List<string>();
                foreach (Microsoft.Office.Interop.Excel.Range r in rng)
                {
                    string formula = r.Formula;
                    if (FormData.refreshFuncs.Contains(formula.Split('(')[0].Replace("=", "")))
                    {
                        r.Value = "=snt(\"refresh\")";
                        //r.Value = formula;
                        formulae.Add(formula);
                    }
                }

                int i = 0;

                foreach (Microsoft.Office.Interop.Excel.Range r in rng)

                {

                    string formula = r.Formula;

                    if (FormData.refreshFuncs.Contains(formula.Split('(')[0].Replace("=", "")))

                    {

                        Debug.WriteLine(formula);

                        r.Value = formulae[i];

                        i += 1;

                        //r.Value = formula;

                    }

                }

In short, I'm changing the formula of every cell to another rtd formula "snt("refresh")". Then again restoring the original formula in a second loop. Unfortunately, the refresh formula disconnects but not the original formula topic.

My intention is to disconnect the original formula topic so when I rewrite the formula, it would go fetch the data. 

Its very weird that excel is not disconnecting the topic in the cell even though the formula is removed and a new formula is inserted in the cell.

[Govert van Drimmelen]

Hi Deepak,

 

I have some questions that will help make sense of your situation:

·         What Excel version are you using (Excel 2010 RTM version has a bug related to RTD disconnect).

·         Are you using Excel-DNA’s ExcelRtdServer base class for your RTD server, your own implementation of IRtdServer, or using the ExcelAsyncUtil.Run?

·         Are you using Excel 2016 – there are some issues with how RTD servers get started in this version.

 

I’m not sure what you mean by this statement:

“I don't see any option to refresh a RTD cell like other normal udfs.”

·         How would you refresh a normal UDF?

 

If you have a connected RTD topic for every request, you can update the cell by setting a new value on the topic.

 

If the RTD topic is complete and no longer connected, you should be able to recalculate the cell in the same way you would for a normal UDF.

 

-Govert

--
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 https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Govert van Drimmelen]

Oh - there's one more case.

 * Are you using the RTD function from an array formula? Excel RTD does not properly disconnect when called from array formulas.

-Govert

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+unsubscribe@googlegroups.com.

[Deepak Krishna]

Hi Govert,

 

I have some questions that will help make sense of your situation:

·         What Excel version are you using (Excel 2010 RTM version has a bug related to RTD disconnect).

          It's Excel 2013. 

·         Are you using Excel-DNA’s ExcelRtdServer base class for your RTD server, your own implementation of IRtdServer, or using the ExcelAsyncUtil.Run?

         Yes Excel Dna's RTD server 

·         Are you using Excel 2016 – there are some issues with how RTD servers get started in this version.

 

I’m not sure what you mean by this statement:

“I don't see any option to refresh a RTD cell like other normal udfs.”

     

·         How would you refresh a normal UDF?

 I am using RTD server for a typical udf, where once the network call is completed and value of the topic is updated, I will not update the topic again. In this scenario, when network call fails, I set  a static text like "Network problem". So Now the user would try to refresh the formula by selecting the cell and pressing enter. Obviously this would not refresh the formula since RTD topic kind of caches it and fetches the same value. 

So I created a button on the ribbon for refresh. My intention is to some how disconnect the topic from the server and reentering the same formula, so that the topic fetches the data again from  network. For disconnecting the topic I am writing a new formula into the cell called snt("refresh") and then again setting back the old formula. I thought this would first diconnect the original topic and then disconnect the snt("refresh") formula topic. But this would not happen. The original formula topic would never get disconnected (only the refresh formula gets disconnected)

 

If you have a connected RTD topic for every request, you can update the cell by setting a new value on the topic.

I cannot keep updating the topics cause the number of formulae is humongous.  

 

If the RTD topic is complete and no longer connected, you should be able to recalculate the cell in the same way you would for a normal UDF.

I donot understand this, What do you mean by complete here? The rtd topic would never get disconnected in my case. Only if i manually change the formula it would get disconnected, But if I do so programatically (above code) it is not disconnecting.  

[Govert van Drimmelen]

Hi Deepak,

If it is not an array formula, it should disconnect after you set the result (end the caching for that topic). The user can then refresh a s for a normal function.

If you don't find this, I suggest you make a tiny standalone sample that we can both run and test.

-Govert

[Deepak Krishna]

I'm uploading a test plugin. Please let me know what do you see in the output. 

For me it prints "connecting" and that is it. And it actually makes sense cause it keeps on updating. What I wanted is on a click of a button. I need the topic to be disconnected and able to enter the same formula into that cell again. 

[Deepak Krishna]

Sorry forgot to attach the file

[Deepak Krishna]

Hi Govert, 

Did you get time to run this file

[Govert van Drimmelen]

Hi Deepak,

Instead of having the 'Refresh' button disconnect the topic, I suggest you just update the topic values.

This will cause the cells to refresh, and is the intention behind the RTD mechanism. It's also much, much more efficient than changing the the formulas of all those cells.

Or do I misunderstand what you are attempting to do?

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Deepak Krishna [kr...@sentieo.com]
Sent: 18 May 2016 10:14 PM
To: Excel-DNA
Subject: Re: [ExcelDna] Excel RTD tweaking

Hi Govert, 

Did you get time to run this file