I can send a report to a Microsoft Excel linked query with pivot table but I would like to know how I can automate that so that the data is refreshed.
Click on the Automate button and
1. Add a "Database Connection" to the Toad Automation Script and configure the database connection as necessary
2. Add a "Select to Excel" icon inside the Database Connection image and include the following:
a. The Microsoft Excel file that you want to update
b. The SQL query saved as a file (*.sql)
3. Click on Schedule and make sure you input your network password by clicking on the "Set password" button in the Task tab.
4. Configure the "Schedule" and "Settings" tabs as appropriate.