Excel宏启用模板自动保存：按周末日期命名并指定存储位置

Got it, let's fix up your Workbook_Open code to save with the week's final day and the exact filename format you want. Here's how to do it step by step:

First, we need to calculate the last day of the current week. In VBA, we can use the Weekday function to figure out what day of the week it is right now, then use DateAdd to jump forward to the week's end. By default, I'll assume your week ends on Sunday (super common for timesheets), but I'll note how to switch to Saturday if that's your workflow.

Then, we'll format that end date into the mm-dd-yyyy format you need, and build the filename string accordingly. Also, we should explicitly set the file format to keep it as an enabled-macro workbook (.xlsm) — your original code didn't specify this, which could cause it to save as a regular .xlsx by mistake.

Here's the updated code:

Private Sub Workbook_Open()
    ' Calculate the last day of the current week (Sunday)
    Dim weekEndDate As Date
    weekEndDate = DateAdd("d", 7 - Weekday(Now, vbSunday), Now)
    
    ' Build the full save path and filename
    Dim savePath As String
    savePath = ThisWorkbook.Path & "\" & "Week Ending in " & Format(weekEndDate, "mm-dd-yyyy") & ".xlsm"
    
    ' Save the file as an enabled-macro workbook
    ThisWorkbook.SaveAs Filename:=savePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Quick tweaks if needed: #

• If your week ends on Saturday instead of Sunday, just change vbSunday to vbSaturday in the Weekday function. That will adjust the calculation to land on the Saturday of the current week.
• The code saves the file to the same folder as your template — if you need a specific fixed location instead of ThisWorkbook.Path, replace that part with your target folder path (like "C:\Your\Timesheet\Folder\").
• We added the .xlsm extension explicitly to avoid any file type confusion.

Test this out by double-clicking your template — it should immediately save a new file named something like Week Ending in 09-15-2023.xlsm in the template's folder.

内容的提问来源于stack exchange，提问作者audiFanatic