Technical FAQs

Ask a Question

Updating an excel spreadsheet query with a button.

Goals and Symptoms

Follow http://www.powerlogic.com/kbarticle.cfm?myrequest=RESL182688%20AND%20viewing to be able to import data into an excel spreadsheet. In order to have this data update every time you click on a button, follow the resolution below.

Causes and Fixes

This procedure will create a button that you can push in order to automate the update process with a macro.

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 doesnt appear, click on View, Toolbars and External Data.



Now lets record the macro. Select an Excel cell on the same sheet outside the database query rangesuch as H10. (If you fail to do this, and your cursor happens to be inside the data range, the macro wont work properly.) Then turn on the macro recorder by clicking on Tools, Macro and Record New Macro, engaging the Record Macro dialog box.



Select a name (AutomaticUpdate) for the macro and type it in the Macro Name box and click on OK. That should engage the Stop Recording toolbar. Dont worry if it doesnt; there are others ways, which well describe later, to turn off the recording.

Select a cell in the database query range, such as B5, and click on the Refresh button (orange exclamation point) on the External Data toolbar (let it run through its process) and on the Stop button on the Stop Recording toolbar. If the toolbar doesnt pop up, do one of the following: Select either Tools, Macro, Stop Recording or View, Toolbars and Stop Recording. Then click on Stop.

ALMOST DONE
Now that weve recorded a macro, we need to attach it to a button in the spreadsheet. Though any type of button will work, well add one from Excels Forms toolbar. Click on View, Toolbars, Forms. Left-click on the button (its circled) and then release it.




Now move your cursor to where you want to place the button, left-click and hold down the mouse button as you draw the button icon to the size you want and release the mouse button. The Assign Macro dialog box will pop up. Click on the name of the macro you created (in this case AutomaticUpdate) and on OK. You have successfully attached the macro to the button. If you want to change the default label shown on the button, right-click over the button and edit the text label.



Finally, to test the macro, go back to your original database and add some records. Then return to your spreadsheet and click on the new button. If you followed all the steps, the new records, as well as the old ones, will appear in your spreadsheet automatically.

Although it took many setup steps, now you can save loads of time by updating your spreadsheet with new database information with just one click.

 

  • 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: RESL192080 V1.0, Originally authored by KBAdPM on 06/05/2008, Last Edited by KBAdPM on 06/05/2008
Related ranges: ION Enterprise V5.0, ION Enterprise V5.5, ION Enterprise V5.6
Was this helpful?
What can we do to improve the information ?