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"
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")
bc = rs.Fields("Barcode")
Me.barcode = bc
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "print time sheets for selected employees", gg, True, fn & " " & Ln
DoEvents
.MoveNext
Loop
End With
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
Sub format_sheets_now()
Dim ws As excel.Worksheet
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strPath As String
Dim stDocName As String
'Early binding
'Dim Excel_Application As excel.Application
'late binding
Dim Excel_Application As Object
Dim Excel_Workbook As Workbook
Dim Current_Worksheet As Worksheet
Dim gg As String
gg = "C:\aaa\timesheets\Employee Time Report master.xls"
sdt = Format(start_date, "dd-mm-yy")
edt = Format(End_date, "dd-mm-yy")
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Set Current_Worksheet = Excel_Workbook.Worksheets(1)
Excel_Application.WindowState = xlMaximized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Excel_Workbook.Worksheets(1).Select
Excel_Application.DisplayAlerts = False
Excel_Workbook.SaveAs filename:="C:\aaa\timesheets\Employee Time Report for - " & sdt & " to " & edt & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Excel_Application.DisplayAlerts = True
Bookmarks