Hi Guy's, is it possible to loop through part of a recordset, ie we only require Date once, Invoice Number once Customer once but there may be 4 records of:
Invoice Details, Hours, Rate, Amount
I have in the invoice details Monday, Tuesday, Wednesday, Friday so there are 4 records
The Excel file is showing Friday only then coming up with Object required and debug highlights this line: .Worksheets(1).Cells(13, 9) = rs.Fields("Amount")
Code:
Set ApXL = CreateObject("Excel.Application")
Set xlWB = ApXL.Workbooks.Open(XLTemp)
ApXL.ActiveWorkbook.SaveAs XLPath & XLFile
ApXL.Workbooks.Open XLPath & XLFile, True, False
ApXL.Visible = True
Code:
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM tblInvoices WHERE Customer = '" & strCustName & "'" & " And InvoiceNumber = " & InvNo)
With xlWB
.Worksheets(1).Cells(1, 9) = rs.Fields("InvoiceNumber")
.Worksheets(1).Cells(2, 9) = rs.Fields("InvoiceDate")
.Worksheets(1).Cells(4, 9) = rs.Fields("Customer")
.Worksheets(1).Cells(5, 9) = rs.Fields("Add1")
.Worksheets(1).Cells(6, 9) = rs.Fields("Add2")
.Worksheets(1).Cells(7, 9) = rs.Fields("Town")
.Worksheets(1).Cells(8, 9) = rs.Fields("PostCode")
Do Until rs.EOF
.Worksheets(1).Cells(13, 1) = rs.Fields("InvoiceDetails")
.Worksheets(1).Cells(13, 7) = rs.Fields("Hours")
.Worksheets(1).Cells(13, 8) = rs.Fields("Rate")
.Worksheets(1).Cells(13, 9) = rs.Fields("Amount")
Loop
.Worksheets(1).Cells(44, 9) = DSum("Amount", "tblInvoices", "[InvoiceNumber] = " & InvNo)
.Worksheets(1).PageSetup.FitToPagesWide = 1
.Worksheets(1).PageSetup.FitToPagesTall = 1
.Worksheets(1).PageSetup.Orientation = xlLandscape
End With
xlWB.Close
ApXL.Quit
Set ApXL = Nothing