Beyond Excel: VBA and Database Manipulation

Nov 3 2009   6:07PM GMT

Delivering a Finished Product

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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 
'       Get the data
        With ActiveSheet.ListObjects.Add( _
                SourceType:=0, _
                Source:=Array( _
                    "ODBC;" & _
                    "DSN=MS Access Database;" & _
                    "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"), _
            .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
    End If
End Sub
Here is a graphic way of looking at the process.
  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

Basic Process

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: