Access.2007.VBA.Bibl..

Dim rng As Excel.Range

Dim rngStart As Excel.Range

Dim strDBPath As String

Dim strPrompt As String

Dim strQuery As String

Dim strSaveName As String

Dim strSheet As String

Dim strSheetTitle As String

Dim strSQL As String

Dim strTemplate As String

Dim strTemplateFile As String

Dim strTemplatePath As String

Dim strTitle As String

Dim wkb As Excel.Workbook

Dim wks As Excel.Worksheet

Create a filtered query using the dates selected in the dialog:

strQuery = “qryArchive”

Set dbs = CurrentDb

strSQL = “SELECT * FROM tblOrders WHERE “ _

& “[ShippedDate] Between #” & dteStart & “# And #” _

& dteEnd & “#;”

Debug.Print “SQL for “ & strQuery & “: “ & strSQL

lngCount = CreateAndTestQuery(strQuery, strSQL)

Debug.Print “No. of items found: “ & lngCount

If lngCount = 0 Then

Exit if no orders are found in the selected date range:

strPrompt = “No orders found for this date range; “ _

& “canceling archiving”

strTitle = “Canceling”

MsgBox strPrompt, vbOKOnly + vbCritical, strTitle

GoTo ErrorHandlerExit

Else

strPrompt = lngCount & “ orders found in this date “ _

& “range; archive them?”

strTitle = “Archiving”

intReturn = MsgBox(strPrompt, vbYesNo + vbQuestion, _

strTitle)

If intReturn = vbNo Then

GoTo ErrorHandlerExit

End If

End If

Create a new worksheet from the template and export the Access data to it:

strDBPath = Application.CurrentProject.Path & “\”

Debug.Print “Current database path: “ & strDBPath

Part I The Office Components and What They Do Best