Technical FAQs

Ask a Question

Schedule an update of an excel sheet query to occur at a specific interval.

Goals and Symptoms

Goal:
Having learned to import data into an Excel spreadsheet using the procedure in http://www.powerlogic.com/kbarticle.cfm?myrequest=RESL182688%20AND%20viewing. If you would then like the query to update on a scheduled interval (e.g. every 15min) follow the procedure below.

Causes and Fixes

Automatic Scheduled Update Of Your Excel Sheet Query.

(This procedure starts assuming the query already exists in excel)

    • 1) Put your cursor inside the contents of the database query in your spreadsheet; that should cause the External Data toolbar to pop up.  If it does not pop up you will need to click on View > Toolbars > External Data.


    • 2) Record the macro.  Select an Excel cell on the same sheet outside the database query range (If you fail to do this, and your cursor happens to be inside the data range, the macro won’t work properly).  Turn on the macro recorder by clicking on Tools > Macro > Record New Macro.


    • 3) Select a name for the macro and type it in the Macro Name box and click OK.  That should engage the Stop Recording toolbar.  If it doesn’t click View > Toolbars > Stop Recording.


    • 4) Select a cell in the database query range and click on the Refresh button (Orange Exclamation Point) on the External Data toolbar (let it run through it’s process) and then click on the Stop button on the Stop Recording toolbar.

      5) Now that we’ve recorded a macro we need to edit it to automatically update itself on a scheduled basis.  Type Alt + F11 in the excel window to bring up the macro code.



    • 6) You will now want to add the following lines of code to the macro right beneath the first line (You can change the TimeValue to the update interval you would like, right now it updates every 15min)(“MyMacro” should be the name of the macro and in this case should be change to Macro5):

    dTime = Now + TimeValue("00:15:00")
    Application.OnTime dTime, "MyMacro"

    • 7) Click on the Run button (Green play button) in the Debug toolbar or at the top.

Now your macro should update the spreadsheet at your scheduled interval.

  • Last Revised: May 2008

    Applies To: ION Enterprise 5.6, 5.5, and 5.0

    Original Author: MN

    Public

    All content © 1992-2008 Schneider Electric

Was this helpful?
What can we do to improve the information ?