Auto-saving an Excel file opened from a template file

Question

I made a macro-enabled Excel file (.xlsm) and I would like to save all files opened with the template to a certain location with the date in the file name immediately after double-clicking the template.

Private Sub Workbook_Open()
    ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(Now, "yyyy-mm-dd") & " Timesheet"
End Sub

I'd like it to save with the last day of the work week.

The file name should be "Week Ending in mm-dd-yyyy).
I have a formula in my workbook that does this, but I'm not sure if this is the same as VBA code.

=IF(WEEKDAY(TODAY())=7,TODAY()-1,IF(WEEKDAY(TODAY())=6,TODAY(),TODAY()-WEEKDAY(TODAY())-1))

Tragamor Tragamor · Accepted Answer · 2018-03-30T09:32:55

There are a number of issues with how you are currently implementing your code.

Opening your template file will autosave it so editing the original becomes more difficult

You can't access the templates path if you open it as a normal file

Hopefully the following will help implement the template although you will need to hardcode the desired path in the code

In 'ThisWorkbook'

Private Sub Workbook_Open()
    Call SaveFromTemplate
End Sub

In a module

Function SaveFromTemplate()
    Dim Path As String: Path = ActiveWorkbook.FullName
    If Not Right(Path, Len(Path) - InStrRev(Path, ".")) = "xltm" Then
        Path = "C:\Edit this path\"
        ' Friday = 6
        ThisWorkbook.SaveAs Path & Format(WeekdayDate(Date, 6), "yyyy-mm-dd") & " Timesheet"
    End If
End Function

Function WeekdayDate(DateVal As Date, DayInWeek As Long) As Date
    DayInWeek = DayInWeek Mod 7
    Dim DayOffset As Long: DayOffset = DayInWeek - Weekday(DateVal) + IIf(DayInWeek - Weekday(DateVal) < 0, 7, 0)
    WeekdayDate = DateAdd("d", DayOffset, DateVal)
End Function

* ADDENDUM *

I hadn't realised you were using a '.xlsm' as your template start. You can relay the template path with this but you will have issues when re-opening the saved '.xlsm'

Modified so it only saves if it's not the 'Timesheet' file

In 'ThisWorkbook'

Private Sub Workbook_Open()
    If InStr(ThisWorkbook.FullName, "Timesheet") = 0 Then
        ' Friday = 6
        ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(WeekdayDate(Date, 6), "yyyy-mm-dd") & " Timesheet"
    End If
End Sub

Auto-saving an Excel file opened from a template file

1 Answers