File SaveAs weird behaviour.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
137
Office Version
  1. 2013
Platform
  1. Windows
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:-

VBA Code:
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?
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
137
Office Version
  1. 2013
Platform
  1. Windows
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
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
137
Office Version
  1. 2013
Platform
  1. Windows
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:-

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,531
Messages
5,625,366
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top