7.5. 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 dim adoconn dim adors dim x dim y set adoconn = createobject("ADODB.Connection") adoconn.connectionstring = "driver={SQL Server};" & _    "server=MJS_HOME;uid=Michael Schmalz;trusted_connection=True;database=Northwind" adoconn.open set adors = createobject("ADODB.Recordset") adors.open "Select [Products by Category].* from [Products by Category]", adoconn set xlapp = createobject("Excel.Application") set xlwb = xlapp.workbooks.add set xlws = xlwb.activesheet xlapp.visible = true set xlrng = xlws.cells(2,1) y = adors.fields.count -1 for x = 0 to y    xlws.cells(x+1).value = adors.fields(x).name next xlrng.copyfromrecordset adors xlws.columns.autofit set xlrng = xlws.range(xlws.cells(2,1),xlapp.activecell.specialcells(11)) xlrng.Sort xlws.Range("A2"), 1 set xlrng = xlws.range(xlws.cells(1,1),xlapp.activecell.specialcells(11)) xlrng.subtotal 1, -4157, 4, -1, 0, -1 set xlrng = xlws.columns("D:D") xlrng.style = "Comma" xlrng.numberformat = "0" xlws.Outline.ShowLevels 2 xlwb.saveas ("c:\sampleDTSexcelworkbook.xls") xlwb.close xlapp.quit Main = DTSTaskExecResult_Success End Function

There are a couple of things that make this code tricky to write. First, because we are using VBScript instead of VBA or VB, we do not have access to the object properties, which means you will not get help as you write the code. Second, you cannot use the constants that are available when you use VBA or VB. For example, in the part of the line that says xlapp.activecell.specialcells(11), the 11 refers to a constant called Excel.xlCellTypeLastCell. If you do not know that xlCellTypeLastCell equals 11, go into the Immediate Window in Excel, type in ? Excel.xlCellTypeLastCell, and press Enter. Excel tells you what the constant is. You can do this for constants related to any object by typing in the object name, the period, and the name of the constant.

This example is a little different from anything you have read in this book so far. First, notice that the variables are declared with no type. Each of them has the type Variant. Since we do not have access to the objects' properties, you have to use the CreateObject method to create the ADO Connection and Excel Application (this is called late binding ). The query is called "Products by Category," and for unknown reasons, the Northwind database has spaces in the names of the views. I don't recommend this when you develop databases because you then have to put the name of the view or table in brackets. So, you will notice the brackets when you see the line opening the recordset in the code.

Now that the recordset is open, you see the lines of code that create the Excel application, make it visible, and create the other necessary Excel objects. Also, notice that instead of going through the field names using For...Each and going through the ADO fields, I use For x = 0 to y, where y is set to the number of fields in the query minus 1. You could use this method in other places, but I generally prefer to cycle through the fields. The one tricky thing about this method is that Field(0) goes in the first column. This is why you see xlws.cells(x+1).value = adors.fields(x).name, where in the code in Example 7-4, we started with x = 1 and ended with x = x + 1 after each field. This section of code puts all of the field names in the top row.

The next section uses the CopyFromRecordset method to put the data into the Excel worksheet and uses AutoFit to resize the columns. The code sorts by the Product Category, which is the first column, and does a subtotal on the fourth column, the Quantity. In the subtotal method, the first argument is the Group By field. In this case, we want to group by the first column, so we enter 1. The next argument asks which function to use. Since I want a sum, I find out the constant for xlSum by going into the Immediate Window in Excel and typing in ? Excel.xlSum--the result is -4157. The next argument is the column(s) that you want totaled. In this case, we are totaling only one column (the fourth), so we type in 4. The next argument asks whether to replace the current subtotals. Use the value -1, which is trUE in VBA. The next argument asks whether you want page breaks. In this case, a 0 is FALSE, meaning that you do not want page breaks. The final argument asks whether you want the summary to be above or below the numbers. The choices are xlSummaryAbove or xlSummaryBelow. I chose xlSummaryBelow, which is 1; xlSummaryAbove is 0.

After this, the code sets the number format to have commas with no decimal places. Then, since this report shows subtotals, I use the ShowLevels method of the Outline property of the Worksheet the Outline property refers to the Outline object on the worksheet. After the first level, the default view, the second level is the level category name, which you can see by going into the Excel worksheet created by this DTS package. Click on 1, 2, or 3 on the upper-left section of the worksheet (below the menus and toolbars) to change the level. Figure 7-3 shows what the resulting workbook looks like.

Figure 7-3. The Excel workbook created by the DTS package

For easier Excel reporting (subtotals, sorting, pivot tables, etc.), if you report from SQL Server, look into having the reports done on a schedule with DTS. Once you have all of this information typed in and have saved the package, you will see a green arrow pointing to the right in the toolbar that says Execute when you hover over it. Press that button to create your Excel workbook.

To run this code on a schedule, right-click on the package in Enterprise Manager and select Schedule Package. You can set it up to run on the schedule that you want at the time that you want. If you have a nightly process that is done at 2 a.m. and people come into work at 7 a.m., you can set your packages to run at 5 a.m. and save them into a public folder so that they are available first thing, or you can add an Email task and have the file emailed to the appropriate people. If you are interested in going into depth into these types of automation, it's well worth getting a book specifically on SQL Server.