code help exporting to Excel

[corkster]

Currently I am able to export to Excel all the coordinators each to
their own workbook and export the employees that are associated with
those coordinators within that workbook (the code below does this, John
Nurick was a big help with this code). What I would like is for the
employees to be exported to separate worksheet/tab within the workbook
for that coordinator instead of all in one worksheet.

Anyone have suggestions.

Dim strSQL As String
Dim dbD As DAO.Database
Dim qdfExport As DAO.QueryDef
Dim rscoordinator As DAO.Recordset
Dim strFileSpec As String

Set dbD = CurrentDb()
Set qdfExport = dbD.QueryDefs("Query3")
Set rscoordinator = dbD.OpenRecordset("Query4", dbOpenSnapshot)
Do Until rscoordinator.EOF

qdfExport.SQL = "SELECT * FROM Table2 WHERE [coordinator]=""" &
rscoordinator.Fields("coordinator").Value & " "";"


strFileSpec = "C:\1 corey projects\HEC\testing\" _
& rscoordinator.Fields("coordinator").Value & " " & Format(Date,
"mm-dd-yyyy") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query3",
strFileSpec, True, "" & (employee)
rscoordinator.MoveNext


Loop

 

[corkster]

Currently I am able to export to Excel all the coordinators each to
their own workbook and export the employees that are associated with
those coordinators within that workbook (the code below does this, John
Nurick was a big help with this code). What I would like is for the
employees to be exported to separate worksheet/tab within the workbook
for that coordinator instead of all in one worksheet.

Anyone have suggestions.

Dim strSQL As String
Dim dbD As DAO.Database
Dim qdfExport As DAO.QueryDef
Dim rscoordinator As DAO.Recordset
Dim strFileSpec As String

Set dbD = CurrentDb()
Set qdfExport = dbD.QueryDefs("Query3")
Set rscoordinator = dbD.OpenRecordset("Query4", dbOpenSnapshot)
Do Until rscoordinator.EOF

qdfExport.SQL = "SELECT * FROM Table2 WHERE [coordinator]=""" &
rscoordinator.Fields("coordinator").Value & " "";"


strFileSpec = "C:\1 corey projects\HEC\testing\" _
& rscoordinator.Fields("coordinator").Value & " " & Format(Date,
"mm-dd-yyyy") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query3",
strFileSpec, True, "" & (employee)
rscoordinator.MoveNext


Loop

Not sure if I posted in the right group? or do I need to clarify? I
am working on coding in mS access 2003 form needing vba code or
direction on how to procede. Any help is greatly appreciated.

Thanks

 

[corkster]

Currently I am able to export to Excel all the coordinators each to
their own workbook and export the employees that are associated with
those coordinators within that workbook (the code below does this, John
Nurick was a big help with this code). What I would like is for the
employees to be exported to separate worksheet/tab within the workbook
for that coordinator instead of all in one worksheet.

Anyone have suggestions.

Dim strSQL As String
Dim dbD As DAO.Database
Dim qdfExport As DAO.QueryDef
Dim rscoordinator As DAO.Recordset
Dim strFileSpec As String

Set dbD = CurrentDb()
Set qdfExport = dbD.QueryDefs("Query3")
Set rscoordinator = dbD.OpenRecordset("Query4", dbOpenSnapshot)
Do Until rscoordinator.EOF

qdfExport.SQL = "SELECT * FROM Table2 WHERE [coordinator]=""" &
rscoordinator.Fields("coordinator").Value & " "";"


strFileSpec = "C:\1 corey projects\HEC\testing\" _
& rscoordinator.Fields("coordinator").Value & " " & Format(Date,
"mm-dd-yyyy") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query3",
strFileSpec, True, "" & (employee)
rscoordinator.MoveNext


Loop

Not sure if I posted in the right group? or do I need to clarify? I
am working on coding in mS access 2003 form needing vba code or
direction on how to procede. Any help is greatly appreciated.

Thanks

Never mind I did figure this out.