|< Day Day Up >|
While you can do all of this reporting without putting it into a table, I have found that by using a table to house report formats, you can make changes without touching the code. This makes it easier to develop reporting applications and allow others to maintain them. How much you put into the report formats is up to you. I suggest having fields available for font parameters, number formats, formulas, and a true/false field to allow you to hide the row. Let's look at the merits of using a table.
Assume that you wrote code that builds an income statement report with about 30 lines and several sums. At the bottom, to come up with net income, add income subtotals together and subtract expense subtotals. Of course there are underlines, double-underlines, and other formatting, as well as formulas. This report works well for a while but after five or six months in production, someone wants to add a few lines to the report to break out some expenses. If you did all of this formatting in code, you would have to edit just about every line that has to do with formatting, formulas, etc.
Now, if you have a table that holds all of the instructions, you would just need to put in the lines that you need, which would hold the formatting that you wanted for those rows. You then might have to update the formulas in the table to account for the number of new rows. I have found that it is much easier to teach someone how to update a formula in R1C1-style notation than it is to teach someone who has never done programming how to update a reporting module. Your choice depends largely on the role that you want to play. Many consultants make a nice living by maintaining their own applications for clients, and there is nothing wrong with writing the report with code alone. However, I would seriously consider putting the reporting formats in a table to make your application easier to modify.
With that said, let's take a look at this method from the top. First, look at the table design. Go into Access and create a new table. The first column is a line number. Having line numbers, even if you don't print them, ensures that your report shows up in the expected order. Next, decide whether to force the user to put in the exact formatting strings that Excel requires, or whether to use descriptive text, and either have it look up the Excel string in a table or add it to your code. Once you have figured that out, determine the number of columns that you need and give them names that are descriptive enough to make it easy for the user. I suggest using a table of lookup values, as this both makes the programming side much easier and is easier for the user. In addition, you can eliminate or add options to the table without having to touch the code. You won't be able to do that for the formula strings, but for anything where you set Excel constants, I suggest having a table of values (see Figure 8-3).
Let's say that in your report you want to allow users to choose whether to place a border in any given row on the report. There are many ways to make these options available to your users. As previously stated, the easiest way is to have a table with the different options and a lookup value to the value of the Excel constant. To accomplish the border on the row, create a table with two columns called tbl_Border. If the user doesn't want a border, she can leave the field blank. See the values in Table 8-2; you can look up these values in the Immediate Window in Excel as you looked up the formulas earlier in this chapter and the Excel constants in Chapter 7.
Now that you have the values for the placement of the border, you need a second table with the values of the types of lines that you want on the border. Call this table tbl_LineTypes. See the values in Table 8-3.
Once you have the values, the code is very simple. Assume you store the constant values in a variables declared as Long, and use Blocation and Bstyle. To find out whether the code needs to run, first to make sure that the user selected a style. There are a number of ways to check, but I suggest setting the value to 0 right after declaring it; if there is no selection, there will be no value put in, and the code will be skipped. Here is the small section of code:
If Blocation <> 0 Then With xlrng.Borders(Blocation) .LineStyle = Bstyle End With End If
You could also add options for line weight, line color, etc., but for now, keep it simple and allow these two options for borders. Also, put in a formula column, a font size, a Yes/No field for bold, and a number format column. The formula and number format will most likely be freeform text and the font size will just take a number. However, you could offer some styles as options for the number format. Table 8-4 defines what your table will look like. Call the table tbl_ReportFormat .
This gives you a good start to see the possibilities. Keep in mind that any other formatting or formula options that you want to set can be done with a table like this.
Now, let's put this to use. The first thing that you need to know is what to link to with the Link_Field. In this case, I use the product number for the rows where I pull data. Look at Figure 8-3 to see what the table looks like filled in.
Figure 8-3. The resulting table to produce a report format using the same data used in previous examples
In the previous examples, we used a query called qry_BaseQuery. I modify it in this example to be qry_BaseQuery_ReportFormat . The only difference is that I added the ProductNum field, since that is what we are using to link to the report format table. In these reports, I want every row to show up even if there are no values I have to be very careful about the order in which I run the queries. First, run qry_BaseQuery_ReportFormat. Next, run qry_ReportStep1 , which takes a parameter for the line of business that we want to report on. The final query uses a left join because we want all rows in the report format table and only the rows that match in the qry_ReportStep1 query. See Example 8-5 for the SQL of qry_BaseQuery_ReportFormat, Example 8-6 for the SQL of qry_ReportStep1, and Example 8-7 for the SQL of qry_ReportStep2.
Example 8-5. qry_BaseQuery_ReportFormat
SELECT tbl_CostCenters.LineOfBusiness1, tbl_Products.ProductNum, tbl_CostCenters.LineOfBusiness2, tbl_CostCenters.CostCenter, tbl_CostCenters.CenterName, tbl_Sales.SaleDate, tbl_Products.ProductCategory, tbl_Products.ProductName, tbl_Sales.Quantity, tbl_Sales.TotalCost FROM tbl_Products INNER JOIN (tbl_CostCenters INNER JOIN tbl_Sales ON tbl_CostCenters.CostCenter = tbl_Sales.CostCenter) ON tbl_Products.ProductNum = tbl_Sales.ProductNum;
Example 8-6. qry_ReportStep1
PARAMETERS StoreType Text ( 255 ); SELECT qry_BaseQuery_ReportFormat.LineOfBusiness2, qry_BaseQuery_ReportFormat.ProductNum, Sum(qry_BaseQuery_ReportFormat.Quantity) AS SumOfQuantity, Sum(qry_BaseQuery_ReportFormat.TotalCost) AS SumOfTotalCost FROM qry_BaseQuery_ReportFormat GROUP BY qry_BaseQuery_ReportFormat.LineOfBusiness2, qry_BaseQuery_ReportFormat.ProductNum HAVING (((qry_BaseQuery_ReportFormat.LineOfBusiness2)=[StoreType]));
Example 8-7. qry_ReportStep2
SELECT tbl_ReportFormat.LineNumber, tbl_ReportFormat.Print_Description, tbl_ReportFormat.Excel_R1C1_Formula, tbl_ReportFormat.Excel_Number_Format, tbl_ReportFormat.Font_Bold, tbl_Border.Excel_Border_Constant_Value, tbl_LineTypes.Excel_Line_Constant_Value, tbl_ReportFormat.Font_Size, Sum(qry_ReportStep1.SumOfQuantity) AS Quantity, Sum(qry_ReportStep1.SumOfTotalCost) AS Sales FROM ((tbl_ReportFormat LEFT JOIN qry_ReportStep1 ON tbl_ReportFormat.Link_Field = qry_ReportStep1.ProductNum) LEFT JOIN tbl_Border ON tbl_ReportFormat.Border_Location = tbl_Border.Description) LEFT JOIN tbl_LineTypes ON tbl_ReportFormat.Border_Style = tbl_LineTypes.Description GROUP BY tbl_ReportFormat.LineNumber, tbl_ReportFormat.Print_Description, tbl_ReportFormat.Excel_R1C1_Formula, tbl_ReportFormat.Excel_Number_Format, tbl_ReportFormat.Font_Bold, tbl_Border.Excel_Border_Constant_Value, tbl_LineTypes.Excel_Line_Constant_Value, tbl_ReportFormat.Font_Size ORDER BY tbl_ReportFormat.LineNumber;
With the queries, we are ready to write the code in Access VBA to automate Excel. In this example, I have a variable for the type of store, and I fill in a value; you could easily run multiple types by using a recordset, as we did in the earlier examples. For simplicity, run only one, shown in Example 8-8.
Example 8-8. Building a report in Excel using a report format table
Public Sub ExcelReportFormat( ) Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim db As DAO.Database Dim qry As DAO.QueryDef Dim rs As DAO.Recordset Dim fld As DAO.Field Dim LocationChoice As String Dim w, x, y, z As Integer LocationChoice = "Storefront Locations" Set xlapp = New Excel.Application xlapp.Visible = True Set xlwb = xlapp.Workbooks.Add Set xlws = xlwb.ActiveSheet Set db = CurrentDb Set qry = db.QueryDefs("qry_ReportStep2") qry.Parameters(0).Value = LocationChoice Set rs = qry.OpenRecordset xlws.Range("A1").Value = "Sales Report for " & LocationChoice x = 1 For Each fld In rs.Fields xlws.Cells(3, x).Value = fld.Name x = x + 1 Next fld Set xlrng = xlws.Range("A4") xlrng.CopyFromRecordset rs y = 4 + rs.RecordCount For z = 4 To y ' We are assuming that there are only going to be two fields with ' values that need totaled. You can also write it so that you get ' the count of fields -1 and assume that the result of that calculation ' minus 8 is the last x rows that need to have calculations For w = 3 To 8 Set xlrng = xlws.Range(xlws.Cells(z, 9), xlws.Cells(z, 10)) Select Case xlws.Cells(3, w).Value Case "Excel_R1C1_Formula" If xlws.Cells(z, w).Value <> "" Then xlrng.FormulaR1C1 = xlws.Cells(z, w).Value End If Case "Excel_Number_Format" If xlws.Cells(z, w).Value <> "" Then xlrng.NumberFormat = xlws.Cells(z, w).Value End If Case "Font_Bold" If xlws.Cells(z, w).Value = vbTrue Then Set xlrng = xlws.Rows(z) xlrng.Font.Bold = True End If Case "Excel_Border_Constant_Value" If xlws.Cells(z, w).Value <> "" Then With xlrng.Borders(xlws.Cells(z, w).Value) .LineStyle = xlws.Cells(z, w + 1).Value End With w = w + 1 End If Case "Font_Size" If xlws.Cells(z, w).Value <> "" Then Set xlrng = xlws.Rows(z) xlrng.Font.Size = xlws.Cells(z, w).Value End If End Select Next w Next z Set xlrng = xlws.Range(xlws.Columns(3), xlws.Columns(8)) xlrng.Delete xlws.Columns.AutoFit xlws.Columns(1).Font.Size = 10 xlws.Rows(1).Font.Size = 14 xlws.Range("A3").Value = "Line #" xlws.Range("B3").Value = "Description" Set xlrng = xlws.Range(xlws.Cells(1, 1), xlws.Cells(1, 4)) xlrng.HorizontalAlignment = Excel.xlCenterAcrossSelection xlws.Columns.AutoFit rs.Close qry.Close Set fld = Nothing Set qry = Nothing Set rs = Nothing Set db = Nothing Set xlrng = Nothing Set xlws = Nothing Set xlwb = Nothing Set xlapp = Nothing End Sub
Since this procedure does not take any parameters, press the Run button or F5 inside the procedure when you have finished typing it in. There are a few things to note. First, it uses Select...Case instead of additional nested IF statements to make the code more readable. Also, this procedure assumes that the line style of the border is the column to the right of the border location because the location and line style are set at the same time. Next, increment w by 1 to skip that column during the next cycle of the For loop. You could also store all of the options in variables during the loop. Once we are done using the information for the report format, I set a range equal to those columns and delete them.
In reality, I would not format a report like Figure 8-4. However, it demonstrates setting different font styles, font size, border styles, etc. Also, look at the formulas. Writing R1C1-style notation in a table makes it easy to count the rows above or below a row to set your formulas. You are essentially forced into using R1C1-style notation unless you can be certain that you will always use the same columns and rows. Also, if you make changes to the format using A1-style notation, you are in for much more difficult changes to the formulas. It might seem more difficult to use a table, but if you hardcode reports and later need to make changes, you quickly see the value of spending the time up front to create the reports with information in a table. You might need to work though this a few times before you see the value, but I encourage you to do that.
Figure 8-4. The resulting workbook from Example 8-8, formatted to demonstrate the options that you have
|< Day Day Up >|