Hi Vlad, thank you, i am getting an error on my adjustments, i am unsure if i need a NEXT because i have got a FOR and need a Loop because I have a DO
I have gone wrong somewhere in my adapting ?
Also do i need to loop the recordset ? thank you so much
Code:
Dim xl As Excel.Application, xlsht As Excel.Worksheet, xlwbk As Excel.WorkbookDim srcPath As String, srcFile As String
Dim iWeekday As Integer, iSheet As Integer, rs As DAO.Recordset
Dim StartDate As Date, EndDate As Date
srcPath = "T:\Docs\"
srcFile = "Delivery Planning.xlsx"
StartDate = InputBox("Enter Start Date ?", , "1/1/22")
EndDate = InputBox("Enter End Date ?", , "28/2/22")
iSheet = 1
For iWeekday = 1 To 7
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblAssign WHERE DeliveryDate Between #" & StartDate & "# And #" & EndDate & "# AND Weekday([DeliveryDate]) = " & iWeekday)
rs.MoveFirst
Do Until rs.EOF
Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(srcPath & srcFile)
Set xlsht = xlWrkBk.Worksheets(iSheet)
xlsht.Range("A3").CopyFromRecordset rs
rs.MoveNext
iWeekday = iWeekday + 1
Next
iSheet = iSheet + 2
Loop