I do this after formatting my sheets, it saves the file as xlsm then deletes the old file. it can all be done from access. (90% of code removed for clarity.)
this creates the files...
Code:
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 timesheet master.xls"
bb = InputBox("Enter number of sheets required", "Excel time sheet generation", 1)
fn = "FirstName"
Ln = "LastName"
DoCmd.RunSQL "DELETE * FROM manual_sheet"
t = Forms![main menu].start_date
tt = Forms![main menu].End_date
aa = tt - t
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
then i clean it up here...
Code:
Excel_Workbook.SaveAs filename:="C:\aaa\timesheets\Employee Timesheet for - " & sdt & " to " & edt & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
t = Len(Dir("C:\aaa\timesheet backups"))
If t = 0 Then
Else
Excel_Workbook.SaveAs filename:="C:\timesheet backups\Employee Timesheet for - " & sdt & " to " & edt & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End If
Excel_Application.DisplayAlerts = True
Excel_Application.Visible = True
Kill "C:\aaa\timesheets\Employee Timesheet Master.xls"