Live time on Excel

Question

Good morning everybody.

I developed an application plug-in for Excel using VSTO.

The thing is that I need to have a cell displaying the current time refreshed every 10 seconds.

The =NOW() function of Excel is not refreshed automatically, even when auto-calculation is on.

The best solution I could come up with was to create an RTD server passing the current time as suggested in Kenny Kerr's blog post.

However, with this implementation, the RTD server sometimes stops updating.

I guess this is due to the fact that a Excel is somehow busy or that the cell is not accessible at the time of the update.

It's not really a big deal if the data is not updated, my problem is that the server stops after that.

The implementation of the important functions are the following (sorry it's a bit long):

    public dynamic ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues)
    {
        m_topicId = TopicID;
        m_timer.Start();
        return GetTime();
    }

    private dynamic GetTime()
    {
        return DateTime.Now.ToOADate();
    }

    public void DisconnectData(int TopicID)
    {
        m_timer.Stop();
    }
    public Array RefreshData(ref int TopicCount)
    {
        object[,] data = new object[2, 1];
        data[0, 0] = m_topicId;
        data[1, 0] = GetTime();

        TopicCount = 1;

        m_timer.Start();
        return data;
    }

    public int ServerStart(IRTDUpdateEvent CallbackObject)
    {
        m_callback = CallbackObject;
        m_timer = new Timer();
        m_timer.Elapsed +=new ElapsedEventHandler(m_timer_Elapsed);
        m_timer.Interval = 10000;
        return 1;
    }

    void  m_timer_Elapsed(object sender, ElapsedEventArgs e)
    {
        m_timer.Stop();
        m_callback.UpdateNotify();
    }

I don't really know where the exception could occur, because we never effectively write in any cell.

Hence I was thinking that I could let the timer run (never stopping it until the server is closed).

Do you think this idea is suitable?

Do you think there is a better way of making sure the server never stops updating the cell?

Thanks for you help,

Jeremie

Answer 1

The exception was due to the fact that Excel is sometimes not ready to accept the value from the RTD server, or that Excel is busy and does not handle the UpdateNotify quickly enough for the RTD's liking.

void  m_timer_Elapsed(object sender, ElapsedEventArgs e)
{
    try 
    {
      m_callback.UpdateNotify();
    }
    catch {}
}

Ensured that the RTD server will retry at the next tick.