|< Day Day Up >|
So far you've seen how to pull in a recordset and how to do some formatting in Excel. The first couple of chapters show many examples of how to pull in recordsets because pulling is the primary method for bringing in data.
This example brings formatting and recordset pulling together. There is a query in the Northwind database that is called [Sales by Category], which is in brackets because there are spaces in the name. That data is not updated daily, but assume that you have a query named that on your system that is updated daily (or monthly, weekly, etc.). You also have a user who wants to see an Excel report that shows that data and produces subtotals by category. I will show you several methods to give the user the desired report, and you can pick the one that suits you best.
3.5.1. Getting to the Data
The first choice you have to make is whether to use ADO or DAO. Since Access will be my only source of data, I will use DAO. The next option is to decide where you want to put the data. I am going to default to the active worksheet starting in cell A4. This will give you room to put a title on the report. Once that is done, determine what you want to do with the data. The program offers two options: using the Subtotal feature in Excel or using a PivotTable. You could also use an input box and have the user type in a number, etc. I will use a message box to ask the user whether she wants a PivotTable. If she clicks Yes, it pulls the data and puts in a pivot table. If she clicks No, it pulls the data and uses Subtotals. If she presses cancel, it stops execution. This example also uses a button placed on the worksheet to allow you to run the code.
Your first step is to go into Excel and turn on the Visual Basic toolbar by going to View Toolbars and checking Visual Basic. On the Visual Basic toolbar, click on the button that says Control Toolbox when you hover over it to bring up a box with the standard controls that you can place on your Excel Worksheets. We want to use the Command button. When you click the Command button box, your mouse cursor changes to a plus sign. Go to the top of your worksheet and click and drag a box to a size that you want.
Next, right-click on the Command button and select Properties. There are several properties to change so that this worksheet will look and print the way you want it to. First, change the PrintObject property to False so that the button will not show when you print the report. Next, change the Caption property to Pull Data or something similar. You can also change the system name that VBA uses to refer to the Command button to something more meaningful, so let's call it DataButton. Change the name on the box called Name (the first box on the alphabetical list or under Miscellaneous in the categorized list). You can also change the font, color, etc., but it is not necessary for this example. Once you have finished modifying the button, close the Properties box, right-click on the button again, and select View Code. When you are in the VBA screen, go to Tools References and select a reference to Microsoft Data Access Objects. I am using Version 3.6. Put the code in Example 3-5 in for the DataButton_Click Event.
Example 3-5. Summarize data with a PivotTable or SubTotal
Private Sub DataButton_Click( ) Dim wrk As DAO.Workspace Dim dbconn As DAO.Database Dim rs As DAO.Recordset Dim fld As DAO.Field Dim msgoption As Long Dim x As Integer Dim xlws As Excel.Worksheet Dim xlws2 As Excel.Worksheet Dim xlrng As Excel.Range Set xlws = ActiveSheet Set wrk = DAO.CreateWorkspace("myworkspace", "admin", "") Set dbconn = wrk.OpenDatabase("C:\Program Files\Microsoft Office\OFFICE11\" & _ "SAMPLES\Northwind.mdb") Set rs = dbconn.OpenRecordset("Select * from [Sales by Category]") msgoption = MsgBox("Do you want a PivotTable?", vbYesNoCancel, _ "Report Type") Select Case msgoption Case vbYes Set xlrng = xlws.Cells(4, 1) On Error Resume Next xlrng.RemoveSubtotal x = 1 For Each fld In rs.Fields xlws.Cells(4, x).Value = fld.Name x = x + 1 Next Set xlrng = xlws.Cells(5, 1) xlrng.CopyFromRecordset rs xlws.Columns.AutoFit Set xlrng = xlws.Columns("D:D") xlrng.NumberFormat = "$#,##0.00" Set xlrng = xlws.Range(xlws.Cells(4, 1), _ xlws.Cells(rs.RecordCount + 4, rs.Fields.Count)) Set xlws2 = ActiveWorkbook.Sheets.Add xlws2.Name = "PivotTable" xlws2.PivotTableWizard xlDatabase, xlrng, xlws2.Cells(3,1),"SalesbyCategory", _ False, True, True, True, False, , True, True, , , True xlws2.Cells(3, 1).Select xlws2.PivotTables("SalesbyCategory").AddFields RowFields:="ProductName", _ ColumnFields:="CategoryName" With xlws2.PivotTables("SalesbyCategory").PivotFields("ProductSales") .Orientation = xlDataField .NumberFormat = "$#,##0.00" End With ActiveWorkbook.ShowPivotTableFieldList = False Set xlws2 = Nothing Case vbNo Set xlrng = xlws.Cells(4, 1) On Error Resume Next xlrng.RemoveSubtotal x = 1 For Each fld In rs.Fields xlws.Cells(4, x).Value = fld.Name x = x + 1 Next Set xlrng = xlws.Cells(5, 1) xlrng.CopyFromRecordset rs xlws.Columns.AutoFit Set xlrng = xlws.Columns("D:D") xlrng.NumberFormat = "$#,##0.00" Set xlrng = xlws.Range(xlws.Cells(4, 1), _ xlws.Cells(rs.RecordCount + 4, rs.Fields.Count)) xlrng.Subtotal 2, xlSum, 4, True, False, xlSummaryAbove xlws.Outline.ShowLevels 2 Case vbCancel GoTo ExitStuff End Select ExitStuff: Set xlws = Nothing Set xlws2 = Nothing Set xlrng = Nothing rs.Close Set fld = Nothing Set rs = Nothing dbconn.Close Set dbconn = Nothing Set wrk = Nothing End Sub
There is a lot going on in this procedure. First, the code sets up the DAO connection. This example uses the Northwind database and, provided you installed it in the default location, this path should work if you are using Office 2003. If you are using another version of Office, the path will be different. If the given path does not work, search your hard drive to find the correct path. As an aside, I will mention that I find the Select Case...End Select statement much easier to read than multiple If...Then statements. The Select Case statement allows you to write code that will be executed based on the value of your variable.
In the example above, you store the result of the message box in a variable called msgoption. The message box returns a value that is most easily deciphered by using the constants provided by Excel. The only options we gave were Yes, No, and Cancel. Excel VBA recognizes these as vbYes, vbNo, and vbCancel.
If the user clicks Yes, saying that she wants a pivot table, the code between Case vbYes and Case vbNo runs. The first few lines of both are the same. Remove subtotals if they exist. I put a line that says On Error Resume Next above this line; it will be in effect for each line after that. I suggest putting this in as the very last thing when you are writing code. This way, you know where your errors are occurring. As a general rule, I like to see all errors while I am writing and testing code. Then, when I put the code into production, I like to use On Error statements to try to avoid having the end user see error messages.
In addition to this command to add error-checking to the Remove Subtotals command, it is useful to have the On Error Resume Next line in for the occasion when someone runs the report twice and asks for a PivotTable both times. When this happens, Excel generates an error message that the sheet named "PivotTable" already exists. By having the code bypass that request, Excel simply skips the step of renaming the sheet, and it stays the default sheet. Returning our focus to the data, after the subtotals are removed (or the step is skipped, in the case where there are no subtotals to remove), you need to add the titles of the columns to the sheet by cycling through the Fields collection of the recordset. Use the CopyFromRecordset method of the range object to pull in the data from the recordset. With the data in place, the procedure formats the data in column D using a dollar sign, commas, and two decimal places. Those steps are the same for both vbYes and vbNo. In vbYes, the next step is to make the PivotTable.
There are two general methods for creating a PivotTable. The first method uses the PivotCaches method of the Workbook object. While I find the PivotCaches method to be less intuitive, it is the method that you see if you record a macro of making a PivotTable. The second method, used in the code above, is the PivotTableWizard method of the Worksheet object. Prior to this method being called, the xlrng Range Object is set up to reference the data worksheet, and the Cells object is used so that the data can be referred to by rows and columns. You could also use R1C1 notation, but this method is a little cleaner to read since we are pulling the values from rs.Fields.Count, etc. It is important to set up this Range first because you will build the PivotTable on a new worksheet, and the data resides on another sheet; by setting the range reference first, it becomes a simple matter of using that variable for the data range. The other steps are putting on the row, column, and data fields, as well as formatting the data to be like the data on the original worksheet.
For the vbNo option, go through the same steps as vbYes up to the point where column D is formatted. The Subtotal method is very easy to use. First, the Range object needs to be set up to refer to the range. Next, the Subtotal method of the range object is called by using Range.Subtotal VBA helps you write the rest as you type. The first argument asks which column you want to group on. In Example 3-5, column 2 is selected instead of column 1 because it has the actual names. The consolidation function being used is xlSum. You can select from 12 different functions, including Average (xlAverage), Count (xlCount), etc. If you are not sure which one you want to use, you can highlight Subtotal with your mouse and press F1. Microsoft Help describes each function.
The next argument asks what column(s) you want to apply the function to. In this case we are using only one, so we can simply write 4. If there were multiple columns, say you wanted 4, 5, and 6, you would write Array(4,5,6). The next two arguments want to know whether it should replace the subtotals and whether you want page breaks between groups. The final argument asks if you want the totals to be above or below the group. In Figure 3-3, I have selected above the group. If you select xlSummaryBelow, the grand total appears at the bottom, and all of the details above the subtotals. This is really a user preference. Thinking back to the original reason for this report, the user wanted a summary of the data. It is unlikely that she wants to see all the details by default, but she probably wants to have the option to see the details if she chooses to. Assuming that, the ShowLevel method of the Outline property of the Worksheet object is being used to set the opening level to 2. In this case, 1 is the grand total, 2 is the total by category, and 3 is the detail. If the user wants to expand any subtotal, Excel places plus signs to the left of the row. Click on the plus sign to expand the subtotal. Also, press 1, 2, or 3 at the top left of the worksheet to get to the subtotal level that you want.
The vbCancel line simply sends it to the ExitStuff marker. In this case, you could have placed nothing in that line. However, since in many cases you will have code running after the Select Case statement, I added it. At the end of the procedure all of the objects are closed and/or set to nothing.
|< Day Day Up >|