I have an Excel AddIn written in C#, Excel-DNA, NetOffice On each workbook, users can run some process to fetch data (using REST web service) from a server. Now users want to automatically refetch data (refresh) on each workbook at different intervals or time. Say, on the first workbook, users want to refetch data (refresh) automatically every 5 minutes On the second, they want to refresh data every hour. On the third, they want to refresh data at 1:00pm every day. On the fourth, they want to refresh data at 2:20pm every day, etc.
My thought is for each workbook that users want to auto refresh, add a hidden worksheet to remember at what frequency or what time to refresh.
Set a timer in addin, the timer will go off every minute to check which workbook need refreshing and refresh it.
I feel it will have a big performance hit. Please help to get a better solution. thanks
Using Reactive Extensions, and the support in Excel-DNA for making 'live' RTD formulas from Reactive Extensions IObservables, might give you one approach. You could take the refresh rate as one of the parameters to your function, and then set up your Observable using something like the discussion here: Polling a website using Rx
That resulting IObservable is then exposed to Excel as a 'live' worksheet formula through the RxExcel support in Excel-DNA. Every update of the observable will update the corresponding cells that called that function.