Toad World® Forums

Using Excel Macro in an automation script


#1

First, my apologies for the long question. I’m a bit frustrated.

The Environment: TOAD for SQL Server via an RDP server. Scripts work great and the scheduler runs like a top.

The Issue: I currently have several reports that run via the scheduler. Once they run I still have to come in and format them. I looked through the tutorials and found the one that describes the use of .xlsm macros so I tried that one. Worked well. Had a few ‘logistical’ issues. First, the template has to be in the folder where you export it to. The second issue is that you can’t rename the file (all you can do is append the date/time to it). This is a bad resolution for me. First I ‘stage’ these files for analasys before I upload them to a SharePoint. Second, the RDP Server doesn’t allow access to the network file I stage them at (just the local share I have on the RDP server) which makes it impossible for my backup to get to. Third, the files need to be saved under a different name than the macro template.

What I’d like to do: Have the report run. Have the file formatted. Then save the finished file to the network share that my team has access to.

Yes - it currently will send the file to the network share. xp_exec has been disabled.


#2

Aaron,

YOU Said

• The Issue: I currently have several reports that run via the scheduler. Once they run I still have to come in and format them.

• I looked through the tutorials and found the one that describes the use of .xlsm macros so I tried that one. Worked well. Had a few ‘logistical’ issues.

• First, the template has to be in the folder where you export it to.

• The second issue is that you can’t rename the file (all you can do is append the date/time to it). This is a bad resolution for me.

o First I ‘stage’ these files for analasys before I upload them to a SharePoint.

o Second, the RDP Server doesn’t allow access to the network file I stage them at (just the local share I have on the RDP server) which makes it impossible for my backup to get to.

o Third, the files need to be saved under a different name than the macro template.

‘****************************’

Having formatted your issues, I believe what you want is an excel XLSM file that will:

• Run on open

• Execute a script against a SQL server database, placing the result in a worksheet

• Format the result according to so pre described process or formatting quideline

• Save the worksheet as a separate XLSX as a dated file on a shared folder that is part of Sharepoint.

The good news all of this can be done within the excel macro, for I have done the same.

What is confusing is that the word “Automation”… What exactly do you mean here? Hopefully, it is a generic term like a task on the server and NOT with SQL Server – Automation or a SQL Agent Job.

The adds a layer of complication that may not be needed.

Let me see a requirement document (details) as to what you need here.

Hank Freeman