locked
Excel data auto refresh question RRS feed

  • Question

  • Hi,

    I have an excel file connected to a data source via ODBC that is set to refresh every 30 mins. The file resides on Windows 2008 server.

    I've noticed the I see changes when I refresh it manually but it doesn't seem to be refreshing the data automatically.

    Does the file need additional access for this?

    Can it be created under any user account or does it have to be a local admin?

    Is there any other setup required other than setting the connection to auto refresh?

    Thanks.

    Monday, February 18, 2013 7:40 PM

Answers

  • Record a macro where you refresh the data, and use code like this, where Macro1 is the name of your recorded macro. Run StartIt to begin your refresh cycle, and StopIt to be able to close the application without Excel re-opening it to run the scheduld macro

    Public NextTime As Date

    Sub StartIt()
      NextTime = Now + TimeValue("00:30:00")
      Macro1
      Application.OnTime NextTime, "StartIt"
    End Sub

    Sub StopIt()
      Application.OnTime NextTime, "StartIt", Schedule:=False
    End Sub

    • Proposed as answer by ryguy72 Saturday, February 23, 2013 9:17 PM
    • Marked as answer by Quist Zhang Thursday, February 28, 2013 12:16 PM
    Tuesday, February 19, 2013 2:36 PM

All replies

  • Hi Ravi,

    Thanks for posting in the MSDN Forum.

    I would recommend you invoke a timer in add-in or VBA to refresh the data for your document.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 19, 2013 4:51 AM
  • Tom,

    Thanks for your reply. Could you please give me some pointers?

    Thanks.

    Tuesday, February 19, 2013 2:20 PM
  • Record a macro where you refresh the data, and use code like this, where Macro1 is the name of your recorded macro. Run StartIt to begin your refresh cycle, and StopIt to be able to close the application without Excel re-opening it to run the scheduld macro

    Public NextTime As Date

    Sub StartIt()
      NextTime = Now + TimeValue("00:30:00")
      Macro1
      Application.OnTime NextTime, "StartIt"
    End Sub

    Sub StopIt()
      Application.OnTime NextTime, "StartIt", Schedule:=False
    End Sub

    • Proposed as answer by ryguy72 Saturday, February 23, 2013 9:17 PM
    • Marked as answer by Quist Zhang Thursday, February 28, 2013 12:16 PM
    Tuesday, February 19, 2013 2:36 PM