Delivering a Finished Product

Last post I introduced the wrapper for creating PivotTables and PivotCharts.  This wrapper isolates the changes you must make to add PivotTables and PivotCharts to just one routine.  By isolating these changes, your job is simplified and setting up PivotTables and Charts can easily take less than a minute.  All that’s left to do is to add one line to Macro1 to call the wrapper.  Once you add that line, all your user has to do to is click the “easy” button to retrieve their data, see it in a graph they can easily filter, and see it in a PivotTable that automatically provides “drill down” support.  Here is how Macro1 should look.  (*Note: The red line must be changed to where your Northwind database resides).

Sub Macro1()

    Dim s As String

'   Ask user for input parameters   

    s = Trim( _
             InputBox("Enter State Code:" & vbCr & vbCr & _
                 "'%' is a wildcard.  By itself it will retrieve all states. " & _
                 "'N%' will retrieve all states beginning with 'N'" & vbCr & _
                 "'%Y' will retrieve all states ending in 'Y'", _
                 "State Code Prompt", "%") _
            )
   
'   If OK was pressed then process request
    If s > "" Then
       
'       Clear the spreadsheet 
        Cells.Delete
        Cells.ClearContents
       
'       Get the data
        With ActiveSheet.ListObjects.Add( _
                SourceType:=0, _
                Source:=Array( _
                    "ODBC;" & _
                    "DSN=MS Access Database;" & _
                    "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"), _
                Destination:=Range("$A$5")).QueryTable
           
            .CommandText = Array( _
                "SELECT O.`Order ID`, O.`Customer ID`, O.`Order Date`, " & _
                       "C.`First Name`, O.`Ship State/Province`, " & _
                       "D.Quantity, P.`Product Name` " & vbCr, _
                "FROM   Customers C, Orders O, " & _
                      "`Order Details` D, Products P " & vbCr, _
                "WHERE  O.`Customer ID` = C.ID " & vbCr & _
                "  AND  O.`Order ID`    = D.`Order ID` " & _
                "  AND  D.`Product ID`  = P.ID " & _
                "  AND  C.`State/Province` LIKE '" & s & "'")
            .RowNumbers = False
            .ListObject.DisplayName = "Data_Data"
            .Refresh BackgroundQuery:=False        
        End With
               
'       Name the data range
        With Range("Data_Data")
            Names.Add "Data", _
                Range(.Cells(0, 1), .Cells(.Rows.Count, .Columns.Count))
            Range(Cells(.Row, 3), _
                  Cells(.Row + .Rows.Count, 3)).NumberFormat = "m/d/yy;@"
        End With
       
'       Add a PivotTable and PivotChart
        Pivot_Template
       
    End If
   
End Sub

1. The user clicks the “easy” button which invokes Macro1()
2. Macro1() gets the user input and retrieves data from the database
3. The data is sent to the XL spreadsheet in tab Data
4. Macro1 calls Pvt_Template
5. Pvt_Template sets the PivotTable values and calls Setup_Pivot
6. Setup_Pivot creates the PivotTable in tab pvtTemplate and returns control to Pvt_Template
7. Pvt_Template calls Setup_PivotChart 
8. Setup_PivotChart creates the PivotChart in tab chtTemplate .  Control passes up the stack to Macro1() which ends

Basic Process