How to refresh RTD data in Excel to reattempt XlCall.RTD from add-in

Jan 6, 2014 at 6:48 PM
Edited Jan 6, 2014 at 7:53 PM

I wonder if somebody out there would have an idea of what I am doing wrong.

We have an addin mostly based on ExcelDNA, which by the way is just great.

At some point, one of the functions needs to take a decision based on the result of an XlCall.RTD call before returning a result. The first time I call the function with an specific combination of parameters I get "wait", which is expected because it is what the RTD server I am using returns from ConnectData.

However, without finishing the function, when I detect a "wait" in the result I just do a sleep and try again hoping to get a refreshed result this time but I just get "wait" again.

I suspect the Thread.Sleep that I am using just stops the execution of the same thread that would update the RTD results so I need a way to tell Excel "just go ahead with your calculations" before I try again the XlCall.RTD.

Does anybody have any suggestion about what should be done in my case? Any help would be really appreciated.

Please note that the idea is that the function would XlCall.RTD one or more times until it gets a valid value from the RTD server.

Thanks in advance for your help.

Jan 6, 2014 at 7:52 PM
Edited Jan 6, 2014 at 7:52 PM
I just tried doing an Excel.Application.RTD.RefreshData() instead of the Thread.Sleep but it does "System.Runtime.InteropServices.COMException".
Jan 6, 2014 at 9:29 PM
Edited Jan 7, 2014 at 2:29 PM
I tried by setting "IsMacroType=true" on the ExcelFunction that I am using to test this.

Now, it does not abort on RTD.RefreshData() but I am still getting the same "wait" result after the second call to XlCall.RTD.