Automating Complex Excel Reports

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.

 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.

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”.

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.

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.

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.

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.

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’.

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

dpeabody@quest.com