As a frequent MS Excel user, you probably have some daily routine operations. In this case Excel macros helps record the sequence of the user’s actions as a VBA script. This approach is ideal for automating uncomplicated operations. For more sophisticated tasks, users with programming skills can create a VBA project.
The Automation add-in offers a completely new approach to automation of Excel routines:
- Write commands in a simple Excel table instead of bulky VBA modules
- Automate even complex and multistep operations
- Automate XLTools features: SQL Queries, Export to CSV, Unpivot Table, etc.
- Create your own custom buttons on the toolbar
- For advanced users and developers
You do not have to be a VBA-guru. If some of your business processes in Excel take too much time, our XLTools team will help you automate them!
Add "Automation" to Excel 2016, 2013, 2010, 2007
Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).
Download XLTools Add-in
Learn your way around:
- How to automate operations in Excel without VBA [Download Tutorial]
- Example: how to automate an SQL query against Excel tables
How to automate operations in Excel without VBA [Download Tutorial]
Excel VBA macros often grow into hundreds lines of script, very inconvenient to work with. XLTools Automation add-in allows you to write commands in simple and concise Excel tables. Table display is more informative, visual and easier to edit. You can also assign an automation command to a custom button on the ribbon.
The Automation add-in is a universal tool to automate virtually any command and their sequences:
- Automate SQL Queries against Excel tables: SELECT, GROUP BY, JOIN ON, etc.
- Automatically unpivot a crosstab table to a flat list
- Automatically export an Excel table to a CSV file
- Automatically extract data from other Excel or CSV files
- Automate table filtering, etc.
Simply write a command using the Tutorial > Click ‘Execute Commands’ > Done! It will take just a few seconds to generate the result.
DOWNLOAD TUTORIAL: examples, syntax, code and line-by-line transcripts (xlsx, 246 KB).
Example: how to automate an SQL query against Excel tables
Let’s take an example of a retail store. Suppose you have to prepare a quarterly sales report. One option is to use the SQL Queries add-in and run a query against your Excel data. But if you have to prepare similar reports regularly, you can automate this SQL query.
1. Prepare you primary data for SQL query. Format it as a named table, otherwise SQL query will not be able to process data:
- Select the range ‘Historical Product Price List and Sales’.
- On the ‘Home’ tab click ‘Format as Table’ > Apply a table style.
- On the ‘Design’ tab, type the table name ‘Sales2014’.
2. Add a new worksheet, e.g. ‘AutoCommands’, and write the SQL automation command:
- XLTools.SQLSelect – type the name of the command exactly; place it in two merged cells.
SQLQuery – write the query as you normally would. Please note, the add-in uses SQLite syntax.
Tip: instead of manually typing the query, use the intuitive SQL Queries editor and then copy-paste the script to automation table.
ApplyTableName – type the name for the resulting table.
The query result is automatically generated in table format. If necessary, you can address this table in further queries.
- OutputTo – specify where to place the result.
Please note: in order for Automation or SQL add-ins to identify references, do not use spaces in the names of worksheets, workbooks, or tables.
3. Execute the SQL automation command:
- Select the commands table > Click the ‘Execute Commands’ button on XLTools tab.
Done, the result is generated immediately.
In this example, the SQL query has extracted sales data for 3rd Quarter 2014.
4. Add your own buttons to Excel ribbon.
In each Excel workbook you can create up to 3 custom buttons assigned to your own automation commands:
- Click ‘Add Custom buttons’ on XLTools tab.
- Name the button, e.g. ‘Generate Q Reports’ > Validate the range of the commands table.
- Click ‘Save’ > The custom button will appear on XLTools tab.
Now, you can easily create a quarterly report in just one button click – literally!
Any questions or suggestions? Please leave your comment below.