File SaveAs weird behaviour.

[Kayslover]

Firstly, Happy New Year to all and hope that this year will be a lot better than the last.

I have the following in a macro. It opens an Excel Template called Buylist V2.xltm, unprotect a sheet, populate a cell, protect the sheet that was just unprotected and finally save the template file as just a Macro Enabled file with an .xlsm extension.

All variable have been defined as Dim the code is as follows:-

Workbooks.Open fPath & "Buylist V2.xltm"
Sheets("Buylist").Unprotect
Sheets("Buylist").Select
Range("A1").Value = myYear
ActiveSheet.Range("A3").Select
Sheets("Buylist").Protect
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fPath & myYear & " Buylist.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
MsgBox (ActiveWorkbook.Name & " has been created and saved." & vbNewLine & vbNewLine _
                & "Press OK to confirm the message."), vbOKOnly, Title:="                      Create Treasurer's Files"
ActiveWorkbook.Close
Application.DisplayAlerts = True

The problem I am facing is that if the file I am trying to save exists, it just shows the Opened Template, and the macro doesn’t even show the MSGBOX, and it STOPS.

My understanding was that the Application.DisplayAlerts = False would stop giving the message that the file exists and do you want to save, exit…. And that the file would be forced saved.

I am using this mechanism to force save files elsewhere in the macro and getting the results that I want (i.e. force save).

Any ideas how to get round this or where am I going wrong to force save the file?

 

[davesexcel]

Is there more code?

 

[Kayslover]

Daveexcel,

There is more code to the end that does various things (I didn't include it as it is long and works as expected).

There is code at the top that gets input from the user by using an InputBox to get the Year which is store in myYear

 

[davesexcel]

Is myYear enter as a date?
Do you have an error trap?

 

[Kayslover]

Daveexcel,

Dim myYear As Variant
Dim AllOk As Boolean

Dim ws As Worksheet
Dim fPath As String
fPath = ThisWorkbook.Path & "\"
Dim FolderName As String
FolderName = ThisWorkbook.Path & "\"
Dim d As Long
Dim CurrentFile As String
Dim myYear As Variant
Dim Confirm As String
Dim CurrentYear As Integer
Dim Answer1 As Integer
Dim Answer2 As Integer
Dim Answer3 As Integer
Dim Answer4 As Integer

Input to InputBox is YYYY (i.e. 2021), the only error trap is as follows:-

Do Until AllOk
        myYear = InputBox("Please enter the year (Format YYYY) that you want to create monthly Till Taking Sheets for and other associated files.", Title:="                      Create Treasurer's Files")
        If myYear = "" Then Exit Sub
        If Val(myYear) >= Year(Date) Then
            Confirm = MsgBox("The Year you want to create files for is " & myYear & " Is that correct?", vbQuestion + vbYesNo + vbDefaultButton2, Title:="                      Create Treasurer's Files")
        If Confirm = vbYes Then AllOk = True
            MsgBox ("Starting to create required files. If you look just above the Start button, you will get updates as to which monthly Till Taking file is being created."), vbOKOnly, _
            Title:="                      Create Treasurer's Files"
        Else
            MsgBox "Date entered is " & myYear & " and it is in the past, please enter a vaild date", Title:="                      Create Treasurer's Files"
        End If
Loop

 

[Kayslover]

DavesExcel,

Resolved this issue if you are interested.

Have a look at another thread that I created for informations purposes. Macro Keyboard Shortcut NOT working.