Option Compare Database
Option Explicit
Sub Productivity_Daily_Report()

Dim rsResources As Recordset
Dim rsFirst As Recordset
Dim rsSecond As Recordset

Dim sqlResources As String
Dim sqlFirst As String
Dim sqlSecond As String

Dim app As New Excel.Application, excelws
Dim Agent As String

sqlResources = "Select * from Resources;"
Set rsResources = CurrentDb.OpenRecordset(sqlResources)

If rsResources.EOF And rsResources.BOF Then
    Beep
    MsgBox "No records!"
    rsResources.Close
    GoTo end_it
End If

Set app = CreateObject("Excel.application")

Set excelws = app.Application
With excelws
.Visible = True

Do While Not rsResources.EOF

Agent = rsResources!FullName

app.Workbooks.Open "Z:\Productivity\Blank.xls", , True

'*********** First Query ***************

sqlFirst = "SELECT Main.FPO, Main.Rec_Date, AI_Queue.[Task Name], Count(Main.MainId) AS CountOfMainId" & vbCrLf & _
"FROM Production_Selection, Main INNER JOIN AI_Queue ON Main.[A&I Queue] = AI_Queue.[A&I Queue]" & vbCrLf & _
"WHERE (((Weekday([Main].[Rec_Date]))<>7 And (Weekday([Main].[Rec_Date]))<>1) AND ((Main.Rec_Date) Between [StartDate] And [EndDate]) AND ((Main.FPO)='" & Agent & "'))" & vbCrLf & _
"GROUP BY Main.FPO, Main.Rec_Date, AI_Queue.[Task Name]"

Set rsFirst = CurrentDb.OpenRecordset(sqlFirst, dbOpenDynaset)

'************* Second Query ***************

sqlSecond = "TRANSFORM Sum(rsFirst!CountOfMainId) AS SumOfCountOfMainId" & vbCrLf & _
"SELECT rsFirst!FPO, rsFirst![Task Name]" & vbCrLf & _
"FROM Days_Month LEFT JOIN rsFirst ON Days_Month.Date = rsFirst!Rec_Date" & vbCrLf & _
"GROUP BY rsFirst!FPO, rsFirst![Task Name]" & vbCrLf & _
"PIVOT Days_Month.Date"

Set rsSecond = CurrentDb.OpenRecordset(sqlSecond, dbOpenDynaset)

.Range("A2").copyfromrecordset rsSecond

excelws.activeworkbook.SaveAs ("Z:\Productivity\BlankReport_" & rsResources!FullName & ".xls")
excelws.activeworkbook.Close

rsResources.MoveNext
Loop
End With
Set excelws = Nothing
Set app = Nothing

rsResources.Close

end_it:
Set rsResources = Nothing
End Sub