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 wont 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 doesnt 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 its process) and then click on the Stop button on the Stop Recording toolbar.

    5) Now that weve 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

---

Legacy KB System (APS) Data: RESL192086 V1.0, Originally authored by KBAdPM on 06/05/2008, Last Edited by KBAdPM on 06/05/2008
Related ranges: OS & related Third Party PRODUCTS for Power Mangement Systems, ION Enterprise V5.6, ION Enterprise V5.5, ION Enterprise V5.0