Problems Closing Excel Application After Macro Finishes

[EnginerdUNH]

Hi,

I have a macro which at the end of it running, I would like to save a XLSX (i.e. non-macro-enabled workbook) copy of the file and then close out of the workbook and excel. The reason I want to save an XLSX copy vs saving another XLSM is because I don't want anyone to have the ability to re-run the macro on copy of the workbook once it has been run. The code I am using works to save the XLSX file and close out of it but won't close out of the excel application itself. Looking for some help in figuring out what I'm doing wrong. The code I am using looks something like this:

Code: Select all

Application.ScreenUpdating = False
'Do some other stuff here

FilePath = "Z:\FolderName\"
FileName ="TestFileName - " & Format(Date,"MMDDYY") & ".xlsx"

Application.DisplayAlerts  = False
ActiveWorkbook.SaveAs FilePath & FileName, FileFormat:=

Application.ScrenUpdating = True
Application.DisplayAlerts = True

Workbooks(FileName).Close SaveChanges:=False
Application.Quit

[snb]

What happens if you use:

Code: Select all

Sub M_snb()
  For Each it In Workbooks
    it.Saved = True
  Next

  Application.quit
End Sub

[HansV]

When you close the workbook, the code stops running, so the line Application.Quit doesn't get executed. I'd remove the line

Code: Select all

Workbooks(FileName).Close SaveChanges:=False

Excel will then close the workbook automatically when it quits.

[EnginerdUNH]

Hans, you are so good! I wasn't even thinking about the fact that any macros will stop running after you've closed the file but it definitely makes sense now that I think about it. I was able to get everything to work correctly by using your recommendation of removing the Workbooks(FileName).Close SaveChanges:=False line. Thank you so much.