I am wanting to use SQL to automate some of our Excel dashboards, before a longer term solution is in place. Most of these currently involve opening the spreadsheet pressing a button which runs a macro, save the workbook then close.
I can use an ActiveX script and schedule this using the SQL Agent, and this normally works fine. The trouble is if something goes wrong with the spreadsheet I need it to not give a debug popup (as the SQL agent can not interact with this), but to close down the spreadsheet. Currently the spreadsheet is left open and as the session is from the SQL agent the server needs rebooting to release the lock. I'm using SQL 2000 and Windows 2000 Server.
I've tried to schedule a Kill program to close Excel at a later time, but as this is in a separate session fails to kill Excel in the original session.