Woodpile
New Member
- Joined
- Sep 14, 2007
- Messages
- 38
- Office Version
- 365
- 2016
- Platform
- Windows
Hello VBA Mystics,
I am having a issue trying to save a file as a different type name and path. The base file is an xlsm. I want to save as an archived copy when the user closes the workbook without any other action.
My real problem is the code works fine on my machine but not on any other, even under my log in. The variables are showing values when other users hover over them, so that part seems to be working.
Here is the full code for the event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Dim REPORTWEEK As Long, REPORTDATE As Date
If [WEEKDAY(TODAY())] < 6 Then REPORTWEEK = [ISOWEEKNUM(Today())]
If [WEEKDAY(TODAY())] = 6 Then REPORTWEEK = [ISOWEEKNUM(Today())] + 1
If [WEEKDAY(TODAY())] < 6 Then REPORTDATE = [TODAY()] + 1
If [WEEKDAY(TODAY())] = 6 Then REPORTDATE = [TODAY()] + 3
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.SaveAs "S:\Maintenance Cost Control\Planning Metrics\00- Transition Schedules\Schedule Archives\WK-" & [REPORTWEEK] & " " & [REPORTDATE] & " Schedule.xlsx", FileFormat:=51
Application.DisplayAlerts = True
Shell "explorer.exe " & "S:\Maintenance Cost Control\Planning Metrics\00- Transition Schedules\Schedule Archives\"
End Sub
I recorded a macro to see if Excel produced a different code. I used a different filename for to remove the variables, just in case:
ActiveWorkbook.SaveAs Filename:= "S:\Maintenance Cost Control\Planning Metrics\00- Transition Schedules\S4 Transition Schedule.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
This works on other computers but I need the filename to use the variables so each day's file is unique.
Any insight is greatly appreciated.
Thanks,
Ed
I am having a issue trying to save a file as a different type name and path. The base file is an xlsm. I want to save as an archived copy when the user closes the workbook without any other action.
My real problem is the code works fine on my machine but not on any other, even under my log in. The variables are showing values when other users hover over them, so that part seems to be working.
Here is the full code for the event:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Dim REPORTWEEK As Long, REPORTDATE As Date
If [WEEKDAY(TODAY())] < 6 Then REPORTWEEK = [ISOWEEKNUM(Today())]
If [WEEKDAY(TODAY())] = 6 Then REPORTWEEK = [ISOWEEKNUM(Today())] + 1
If [WEEKDAY(TODAY())] < 6 Then REPORTDATE = [TODAY()] + 1
If [WEEKDAY(TODAY())] = 6 Then REPORTDATE = [TODAY()] + 3
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.SaveAs "S:\Maintenance Cost Control\Planning Metrics\00- Transition Schedules\Schedule Archives\WK-" & [REPORTWEEK] & " " & [REPORTDATE] & " Schedule.xlsx", FileFormat:=51
Application.DisplayAlerts = True
Shell "explorer.exe " & "S:\Maintenance Cost Control\Planning Metrics\00- Transition Schedules\Schedule Archives\"
End Sub
I recorded a macro to see if Excel produced a different code. I used a different filename for to remove the variables, just in case:
ActiveWorkbook.SaveAs Filename:= "S:\Maintenance Cost Control\Planning Metrics\00- Transition Schedules\S4 Transition Schedule.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
This works on other computers but I need the filename to use the variables so each day's file is unique.
Any insight is greatly appreciated.
Thanks,
Ed