Access.2007.VBA.Bibl..

strTemplate = “Orders Archive.xltx”

strTemplateFile = strDBPath & strTemplate

If TestFileExists(strTemplateFile) = False Then

Put up a message and exit if the template is not found:

strTitle = “Template not found”

strPrompt = “Excel template ‘Orders Archive.xlt’” _

& “ not found in “ & strDBPath & “;” & vbCrLf _

& “please put template in this folder and try again”

MsgBox strPrompt, vbCritical + vbOKOnly, strTitle

GoTo ErrorHandlerExit

Else

Debug.Print “Excel template used: “ & strTemplateFile

End If

Template found; create a new worksheet from it:

Set appExcel = GetObject(, “Excel.Application”)

Set rst = dbs.OpenRecordset(“qryRecordsToArchive”)

Set wkb = appExcel.Workbooks.Add(strTemplateFile)

Set wks = wkb.Sheets(1)

wks.Activate

appExcel.Visible = True

Write the date range to title cell:

Set rng = wks.Range(“A1”)

strSheetTitle = “Archived Orders for “ _

& Format(dteStart, “d-mmm-yyyy”) _

& “ to “ & Format(dteEnd, “d-mmm-yyyy”)

Debug.Print “Sheet title: “ & strSheetTitle

rng.Value = strSheetTitle

Go to the first data cell:

Set rngStart = wks.Range(“A4”)

Set rng = wks.Range(“A4”)

Reset lngCount to the number of records in the data source query:

rst.MoveLast

rst.MoveFirst

lngCount = rst.RecordCount

For n = 1 To lngCount

Write data from the recordset to the data area of the worksheet, using the columnoffsetargu-
ment to move to the next cell:

Analyzing Data with Excel 3