heres 2 bits of code i use to create timesheets, first one uses a query result and adds extra sheets to the excel workbook, the second adds req sheets and rows to workbook, they both use the range option of the transfer spreadsheet method to name the sheets
Code:
Private Sub Command94_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim gg As String
sdt = Format(start_date, "dd-mm-yy")
edt = Format(End_date, "dd-mm-yy")
gg = "C:\aaa\timesheets\Employee Time Report master.xls"
aa = MsgBox(" Do you also require Manaul Sheets ?", vbYesNoCancel + vbDefaultButton2, "Time Sheet Additions")
If aa = vbYes Then
CC = InputBox("Enter number of sheets required", "Excel time sheet generation", 1)
bb = InputBox("Enter number of lines per sheet required", "Manual Time Sheet creation", 1)
Else
If aa = vbCancel Then Exit Sub
End If
Set db = CurrentDb()
Set rs = db.OpenRecordset("create excel time sheets for selected employees on main menu")
With rs
.MoveFirst
Do While Not .EOF
fn = rs.Fields("First Name")
Ln = rs.Fields("Last Name")
cd = rs.Fields("Code #")
bc = rs.Fields("barcode")
Me.barcode = bc
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "print time sheets for selected employees", gg, True, fn & " " & Ln & " " & cd
DoEvents
.MoveNext
Loop
End With
If aa = vbYes Then
DoCmd.RunSQL "DELETE * FROM manual_sheet"
t = Forms![main menu].start_date
For i = 1 To bb
Set rstActual = db.OpenRecordset("manual_sheet", dbOpenDynaset)
rstActual.AddNew
rstActual!new_date = t
rstActual.Update
rstActual.Close
t = t + 1
Next i
nRecords = DCount("*", "Employee Time Report Output with lunch for manual sheet")
If nRecords = 0 Then
MsgBox "No data for selected period, change report dates and try agian.", vbOKOnly, "Error"
Exit Sub
End If
For i = 1 To CC
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch for manual sheet", gg, True, "FirstName" & i & " " & "LastName" & i
Next i
End If
rs.Close
Set rs = Nothing
Set db = Nothing
Call format_sheets_now
t = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & " And saved in the following directory" & vbCrLf & vbCrLf & " C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")
End Sub '***********************************************************************************************************************************************
Private Sub Command147_Click() 'create manual time sheet
Dim db As DAO.Database
Set db = CurrentDb
Dim rstActual As DAO.Recordset
Set rstActual = db.OpenRecordset("manual_sheet")
gg = "C:\aaa\timesheets\Employee Time Report master.xls"
bb = InputBox("Enter number of sheets required", "Excel time sheet generation", 1)
aa = InputBox("Enter number of lines per sheet required", "Manual Time Sheet creation", 1)
fn = "FirstName"
Ln = "LastName"
DoCmd.RunSQL "DELETE * FROM manual_sheet"
t = Forms![main menu].start_date
For i = 1 To aa
Set rstActual = db.OpenRecordset("manual_sheet", dbOpenDynaset)
rstActual.AddNew
rstActual!new_date = t
rstActual.Update
rstActual.Close
t = t + 1
Next i
For i = 1 To bb
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch for manual sheet", gg, True, "FirstName" & i & " " & "LastName" & i
Next i
Call format_sheets_now
t = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & " And saved in the following directory" & vbCrLf & vbCrLf & " C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")
End Sub