Creating a Pivot Table Report Using the Pivot TableWizard Method

1. Add a new module to a VBA project and enter the PivotTable_Exter-nall procedure shown below. This procedure is also available on the companion CD-ROM in the ProgramPivots.xls file.

2. Run the procedure to generate the PivotTable.

Sub PivotTable_External1() Dim strConn As String Dim strQuery_1 As String Dim strQuery_2 As String Dim myArray As Variant Dim destRange As Range Dim strPivot As String strConn = "Driver={Microsoft Access Driver (*.mdb)};" & _

"DBQ=" & "C:\Program Files\Microsoft Office\Office\" & _


strQuery_1 = "SELECT Customers.CustomerID, Customers.CompanyName, " & _ "Orders.OrderDate, Products.ProductName, Sum([Order " & _ "Details].[UnitPrice]*[QUantity]*(1-[Discojnt])) AS Total " &_ "FROM Products INNER JOIN ((Customers INNER JOIN Orders " & _ "ON Customers.CustomerID = "

strQuery_2 = "Orders.CustomerID) INNER JOIN [Order Details] " &_ "ON Orders.OrderID = [Order Details].OrderID) ON " & _ "Products.ProductID = [Order Details].ProductID " & _ "GROUP BY Customers.CustomerID, Customers.CompanyName, " & _ "Orders.OrderDate, Products.ProductName;"

myArray = Array(strConn, strQuery_1, strQuery_2) Worksheets.Add

Set destRange = ActiveSheet.Range("B5") strPivot = "PivotFromAccess"

ActiveSheet.PivotTableWizard _

SourceType:=xlExternal, _ SourceData:=myArray, _ TableDestination:=destRange, _ TableName:=strPivot, _ SaveData:=False, _ BackgroundQuery:=False

' Close the Pivot Table Field list that appears automatically ActiveWorkbook.ShowPivotTableFieldList = False

' Add fields to the PivotTable With ActiveSheet.PivotTables(strPivot)

.PivotFields("ProductName").Orientation = xlRowField .PivotFields("CompanyName").Orientation = xlRowField With .PivotFields("Total")

.Orientation = xlDataField .Function = xlSum .NumberFormat = "$#,##0.00" End With

.PivotFields("CustomerID").Orientation = xlPageField .PivotFields("OrderDate").Orientation = xlPageField End With

' Autofit columns so all headings are visible ActiveSheet.UsedRange.Columns.AutoFit

End Sub

When you use the PivotTableWizard method of the Worksheet object to create a PivotTable report from an external data source, you need to specify (at a minimum) the following arguments:

SourceType Use the xlExternal constant to indicate that the data for the PivotTable comes from an external data source.

SourceData Specify an array containing two or more elements. The first element of the array must be a connection string to the database; the second argument is the SQL statement for querying an external database. If the SQL statement is longer than 255 characters, break it up into several strings and pass each string as a separate element of the array. In the PivotTable_External1 procedure, the SQL statement necessary for obtaining the required data from an external database is longer than 255 characters; therefore, the SQL string is broken into two strings: strQuery_1 and strQuery_2. Next, the connection string and the SQL statement are placed in an array like this:

myArray = Array(strConn, strQuery_1, strQuery_2) myArray is then used as the SourceData argument of the PivotTableWizard method.

TableDestination Specify a worksheet range where the PivotTable should be placed.

TableName Specify the name of the PivotTable that you want to create.

In addition to the above arguments, the example procedure uses the SaveData and BackgroundQuery arguments. The SaveData argument tells Visual Basic whether to save the PivotTable when the workbook file is saved. By setting this argument to False, the PivotTable will not be saved. This setting allows you to save space on disk. Setting the BackgroundQuery argument to False tells Visual Basic to refrain from executing other operations in Excel in the background until the query is complete.

After creating a PivotTable, the procedure specifies where the fields returned by the SQL statement should be placed in the PivotTable layout. The resulting PivotTable report is illustrated in Figure A-6.

S Microsoft Excel - ProgiamPivols.nls

Ü] fils Ëdt ina&L Fgirtiat lools Window c ess #B.v x®. e - | " -r


0 0

Post a comment