Let’s say you want to write an Excel macro that will create a new workbook and save it to a specific location and then close the new file. This is really easy with Excel VBA:

Sub AddNewWorkbook()
     Set NewWb = Workbooks.Add
     With NewWb
          .SaveAs Filename:="C:\Path to directory\NewFile.xls"
          .Close
     End With
End Sub

You run this once and it works perfectly. You run it again and Excel tells you that “A file named ‘C:\Path to directory\NewFile.xls’ already exists in this location. Do you want to replace it?”

Well, of course you do. Why is Excel bothering you with these trivial questions?

So you want to supress the Excel alert. To do this, just set Application.DisplayAlerts to False before you save the file, and then turn it back on after you’re done:

Sub AddNewWorkbook()
     Set NewWb = Workbooks.Add
     Application.DisplayAlerts = False
     With NewWb
          .SaveAs Filename:="C:\Path to directory\NewFile.xls"
          .Close
      End With
     Application.DisplayAlerts = True
End Sub

Easy-peasy.

This technique is also handy for supressing the oh-so-lovely Do you want to save the changes you made to workbook X? alert. Microsoft has some additional tips for hiding the saving changes alert in this KB article: How to suppress “Save Changes” prompt when you close a workbook in Excel.

Follow

Related posts: