101 Ready-To-Use Excel Macros

Every data-oriented process has an application flow — a succession of applications that take the data from creation to end-user. Sometimes a dataset is touched by only one application, such as when you're creating a report and presenting it in Excel. In many cases, however, data is moved from a database such as Microsoft Access, analyzed and aggregated in Excel, and then distributed via a Word document, PowerPoint presentation, or even e-mail. In this Part, we look at some of the useful macros you can implement to have Excel integrate with other Office applications.

The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.

Macro 92: Running an Access Query from Excel

Here's a nifty macro for those of you who often copy and paste the results of your Microsoft Access queries to Excel. In this macro, you use DAO (Data Access Object) to open and run an Access query in the background and output the results into Excel.

How it works

In this macro, you point Excel to an Access database and pull data from an existing Access query. You then store that query in a Recordset object, which you can use to populate your Excel spreadsheet.

Because you are automating Access, you need to set a reference to the Microsoft Access Object Library. To do so, open the VBE in Excel and select Tools⇒References. The Reference dialog ...