How to automating Excel from an SQL Server Script?

[Guest]

I have an Excel worksheet that is linked to an SQL stored procedure. I have
coded with VBA this worksheet to Open, Update the data from the SQL stored
procedure it is linked to, then copy this data to another worksheet, format
the columns and data, and close both worksheets.

The goal of this is to open, run, and close this Excel worksheet from
commands stored in an SQL script (view, stored procedure, function) that will
be called to run once a month.

Any ideas how to do this? Or is there a better way to do this? Should this
be posted somewhere else (where)?

Thanks ahead of time for your responses, and ideas!!

 

[Guest]

I have an Excel worksheet that is linked to an SQL stored procedure. I have
coded with VBA this worksheet to Open, Update the data from the SQL stored
procedure it is linked to, then copy this data to another worksheet, format
the columns and data, and close both worksheets.

The goal of this is to open, run, and close this Excel worksheet from
commands stored in an SQL script (view, stored procedure, function) that will
be called to run once a month.

Any ideas how to do this? Or is there a better way to do this? Should this
be posted somewhere else (where)?

Thanks ahead of time for your responses, and ideas!!

You can use sp_OACrate (and simmilar) SQL Server stored procedure to work
with Excel from SQL Server, but I think this is not the easiest way to work
with COM objects (look in SQL Server Books Online for sp_OA* samples and
you'll see what I mean). If you already have VBA code that works, maybe you
can use Scheduled Tasks to run it once a manth from Excel, and avoid using
SQL Server stored procedures at all. (maybe using Workbook.Open event?)

Hope this helps.