Using a query as a data source allows you to combine data from multiple tables and also
to format data as you want it to appear in the target worksheet, using data type conver-
sion functions such as CDate, CCur, or CStr.With a few clicks, you can resize the worksheet columns as needed, edit the column headers as
needed, and make the column header row bold, and a plain but serviceable worksheet (Figure 3.4)
is ready for use.FIGURE 3.4
The exported worksheet with a little formatting applied manually.Using Excel Templates to Create Formatted Worksheets Filled with Access Data ................
If you want to produce a more formatted worksheet, you can prepare an Excel template and format
it as needed — for example, adding a large, centered title and column headings with appropriate
text, perhaps in a larger or bolder font than the data area. Then, instead of using the Excel com-
mand on the Ribbon, use VBA code to export the Access data row by row to the data area of a new
worksheet created from the template. I created a set of queries for archiving data, again using the
sample Northwind data, and a dialog form (fdlgArchiveOrders) that allows the user to select a date
range for archiving Orders data, as shown in Figure 3.5.Note the calendar icon next to the date controls (it appears when a control bound to a
Date field has the focus). Clicking the icon opens a calendar for selecting a valid date,
as shown in Figure 3.6. The new calendar pop-up is definitely useful, though selecting a date far in the
past can be tedious, because there is no way to move year by year.NEW FEATURENEW FEATURE
TIPTIP
Part I The Office Components and What They Do Best