Access Cookbook

Print an Access Report from Excel

Problem

You keep and work with your data in Excel, but you’d like to print reports using Access. You know you can use the Access Report Wizard directly from Excel, but you’d like more control over the process. Can you do this using VBA?

Solution

Access allows you to control its actions using Automation. Anything you can do directly from Access, you can also do from Excel. This solution uses Automation to link your Excel worksheet to an Access database, use that data as the data source for a report, and then remove the linked table. Because you can directly link to an Excel worksheet from Access, this process doesn’t need to involve importing the data—you can use it as-is, live, in your Excel environment.

To try out the sample database, first load 12-02.XLS into Excel. This workbook includes the data (shown in Figure 12-3) and the VBA code that controls the sample. Next, click the Open Access Report button, which causes Excel to load a copy of Access and then load 12-02.MDB, link the current data to that database, and display the report in print preview mode.

Figure 12-3. Use data in Excel to print a report in Access

To use this technique in your own applications, follow these steps:

1. Create a database, including a report that you’d like to print. You may want to link the Excel data that’s going to be the data source now, so that it’s easier to create ...