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



Now let’s record the macro. Select an Excel cell on the same sheet outside the database query range—such as H10. (If you fail to do this, and your cursor happens to be inside the data range, the macro won’t 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. Don’t worry if it doesn’t; there are others ways, which we’ll 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 it’s process) and on the Stop button on the Stop Recording toolbar. If the toolbar doesn’t 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 we’ve recorded a macro, we need to attach it to a button in the spreadsheet. Though any type of button will work, we’ll add one from Excel’s Forms toolbar. Click on View, Toolbars, Forms. Left-click on the button (it’s 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

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