One of my favorite features of Toad Data Point is automating complex excel reports. Most reports consist of several sets of data and formatting to make the data attractive or easily readable for the end-user. In this post I will show how to automate these kinds of reports with a combination of export and excel macros.

My sample Sales report consists of the Top 5 products sold and a pivot chart showing total sales by region. I’ve added some headers with fill color to make it more attractive.

Automating Excel Reports in Toad Data Point: Create complex reports with data, formatting, and charts.

 To automate this report, use a sample report as a template, clear the prior data (preserving the formatting) and export the new data. To clear the data or add any formatting at runtime you will need to write a macro in Excel. In Excel 2007 you will need to enable macros. From the office button open the Excel Option page. Click on the Trust Center listed on the left and then the Trust Center Settings button. Select Macro Settings and turn on the ‘Enable all macros’ option.

Automate Report Using Excel Template and Macros: Clear previous data, export new data, and enable macros in Excel.

While macros can be a potential security risk, they have a lot of useful automation features and have been used successfully for a long time. You may need to consult your corporate policies on the use of macros.

To make a macro that clears the prior data, go to the Excel View tab and open up the macro editor. See the help files for the various uses of macros. Below is a sample of clearing a specific range of cells on the worksheet named “Report” and all of the data on the worksheet named “Data”.

Create Macro to Clear Prior Data: Open Excel's macro editor, clear specific cell ranges on named worksheets.

Now we are ready to write our Toad for Data Analysts automation report. We only need two activities, one for each of the data sets.

Toad Automation Script Settings

The first activity is an Export Wizard activity. In this activity we define where to export the data for the pivot grid. Make sure and point to your *.xlsm file.  In order for macros to be used in an Excel 2007 file you must save as an *.xlsm extension.

Export Wizard activity

When the export wizard is pointed to a file that contains macros it will display all macros available. You can set which macros to use and whether to run them before or after the export. In this example there is only one macro, ‘ClearSheets’, and I am setting it up to be run before exporting the data.

Select Macros for Export: The wizard lists available macros; choose to run them before or after export.

For the second activity choose the ‘Select to File’ activity. Enter your SQL, choose the same file, and export the data to the specific cell and sheet location.

Second Activity: Use 'Select to File' to export SQL results to the specified cell and sheet in the same file.

Run and test your script. You need to add one more very important thing to your excel report. You will need to change a setting so the pivot grid will refresh when opening the file. To do this, right click on the pivot table and in the data tab of the options, choose ‘Refresh data when opening the file’.

Testing Script and Enabling Pivot Refresh

Voila! You now have automated a complex excel report. There is a lot you can do with macros. They are well worth the time to learn. And now with the ability to use them in Toad for Data Analysts automation the sky is the limit (okay maybe your time and patience are the limit but at least you have a lot more choices J)

Sample code can be downloaded (Please visit the site to view this file). To run you must have Toad for Data Analysts 2.7 installed in the default location. Copy the sample files to ‘C:temp’.

Have fun and I’d love to hear any suggestions. Feel free to post on our community site.

Debbie Peabody

About the Author

Debbie Peabody

Debbie Peabody is a Software Development Team Lead. She joined Quest in 1997 coding for Quest’s SQL Tuning product SQLab. She brings many years of experience of development on database tool products and co-designed Toad Data Point.

Notable Replies

  1. says:

    This is a front page article in Toad’s version “Featured Content” but it is poorly out of date. I am unable to save the export file as an ‘.xlms’ file. Is there an updated blog for the newer versions of Toad? I’m looking to automate an export to excel to refresh a pivot table.
    Thank you in advance!

  2. says:

    While that blog is old the steps still work. Using macros and *xlsm files are if you want to add customized code before or after the export. You would need to create the xlsm (excel macro enabled file) in Excel before you used it in the export. Is your pivot table created and refreshed in macro code? if so just add macro to clear data page and then export the data to that page. If you do not want to use a macro see this blog