Print an Access Report from Excel
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?
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:
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 ...