Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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

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

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, ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page