code help exporting to Excel

  • Thread starter Thread starter corkster
  • Start date Start date
C

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 said:
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
Click to expand...

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 said:
corkster said:
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
Click to expand...

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
Click to expand...

Never mind I did figure this out.
 
Back
Top