Just for anyones reference in the future the I had to run this again with a few changes to clear the file before I enter the new data into it.
Private Sub Command4_Click()
Dim answer As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim objXL As Excel.Application
Dim objXLWrkBk As Excel.Workbook
Dim objXLWrkSht As Excel.Worksheet
Dim lngStartRange As Long
Dim myCount As Variant
Dim count As Integer
If StartDate > EndDate Then
MsgBox StartDate & " is after " & EndDate & vbCrLf & "Start date can not be after End date" & vbCrLf & vbCrLf & vbCrLf & "Change before you can proceed!", vbExclamation, "Date Error"
Else
strSQL = "SELECT * FROM qryMain WHERE Date BETWEEN #" & StartDate & "# AND #" & EndDate & "#"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.BOF Then
'no records
MsgBox "There are no records for the dates entered!"
Else
myCount = DCount("[CustomerID]", "qryCount")
lngStartRange = 5 'start at row 5
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open("M:\Book Keeping\Matt Majewski Procedures\Ferris Lockbox\Ferris Ind.xls")
Set objXLWrkSht = objXLWrkBk.Worksheets("Sheet1") 'takes the name of the tab in Excel in your case sheet1
objXLWrkSht.Range("A1").Activate
count = 5
While count <> 200
objXL.Cells(lngStartRange, "A").Value = ""
objXL.Cells(lngStartRange, "B").Value = ""
objXL.Cells(lngStartRange, "C").Value = ""
objXL.Cells(lngStartRange, "D").Value = ""
objXL.Cells(lngStartRange, "E").Value = ""
lngStartRange = lngStartRange + 1 'increment the row for next customer
'rs.MoveNext 'move to next customer
count = count + 1
Wend
objXLWrkBk.Close SaveChanges:=True
objXL.Quit
If rs.BOF Then
'no records
MsgBox "There were no reocerds withe the date range you specified!"
Else
lngStartRange = 5 'start at row 5
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open("M:\Book Keeping\Matt Majewski Procedures\Ferris Lockbox\Ferris Ind.xls")
Set objXLWrkSht = objXLWrkBk.Worksheets("Sheet1") 'takes the name of the tab in Excel in your case sheet1
objXLWrkSht.Range("A1").Activate
While Not rs.EOF
objXL.Cells(lngStartRange, "A").Value = rs("CustomerName").Value
objXL.Cells(lngStartRange, "B").Value = rs("CustomerNumber").Value
objXL.Cells(lngStartRange, "C").Value = rs("CheckNumber").Value
objXL.Cells(lngStartRange, "D").Value = rs("InvoiceNumber").Value
objXL.Cells(lngStartRange, "E").Value = rs("CheckAmount").Value
lngStartRange = lngStartRange + 1 'increment the row for next customer
rs.MoveNext 'move to next customer
Wend
objXL.Cells(lngStartRange, "A").Value = "Total"
objXL.Cells(lngStartRange, "E").Value = "=SUM(E5:E" & (lngStartRange - 1) & ")"
answer = MsgBox("Do you really want to print two copies of this?", vbYesNo, "Congirm Print")
If answer = vbYes Then
objXLWrkSht.PrintOut 1, 2, 2
'beep
MsgBox " PRINT OUT DONE!! "
End If
objXLWrkBk.Close SaveChanges:=True
objXL.Quit
End If
Done:
Set rs = Nothing
Set db = Nothing
Set objXL = Nothing
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing
End If