schedule a function to run in Excel Add-In

Question

I have an Excel Add-In written in C#. In the addin one ribbon button is "Refresh", when it is clicked, data is fetched from server and plotted in Excel.
Clients want to refresh data every day at certain time or every few hours automatically. They can write VBA code to do that but they do not want to. We are thinking to add the feature in our AddIn so clients do not have write VBA code to accomplish automatic refresh. I am thinking of a timer but not sure how to yet. Please help, thanks

Answer 1

They can write VBA code to do that but they do not want to.

Yeah, Excel does not handle scheduling natively AFAIK.

You could probably write a task scheduler in C#, but if the client doesn't want to write code I don't see that this gains anything.

But why bother, it's already been done: http://windows.microsoft.com/en-US/windows/schedule-task#1TC=windows-7

Now you're out of the frying pan, but in the fire: if the target of the Windows task scheduler is the Excel workbook itself, and the user(s) currently have the file open, many nasty issues ensue.

What you need is task scheduler to target some other file, and have an event listener running in the users' Excel file(s). Which probably means writing VBA code.

You could be stuck.

[edit]

Try this: http://www.mrexcel.com/forum/excel-questions/528653-visual-basic-applications-file-watcher-possible.html

Answer 2

If you have already written an addin in c# it should be straightforward to automate the refresh.

You could start a thread at the point where the adding gets loaded which will wait for a given amount of time before triggering your refresh logic. If you do this you need to make sure that the thread gets cleaned up again when the addin is unloaded (when excel exits). Do not use Thread.Sleep(myRefreshTime) to make it wait between refreshes, as the thread will hang for 2 hours if you only refresh every 2 hours, making disposal of the thread difficult. I recommend using a wait handle to control the thread and make sure it exits in a timely fashion when you need to dispose. Making it trigger a specific time of day should be simple enough too. You can just check what DateTime.Now() is when the addin is loaded, and compute the value of myRefreshTime compared to your fixed time of day.