Force yes for save over and save as macro free workbook

Question

The macro I made saves a template workbook as two separate files. One is saved per test (Location1,2,3, or 4) then is used in another macro to use the data from each test. The Second is a raw data file kept for back up. Now the issue is every time I run the test per location and run this macro it ask me if I want to save over the previous test. How can I tell it to say yes with out asking. Same for the do i want to save this workbook as a macro free workbook. What do i have to put in my code and where should i put it? Any helps is much appreciated. Thanks

Solved Code:

Sub Auto_Open()

   With Range("A30")
   .Value = Time
   .NumberFormat = "h-mm-ss AM/PM"
   End With

    Dim FileName    As String
    Dim FilePath    As String
    Dim FileDate    As String

    MyNote = "Is Cell 'B27' Overview Information" & SavePath & " Location1,2,3,or 4?"

    Answer = MsgBox(MyNote, vbQuestion + vbYesNo)

    If Answer = vbYes Then

        FilePath = "C:\Users\aholiday\Desktop\FRF_Data_Macro_Insert_Test"
        FileName = Sheets("Data").Range("B27").Text

        Application.DisplayAlerts = False

        ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName

        Dim FileCopyName    As String
        Dim FileCopyPath    As String
        Dim FileTime        As String

        FilePath = "C:\Users\aholiday\Desktop\Backup"
        FileName = Sheets("Data").Range("B27").Text
        FileTime = Sheets("Data").Range("A30").Text


        ThisWorkbook.SaveAs FileName:=FilePath & "\" & FileName & FileTime & ".xlsx", FileFormat:=xlOpenXMLWorkbook

        MsgBox "File was saved!"
        MsgBox "Ready for Next Test, Please Exit."

        Application.DisplayAlerts = True

    Else
       MsgBox "File was not saved, Please Use Location_1,2,3or,4 Durring SIG ATM Test"
    End If

End Sub

score 3 · Accepted Answer · answered Aug 12 '15 at 15:43

3

Add Application.DisplayAlerts = False before you try and save. Remember to turn in back to True after you've saved.

answered Aug 12 '15 at 15:43

Darren Bartrup-Cook

18,362
1
23
45

Will that automatically say yes for both saves? – Duraholiday Aug 12 '15 at 15:46
It should do providing you put it before the first save. I'm looking at @MatthewD answer - I've never seen that method so it may work better. – Darren Bartrup-Cook Aug 12 '15 at 15:59
Worked! I knew it was something simple. Thanks – Duraholiday Aug 12 '15 at 16:03

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

ConflictResolution should be xlLocalSessionChanges to not see the prompt

ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges

from

How to use workbook.saveas with automatic Overwrite

edited May 23 '17 at 10:28

Community

1
1

answered Aug 12 '15 at 15:50

MatthewD

6,719
5
22
41

This is incorrect. `ConflictReslution` deals with conflicts over shared workbooks, not the overwrite prompts, as is stated in the linked answers. – xiaomy Nov 28 '17 at 17:42

Force yes for save over and save as macro free workbook

2 Answers2