8.3. Creating a Report from Scratch

 < Day Day Up > 

When creating a report from scratch, you need to take care of some items beyond formulas and number formatting. You may need to add a worksheet header and footer, work with fonts, and use the page setup options to affect how the worksheet

Figure 8-2. Worksheet resulting from the code in Example 8-3, with a formula in the formula bar that allows the same report template to be used regardless of the line of business

prints. Because we have already worked with a procedure that built the formulas and number formatting from scratch, I modify that procedure to create Example 8-4. For this example, I create a new workbook and add two worksheets. The first worksheet houses the data, and the second worksheet houses the report. I place a title in the worksheet header and format the worksheet to print landscape on one page. The procedure cycles through a recordset to run each report and saves the workbook in a folder on the C drive.

Example 8-4. Report from scratch module
 Public Sub MultiProcRS(  ) Dim xlapp As Excel.Application Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim param As String Dim x, y, z, a, b, c As Integer Dim db As DAO.Database Dim qry As DAO.QueryDef Dim rs As DAO.Recordset Dim fld As DAO.Field Dim fldcol As Integer Dim cyclers As DAO.Recordset Set db = CurrentDb Set cyclers = db.OpenRecordset("SELECT tbl_CostCenters.LineOfBusiness2, " & _   "tbl_CostCenters.LineOfBusiness3 " & _   "FROM tbl_CostCenters " & _   "GROUP BY tbl_CostCenters.LineOfBusiness2, tbl_CostCenters.LineOfBusiness3 " & _   "HAVING (((tbl_CostCenters.LineOfBusiness3)=""Profit Centers""));") Set xlapp = New Excel.Application xlapp.Visible = True cyclers.MoveFirst While Not cyclers.EOF   Set xlwb = xlapp.Workbooks.Add   Set xlws = xlwb.Sheets.Add   xlws.Name = "ReportData"   Set qry = db.QueryDefs("qry_ExcelReport")   param = cyclers.Fields(0).Value   qry.Parameters(0).Value = param   Set rs = qry.OpenRecordset   fldcol = 1   For Each fld In rs.Fields     xlws.Cells(1, fldcol).Value = fld.Name     fldcol = fldcol + 1   Next fld   Set xlrng = xlws.Range("A2")   xlrng.CopyFromRecordset rs   x = rs.RecordCount   rs.Close   Set xlws = xlwb.Sheets.Add   xlws.Name = "Report"   xlws.Cells(3, 1).Value = "Category"   xlws.Cells(3, 2).Value = "Units"   xlws.Cells(3, 3).Value = "Sales"   Set xlrng = xlws.Range(xlws.Cells(3, 1), xlws.Cells(3, 3))   xlrng.Font.Bold = True   Set qry = db.QueryDefs("qry_ExcelProducts")   qry.Parameters(0).Value = param   Set rs = qry.OpenRecordset   Set xlrng = xlws.Range("A4")   xlrng.CopyFromRecordset rs   y = rs.RecordCount   For b = 4 To y + 3     Set xlrng = xlws.Cells(b, 2)     xlrng.FormulaArray = "=Sum((ReportData!R2C2:R" & x + 1 & _                      "C2=Report!R" & b & "C1)*ReportData!R2C4:R" & _                      x + 1 & "C4)"     Set xlrng = xlws.Cells(b, 3)     xlrng.FormulaArray = "=Sum((ReportData!R2C2:R" & x + 1 & _                      "C2=Report!R" & b & "C1)*ReportData!R2C5:R" & _                      x + 1 & "C5)"   Next b   Set xlrng = xlws.Range(xlws.Cells(4, 2), xlws.Cells(y + 3, 2))   xlrng.NumberFormat = "#,##0"   Set xlrng = xlws.Range(xlws.Cells(4, 3), xlws.Cells(y + 3, 3))   xlrng.NumberFormat = "$0.00"   rs.Close   z = y + 5   xlws.Cells(z, 1).Value = "Center"   xlws.Cells(z, 2).Value = "Units"   xlws.Cells(z, 3).Value = "Sales"   Set xlrng = xlws.Range(xlws.Cells(z, 1), xlws.Cells(z, 3))   xlrng.Font.Bold = True   z = z + 1   Set qry = db.QueryDefs("qry_ExcelCenters")   qry.Parameters(0).Value = param   Set rs = qry.OpenRecordset   Set xlrng = xlws.Cells(z, 1)   xlrng.CopyFromRecordset rs   a = z + rs.RecordCount   For b = z To a - 1     Set xlrng = xlws.Cells(b, 2)     xlrng.FormulaArray = "=Sum((ReportData!R2C1:R" & x + 1 & _                      "C1=Report!R" & b & "C1)*ReportData!R2C4:R" & _                      x + 1 & "C4)"     Set xlrng = xlws.Cells(b, 3)     xlrng.FormulaArray = "=Sum((ReportData!R2C1:R" & x + 1 & _                      "C1=Report!R" & b & "C1)*ReportData!R2C5:R" & _                      x + 1 & "C5)"   Next b   Set xlrng = xlws.Range(xlws.Cells(z, 2), xlws.Cells(a - 1, 2))   xlrng.NumberFormat = "#,##0"   Set xlrng = xlws.Range(xlws.Cells(z, 3), xlws.Cells(a - 1, 3))   xlrng.NumberFormat = "$0.00"   xlws.Columns.AutoFit   With xlws.PageSetup     .CenterHeader = "&16Summary Report for " & param & vbCr & _       "&10As of " & Now(  )     .LeftMargin = Excel.Application.InchesToPoints(0.75)     .RightMargin = Excel.Application.InchesToPoints(0.75)     .TopMargin = Excel.Application.InchesToPoints(1)     .BottomMargin = Excel.Application.InchesToPoints(1)     .HeaderMargin = Excel.Application.InchesToPoints(0.5)     .FooterMargin = Excel.Application.InchesToPoints(0.5)     .Orientation = Excel.xlLandscape     .FitToPagesTall = 1     .FitToPagesWide = 1     .PrintErrors = Excel.xlPrintErrorsDisplayed   End With   xlwb.SaveAs "C:\Reports\" & param & ".xls"   xlwb.Close   Set xlwb = Nothing   cyclers.MoveNext Wend Set fld = Nothing cyclers.Close Set cyclers = Nothing Set xlrng = Nothing Set xlws = Nothing xlapp.Quit Set xlapp = Nothing rs.Close qry.Close Set rs = Nothing Set qry = Nothing Set db = Nothing End Sub

An SQL string creates the recordset called cyclers. As you look at the string, notice the two sets of double quotes around Profit Centers. I could have used a query with a parameter and simply passed the parameter to the query, but it is often useful to place the criteria directly in the SQL. If you pass numeric values, it is very easy to do. However, when you get to string values, you must place quotes inside your string. Using single quotes sometimes causes problems if you have an apostrophe in your variable. By placing two double quotes on both sides, the string is built with the appropriate double-quote around the text. This avoids problems with single quotes noted above.

Because this procedure is not called with parameters, you can just press F5 while inside the procedure to run it. You get two Excel files that are nearly identical to the ones produced in Example 8-3. The main difference is that a parameter worksheet isn't needed, and the title is used in the header of the worksheet instead of placing it in the first row.

Also, pay particular attention to the PageSetup section of the code. When the header string is being set, you will see &16 at the beginning of the string, and vbCr and &10 at the beginning of the next line. The ampersand, along with a number, sets the font size for the string. The vbCr places a carriage return (like pressing enter in the header) between the lines. Next, look at the InchesToPoints method of the Excel Application object. Because the margins are set in points, Excel provides this handy method to allow you to set the margin with Inches. For reference, 1 inch is 72 points, but it is much easier to use the method.

If you use the macro recorder to set the page setup, add Excel. in front of each instance of Application.InchesToPoints. This is necessary because this method is part of the Excel Application object, and if you just reference the application while in Access, it attempts to use Access objects and it will not find this method in Access. When you open the created Excel workbook and do a Print Preview of the report, notice that there are two different font sizes in the header, and it is formatted to print on one page. You can also set up a page to print with print titles, with a different orientation, or on multiple pages. The easiest way to set it up is to just use the macro recorder in Excel and edit the code to add the Excel references where needed.

     < Day Day Up > 

    Integrating Excel and Access
    Integrating Excel and Access
    ISBN: 0596009739
    EAN: 2147483647
    Year: 2005
    Pages: 132

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net