Access.2007.VBA.Bibl..

FIGURE 3.5

A dialog form for selecting Northwind Orders data to archive.

FIGURE 3.6

Selecting a date from the calendar pop-up.

Once the start date and end date have been entered or selected, clicking the Archive button runs

a procedure that creates a new Excel worksheet from a template (Orders Archive.xltx) in the same

folder as the database, fills it with data from tblOrders in the selected date range, and deletes the

archived records.

The ArchiveDataprocedure uses the Start Date and End Date values selected in the dialog as

arguments. This procedure is listed as follows, together with the CreateAndTestQueryproce-

dure it uses to create a query programmatically, and another procedure (TestFileExists) that

tests whether a file exists in a specific folder:

Public Sub ArchiveData(dteStart As Date, dteEnd As Date)

On Error GoTo ErrorHandler

Dim appExcel As Excel.Application

Dim intReturn As Integer

Dim lngCount As Long

Dim n As Long

Analyzing Data with Excel 3