You can add advanced formatting to Excel reports by using Excel macros. When you export data using the Export wizard, Toad allows you to execute the macros in an Excel report during the export process. In addition, you can automate the entire process using a Toad Automation script.
In this example, you will create an Automation script that exports data to an Excel file and also executes the macros in the Excel file during the export process.
Watch a video version of this tutorial: Automate Excel Macro-Enabled Reports
You must create the Excel file that contains a macro prior to creating the Automation script. You can use an existing file or use the following steps to create a new one.
You must also enable macros in the Excel file. To do this, go to Microsoft Office button | Excel Options | Trust Center | Trust Center Settings | Macro Settings and select Enable all macros. Also, select Trust access to the VBA project object model.
Note: You may want to review your company’s policy on the use of macros before proceeding.
Now add a few macros to your macro-enabled Excel workbook. For demonstration purposes, you can use the following macros. One macro clears the contents of Sheet1 and the other macro changes the color of alternate rows in Sheet1.
Dim r As Range
Dim row As Integer
Set r = Sheet1.UsedRange
For row = 1 To r.Rows.Count
If row Mod 2 = 1 Then
r.Rows(row).Interior.ColorIndex = 37
In the Export Wizard activity's Activity Input tab, click to the right of the Export template text box.
The Execute Macro page displays. This page shows the list of macros in your macro-enable Excel file. This page allows you to select a macro or macros to execute before the data is exported and also after the data is exported. In the Before export list, select the ClearSheet1 macro and click the right arrow to move it to the execute list. The purpose of this macro is to clear the sheet before the data is exported.
For this example, on the Activity Input tab, select Use Automation Connection and select the same database connection you selected in the Export wizard.
Note: If you deselect this option, Toad will use the database connection in the Export Wizard template (in this case, it is the same connection).
The output of this script is a macro-enabled Excel file with formatted data. The script uses an Export Wizard template which is a stand-alone file that can be used by other scripts.
See Schedule Your Script to learn how to schedule the Automation script.