Integrating Excel and Access

Using DTS to Automate Excel

This section will only be useful for you if you have SQL Server Enterprise Manager. If you don't have Enterprise Manager, ask your database administrator to do this for you if it would be useful for you. First, let's discuss what Data Transformation Services (DTS) is all about. In SQL Server 2000, you can create packages that work with data in SQL Server or other applications, run scripts, execute stored procedures, etc. DTS lets you build export systems that run on a schedule right in SQL Server. If you normally run several queries from SQL Server daily to build reports, you can set them up to run on a schedule and automate Excel right from DTS. You can then save the reports in a common location, or even have DTS email the reports to you when they are done. There are several ways this can be done, but I am going to show you how to use ActiveX and VBScript.

In Enterprise Manager, right-click on Data Transformation Services and select New Package from the drop-down menu. This gives you a new package that, at this point, does nothing. On the package menu, select Task → ActiveX Script Task. This brings up a code window. Type the code from Example 7-5 in the code window.

Example 7-5. SQL Server ActiveX script example to automate Excel

'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Function Main() dim xlapp dim xlwb dim xlws dim xlrng ...