VBA workbooks.Close without being prompted to if the user wants to save?

17
votes

I am writing a VBA program that converts .xls files into .csv files. The problem is that is brings up the "Do you want to save the changes to myFile.csv?" Dialog box.

Here is a snippet of my code:

currentBook.SaveAs Filename:=fileNameS, FileFormat:=xlCSV, ConflictResolution:=xlLocalSessionChanges    
currentBook.Close SaveChanges:=False

What do I need to add so that I don't get the "Do you want to save the changes to myFile.csv?" Dialog box?

2
excelvba
This is an exact duplicate of How to avert the save MessageBox prompt in Excel-VBA? and should therefore be closed. – Jean-François Corbett
@Jean-FrançoisCorbett, the method in that post does not work for me. – power
Why Dont you try "DoCmd.SetWarnings False" – VBwhatnow
@power: They both don't work? – Jean-François Corbett
@Jean-FrançoisCorbett, Alistair Weir's solution works. I had to use currentBook.Saved = True – power

2 Answers

19
votes

Try using ThisWorkbook.Saved = True

        Application.DisplayAlerts = False
        currentBook.SaveAs Filename:=fileNameS, FileFormat:=xlCSV, ConflictResolution:=xlLocalSessionChanges
        currentBook.Saved = True            
        currentBook.Close SaveChanges:=False
        Application.DisplayAlerts = True
2
votes

This is what I have done in the past and it's worked for me:

Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=fileNameS, FileFormat:=xlCSV, conflictResolution:=xlLocalSessionChanges
Application.DisplayAlerts = True
ActiveWorkbook.Close False

You can also try putting the Close before the DisplayAlerts is reset to true...