Toad Data Point Automation Series: Blog #5 – Using Macros in Excel

For more advanced forms of formatting use Excel macros.

Macros are VBA instructions that can be triggered by Toad. By default Excel disables macros. You will need to set up Excel to use macros. When using them the excel extension is *.xlsm. The set up may vary between versions of Excel. Starting with 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. Check the “Trust access to the VBA project object model”.

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 and how to write them.

Below is a sample of clearing several worksheets and coloring every other row of data.

You will need to learn how to build macros. In this example I am simply changing the color of each row. Not the prettiest example but simple for demonstration.

To use macros in TDP, set up the macro in Excel. Then open the Export wizard. Enter your SQL statement. Make sure and point to the macro enabled *.xlsm file. The wizard will recognize that the excel file has macros and display a list of macros. Choose which macros you want to run and when you want to run them, before or after the result set is exported. For example, here a macro is used to clear all of the data. This would be called before the export. After export another macro is called to format the rows.

There are many ways to build simple to complex reports with Excel and Toad Data Point. You are limited only by your imagination and time.