Integrating Excel and Access

Creating a Report from Scratch

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, ...