Programming Excel with VBA and .NET

Connect to External Data

To connect a pivot table to an external data source in code, set the PivotCache object’s Connection and CommandText properties. The PivotTableWizard method sets those properties automatically from a passed-in array argument. The following code creates a new pivot table from a SQL Server database using PivotTableWizard:

Sub QuickDBPivotTable(  )
    Dim ws As Worksheet, pt As PivotTable
    ' Get the active worksheet.
    Set ws = ActiveSheet
    ' Use pivot table wizard to create table on new worksheet.
    Set pt = ws.PivotTableWizard(xlExternal, _
      Array("SELECT Date, SalesRank, ProductName FROM Amazon"), _
      Worksheets.Add.[a1], "QuickDBPivot", , , , , , , , , , , , _
      "ODBC;DRIVER=SQL Server;SERVER=USERS;" & _
      "UID=;APP=Microsoft Office 2003;WSID=WOMBAT4;" & _
      "DATABASE=Sales;Trusted_Connection=True")
    ' Set layout
    pt.AddFields "Date", "ProductName"
    pt.AddDataField pt.PivotFields("SalesRank"), , xlAverage
End Sub

If you have trouble composing the connection string, create a connection to the database using the Query Wizard; then use the connection string that the wizard generates in the .dqy file as described earlier in this chapter in “Connect to an External Data Source.” Prefix that connection string with ODBC; as shown in the preceding code.

The Trusted_Connection=True element in the connection string tells Excel to use Windows integrated security when connecting to the data source. That approach uses the user’s network identity when connecting to the data source rather than ...