Save as xlsx then close with out closing all workbooks

Question

I want to save the active workbook as xlsx and then close it without closing all the open excel workbooks. This code works but closes all the open excel files.

If I remove Application.Quit and leave ThisWorkbook.Close it will work only if I have more then one workbook open but if I only have the one workbook open it will close but leave a blank excel window open.

Sub SaveAsXlsx()

Dim varResponse As Variant
varResponse = MsgBox("Save As xlsx Removing Macros & Then Closes The Workbook", vbYesNo, "Save As xlsx")
If varResponse <> vbYes Then Exit Sub

Application.DisplayAlerts = False
Dim FilePath As String
FilePath = ThisWorkbook.FullName
FilePath = Left(FilePath, Len(FilePath) - 5) & " To Review" & ".xlsx"

ThisWorkbook.SaveAs Filename:=FilePath, FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False

'Enter Anything to Happen on xlsx Book Here
Range("A1").Select


ThisWorkbook.Save

FilePath = Application.ActiveWorkbook.FullName
MsgBox "Saved Review Copy As" & Chr(10) & Chr(10) & FilePath, , "Saved!"

Application.Quit
ThisWorkbook.Close


End Sub

I'm a bit confused. After closing ThisWorkbook, you want to leave Excel open if Excel.Workbooks.Count > 0, but you want to close the application if Excel.Workbooks.Count = 0? — FreeMan
@FreeMan I'm reading it that way as well. Seems like a If Application.Workbooks.Count = 0 Then Application.Quit might be the right way to go here? — JNevill
That works but I had to change it to If Application.Workbooks.Count = 1 Then Application.Quit — luke

FreeMan FreeMan · Accepted Answer · 2019-05-01T13:34:00

In order to close Excel if your code is closing the last open workbook, but leave it open if there are other workbooks open, you'll need to check the Application.Workbook.Count property, like this:

Sub SaveAsXlsx()

  Dim varResponse As Variant
  varResponse = MsgBox("Save As xlsx Removing Macros & Then Closes The Workbook", vbYesNo, "Save As xlsx")
  If varResponse <> vbYes Then Exit Sub

  Application.DisplayAlerts = False
  Dim FilePath As String
  FilePath = ThisWorkbook.FullName
  FilePath = Left(FilePath, Len(FilePath) - 5) & " To Review" & ".xlsx"

  ThisWorkbook.SaveAs Filename:=FilePath, FileFormat:= _
                      xlOpenXMLWorkbook, CreateBackup:=False

'Enter Anything to Happen on xlsx Book Here
  Range("A1").Select


  ThisWorkbook.Save

  FilePath = Application.ActiveWorkbook.FullName
  MsgBox "Saved Review Copy As" & Chr(10) & Chr(10) & FilePath, , "Saved!"

  ThisWorkbook.Close

  If Excel.Application.Workbooks.Count = 0 Then
    Application.Quit
  End If

End Sub

Save as xlsx then close with out closing all workbooks

1 Answers