Automation in Excel without macros

Automate routine tasks and add custom buttons

As an advanced and frequent MS Excel user, you probably have some routine daily operations. MS Excel macros can 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. But is there a way automate Excel operations and avoid time consuming VBA scripts?

The Automation add-in offers a completely new approach:

  • Write commands in a simple Excel table instead of bulky VBA modules
  • Automate even complex and multi-step operations
  • Automate XLTools features: SQL Queries, Export to CSV, Unpivot Table, etc.
  • Create own custom buttons on the toolbar
  • For advanced users and developers

You do not have to be a VBA-guru. Any Excel user with basic knowledge of language commands can master the Automation feature.


Add XLTools Automation to Excel 2016-2007

Works with: Microsoft Excel 2016, 2013, 2010, 2007, and desktop version of Office 365.

Download XLTools Add-in


How to automate operations in Excel without VBA

The Automation add-in is a universal tool to automate virtually any command:

  • 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
  • Automate any sequences of commands
  • Create custom buttons in Excel, etc.

Download Tutorial: examples, syntax, code and line-by-line transcripts (xlsx, 246 kB).


How to automate SQL Queries against Excel data

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 feature and run a query against your Excel data. But if you have to prepare this report regularly, you can automate your SQL query.

1. Format your primary data as a named table, otherwise the query will not be able to locate the data:

  • Select the range ‘Historical Product Price List and Sales’.
  • On the ‘Home’ tab click ‘Format as Table’ > Choose table style.
  • On the ‘Design’ tab type the table name ‘Sales2014’.

Format range as table before SQL Queries Automation

2. Add a new worksheet, e.g. ‘AutoCommands’, and create the commands table:

  • XLTools.SQLSelect – type the name of the command exactly; place in two merged cells.
  • SQLQuery – write the query as you normally would.
    Tip: first, test the same query in the SQL Queries editor and then copy-paste the script.
  • ApplyTableName – type the name for the output table.
  • OutputTo – write where to place the output.

Please note: in order for Automation or SQL to identify references, do not use spaces in the names of worksheets, workbooks, or tables. XLTools uses SQLite standard – learn more (external link).

XLTools Automation: Execute SQL Query

  • Select the commands table > Click ‘Execute Commands’ on the XLTools tab.
  • Done, the result is automatically formatted as a table. So you can run further queries against it.

In this example SQL Query has extracted sales data for 3Q2014. Now we can analyze these 3Q data separately, without changing the primary data:

XLTools Automation: SQL Query Output

3. In each Excel workbook you can create up to three custom buttons:

  • Click ‘Edit Custom Actions’ on the XLTools tab.
  • Name the custom button, e.g. ‘Generate Q Reports’ > Validate the range of the commands table.
  • Click ‘Save’ > The custom button will appear on the XLTools tab.
  • If you make any changes to the commands table, the button will adopt these changes.

XLTools Automation: How to add custom buttons to Excel

 

Any questions or suggestions? Contact us or leave your comment below.

 

Leave a Reply

Your email address will not be published. Required fields are marked *