Formatting Techniques for Data Access from Excel VBA - Formatting Techniques Example (Page 4 of 5 )
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.
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 theDataButton_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 theSelect Case...End Selectstatement much easier to read than multipleIf...Thenstatements. TheSelect Casestatement 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 calledmsgoption. 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 asvbYes,vbNo, andvbCancel.
If the user clicks Yes, saying that she wants a pivot table, the code betweenCase vbYesandCase vbNoruns. The first few lines of both are the same. Remove subtotals if they exist. I put a line that saysOn Error Resume Nextabove 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 useOn Errorstatements 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 theOn Error Resume Nextline 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 theFieldscollection of the recordset. Use theCopyFromRecordset 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 bothvbYesandvbNo. InvbYes, the next step is to make the PivotTable.
There are two general methods for creating a PivotTable. The first method uses thePivotCachesmethod of theWorkbookobject. While I find thePivotCachesmethod 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 thePivotTableWizardmethod of theWorksheetobject. Prior to this method being called, thexlrngRange Object is set up to reference the data worksheet, and theCells 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 fromrs.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 thevbNo option, go through the same steps asvbYesup to the point where column D is formatted. The Subtotal method is very easy to use. First, theRangeobject needs to be set up to refer to the range. Next, theSubtotalmethod of the range object is called by usingRange.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 isxlSum. 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 write4. If there were multiple columns, say you wanted 4, 5, and 6, you would writeArray(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 selectxlSummaryBelow, 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, theShowLevelmethod of theOutlineproperty of theWorksheet 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.
ThevbCancel line simply sends it to theExitStuff marker. In this case, you could have placed nothing in that line. However, since in many cases you will have code running after theSelect Casestatement, I added it. At the end of the procedure all of the objects are closed and/or set to nothing.
Next: Summary >>
More Database Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 9 
