Chapter 35 Use Excel For Output.
Chapter 35 Use Excel For Output.
U
sing VBA to automate Excel spreadsheets for output of data from Access is
extremely useful. You have already seen how you can create reports based on data in
tables, but very often users want to see the information in something more flexible.
Access reports are very good for presenting your data in a tabular form, but they are very
difficult to copy and paste into other applications. Also, users may have a custom format that
they wish to see the data in that is not supported by Access reports.
The Northwind database will be used to provide sample data for this chapter. You can
access it by loading Access 2010 and then opening the Sample folder in the central pane
(Available Templates) of the opening screen.
Using a Recordset to Create a Spreadsheet
Using a recordset in VBA is a very simple way to dump a chunk of data into an Excel
spreadsheet. PressALT+F11to enter the VBE window and insert a new module using Insert |
Module from the VBE menu.
You also need to add a reference to the Excel Object Library. You do this by using Tools |
References on the VBE menu. In the pop-up window, scroll down the extensive list of
libraries until you see Microsoft Excel 14.0 Object Library. Set the check box to True and
then click OK.
329