Using Macro-Enabled Excel Files

Using Macro-Enabled Excel Files

For best results when using macro-enabled Excel files in Toad, especially in Automation scripts, review the following important tips and instructions.

Important Tips

General Tips About Macro-Enabled Files

• Always use an existing file. If creating an Export Wizard template to export to Excel and execute macros, you must select an existing macro-enabled Excel file containing the macros you want to execute.
• Enable macros in the file. To successfully create a macro-enabled file, you must enable macros in the Excel file. See Automate Excel® Macro-Enabled Reports for instructions.

Automation Scripts That Use Macro-Enabled Files

• Always use an existing file. When creating an Automation script that uses the Export Wizard activity to export to Excel and execute macros, you must select an existing macro-enable Excel file containing the macros you want to execute.
• When Publishing a script to Intelligence Central. To successfully publish an Automation script that uses a macro-enabled Excel file, the Excel file must be stored in a shared network location.

Prepare Server on Which Scripts Execute

• Install Excel. If an Automation Script running in Intelligence Central executes a macro in an Excel spreadsheet, Microsoft Excel must be installed on the Intelligence Central host computer.
• Configure run account. The account used to run a script that executes macros, must be configured to execute macros in Excel. This applies to scripts running on Intelligence Central or another server. To configure the account, see the following procedure.
• Resolve other server-side issues. If issues arise when executing macro-enabled Excel files using an Automation script on Intelligence Central or another server, see the following procedure to resolve possible server-side Excel issues.

Resolve Possible Server-Side Excel Automation Issues

If an Automation Script scheduled to execute on Toad Intelligence Central or any other server includes an instruction to run a macro in an Excel spreadsheet, the account used to run the script must be configured to execute macros in Excel.

An indication that the run account is not configured properly would be if the script fails to execute macros or the script fails with the following error:

"The user account specified to run this script on a server is not configured to execute macros in Excel."

To resolve this issue, ensure the account used to run the Automation script on the server is configured properly. See the following procedure.

Step 1: Ensure the Automation script runs under a domain account (not Local System)

For scripts running on Intelligence Central

1. At the time the script is published, select a Windows user account to run the script. Do not select the "Default user" account. See Publish Automation Scripts to Intelligence Central for more information about selecting the account under which a script runs.
2. If you need to identify the account under which a script is currently running in Intelligence Central, open the Script Manager in Toad. Select the script in the Automation Script List. The account is displayed in the Run As column. See Manage Automation Scripts in Intelligence Central for more information about using the Script Manager.

For scripts running on a server

• If the script executes on a server using a Windows scheduler task, ensure the script runs under a Windows user account. To identify/edit the run account, open the Windows Task Scheduler, right-click the scheduled script, and then select Properties.

Step 2: Create a Desktop folder

Excel seems to require a “Desktop” sub-folder in the systemprofile folder. Add this folder to the following location.

• For 64-bit (x64), create this folder: C:\Windows\SysWOW64\config\systemprofile\Desktop.
• For 32-bit (x86), create this folder: C:\Windows\System32\config\systemprofile\Desktop.

Step 3. Change DCOM Config settings for Excel

1. Open DCOMCNFG.
   • Click the Start button, enter dcomcnfg.exe in the Search field, and press Enter. (You might be prompted for administrator credentials.)
2. In the Component Services dialog, go to Console Root | Component Services | Computers | My Computer | DCOM Config.
3. Right-click Microsoft Excel Application and select Properties. If Microsoft Excel Application is not displayed, open the Microsoft Management Console (MMC.exe) and perform the following steps to open the Properties dialog.
   1. Click the Start button, enter mmc -32 in the Search field, and press Enter.
   2. In the Console, select File | Add/Remove Snap-in.
   3. Select Component Services and click Add. Click OK.
   4. In the Console, go to Console Root | Component Services | Computers | My Computer | DCOM Config.
   5. Right-click Microsoft Excel Application and select Properties.
4. Select the Identity tab. Then select The interactive user.
5. Select the Security tab.
   1. Under Launch and Activation Permissions, click Customize and Edit. Add the account under which the Automation script is running.
   2. Under Access Permissions, click Customize and Edit. Add the account under which the Automation script is running.
   3. In addition, if necessary add the other accounts running similar scripts on the server.

Other troubleshooting topics

• Troubleshoot Scripts on Intelligence Central
• Troubleshooting Scheduled Automation Scripts

Exporting to Excel

• Export Data with the Export Wizard
• Use Database Automation Activities—Using the Export Wizard activity in Automation