none
Avoiding AutoFocus RRS feed

  • Question

  • Good afternoon,

    I need to handle a cell that automatically refreshes with the current time as value. Like every 30 seconds.

    The best solution I could get is to include a Timer with my add-in, and at each tick, I use the method "Calculate" on a cell that has value "=NOW()".

    This solution is working well, but the problem is that everytime the timer refreshes the cell, the focus goes back to the excel spredsheet.

    Hence, if I am writing a mail, the focus goes back to excel every 30 seconds.

    The only thing I could think about for this if to use the following code with the timer:

     

    Application.EnableEvents = false;
    TimeRange.Calculate();
    Application.EnableEvents = true;
    

     

    However, I think it is not really elegant and if I reduce the interval between ticks to 5 seconds, then I can't press any button on the ribbon without having incredible delays.

    Is there a better way to handle this problem?

    Is the "Calculate()" method the right solution, or should I simply write the current time in the cell manually from the timer event handler?

     

    Thanks,

    Jeremie

    Monday, May 10, 2010 5:54 AM

Answers

  • public static void tryTimer()
        {
          System.Timers.Timer atimer = new System.Timers.Timer(500);
          atimer.Elapsed += new System.Timers.ElapsedEventHandler(atimer_Elapsed);
          atimer.Start();
          Console.ReadLine();
        }
    
        static void atimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
          updateTime();
        }
    
        public static void updateTime()
        {
          try
          {
            XL.Worksheet asht = app.ActiveSheet as XL.Worksheet;
            asht.Range("a1").Value2 = System.DateTime.Now;
          }
          catch (Exception ex)
          {
          }
        }

    Hi, this is a bit of throw away code.

    There are lots of blogs on why not to be doing this. See eg Andrew Whitechapel's blogs, but I'm not clear on what exactly you are doing it for. I'm be loath to write this in an live client app.

    • Marked as answer by Bessie Zhao Monday, May 17, 2010 11:11 AM
    Tuesday, May 11, 2010 2:16 PM

All replies

  • Hello Jeremie,

    Which kind of this project you are working with? Excel Automation, Excel Add-in, or Excel Workbook? Here is a resource from Andrew Poulsom which could help you how to refresh this range by using VBA: http://www.mrexcel.com/forum/showthread.php?t=31483.

    If you have any concern for this, please feel free to follow up.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, May 11, 2010 10:31 AM
  • public static void tryTimer()
        {
          System.Timers.Timer atimer = new System.Timers.Timer(500);
          atimer.Elapsed += new System.Timers.ElapsedEventHandler(atimer_Elapsed);
          atimer.Start();
          Console.ReadLine();
        }
    
        static void atimer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
          updateTime();
        }
    
        public static void updateTime()
        {
          try
          {
            XL.Worksheet asht = app.ActiveSheet as XL.Worksheet;
            asht.Range("a1").Value2 = System.DateTime.Now;
          }
          catch (Exception ex)
          {
          }
        }

    Hi, this is a bit of throw away code.

    There are lots of blogs on why not to be doing this. See eg Andrew Whitechapel's blogs, but I'm not clear on what exactly you are doing it for. I'm be loath to write this in an live client app.

    • Marked as answer by Bessie Zhao Monday, May 17, 2010 11:11 AM
    Tuesday, May 11, 2010 2:16 PM
  • Hi guys,

    So, to answer to your questions.

    I am working on an Excel Add-In (Application level).

    The idea is that I would like to update the value of a cell every x seconds.

    Basically, I would like to have a cell with the current time, refreshed every 10 seconds.

    @Incre-d : if I use your solution, everytime the timer ticks, the focus goes back to Excel. I mean that if you are working on outlook, your cursor will be taken away from outlook and sent to excel. Hence, you cannot work on a different application while the timer is strated.

     

    Do you also experience this problem?

    Wednesday, May 12, 2010 3:03 AM
  • Hello again Jeremie,

    To achieve this, we also could also use Application.OnTime and Calculate. If you put a formula like "=NOW()" to a cell, we could use SheetChange event to listen this action. Then in this event handler, we could add a VBA component by using VBComponents.Add. For this, please refer to this thread: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/5b2f5387-930a-4932-bae0-f42ddbd9cbc6/. And you need to add a reference to Microsoft.Vbe.Interop on .NET tab. Code like this,

    using VBIDE=Microsoft.Vbe.Interop;

            void Application_SheetChange(object Sh, Excel.Range Target)
            {
                try
                {
                    if (Target.Formula == "=NOW()")
                    {
                        Excel.Workbook Wb = this.Application.ActiveWorkbook;
                        VBIDE.VBComponent oModule = null;
                        oModule = Wb.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
                        string sCode =
                            "Sub UpDateClock()\r\n" +
                            "Worksheets(\"Sheet1\").Range(\"A1\").Calculate\r\n" +
                            "Application.OnTime Now + TimeValue(\"00:00:10\"), \"UpdateClock\"\r\n" +
                            "End Sub";
                        oModule.CodeModule.AddFromString(sCode);
                        DateTime dt = DateTime.Now.AddSeconds(15);
                        Debug.Print(dt.ToString());
                        this.Application.OnTime(DateTime.Now.AddSeconds(10), "UpDateClock");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
     

     Please test this code block in your side. If this does not help you, please feel free to follow up.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, May 12, 2010 6:11 AM
  • I don't experience the same problem. I ran the code for some time to verify this, sent emails, worked on other development work, and am responding with this as the workbook is updating the time. My interval is every half a second, and I see no focus issues at all, which I would expect.

     

    Wednesday, May 12, 2010 6:56 AM
  • Hello again Jeremie,

    Do you have any comment for this question? If there is any help needed on this thread, please feel free to follow up.

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, May 17, 2010 1:58 AM
  • Hello,

     

    Sorry I was out a few days.

     

    Yeah basically I used a workaround which consists in creating an RTD server which refreshes the cell every 10 seconds.

     

    The thing that I don't really like with the VBA code is that my Excel Add-In is rather complicated now and uses multiple spreadsheets and workbooks so I thought it would be kind of error-prone to always look where to write the time.

     

    Moreover, the RTD server looked the most elegant way to handle the problem.

     

    I don't know if you see any problem with my reasoning,

     

    Cheers,

     

    Jeremie

    Wednesday, May 19, 2010 6:07 AM