[RESOLVED] Closing Excel with VBA

[yarsec]

I need some help on this, I have looked around and found some posts, but I can't get it to work.

I am opening an Excel file with windows scheduler on our server. Once the file is open, it automatically updates and I have written a VBA macro to save and close it. My problem is that Excel itself does not close, just the workbook closes. How do I get Excel to fully close?

Here is my vba... Thanks!!

Public Sub CloseAll()
 
Dim Wb As Workbook
SaveAll
For Each Wb In Workbooks
    If Wb.Name <> ThisWorkbook.Name Then
        Wb.Close savechanges:=True
        Set Wb = Nothing
    End If
    
Next Wb
ThisWorkbook.Close savechanges:=True
Set Wb = Nothing
 
End Sub

[Sahir]

Application.Quit

[yarsec]

Where do I put this in my code? Do I use this exact syntax?

[Hack]

Excel VBA question moved to Office Development

[yarsec]

Ok, I put the application.quit at the bottom of my code. It still does not work, I am 2 day newbie at VBA, help?!

VBA code...

Public Sub CloseAll()

Dim Wb As Workbook
SaveAll
For Each Wb In Workbooks
If Wb.Name <> ThisWorkbook.Name Then
Wb.Close savechanges:=True
Set Wb = Nothing
End If
Next Wb
ThisWorkbook.Close savechanges:=True
Set Wb = Nothing

application.quit

End Sub

[Sahir]

Where do I put this in my code? Do I use this exact syntax?

Yes you use the exact syntax. You can call Application.Quit as soon as you are done with closing the workbook.

[Sahir]

Ok, I put the application.quit at the bottom of my code. It still does not work, I am 2 day newbie at VBA, help?!

Where is this code? Is it in an Excel workbook or somewhere else?

[RobDog888]

You you cant close the current workbook and still try to execute vba code as its no longer open. Save the current workbook instead of closing it. Then quit the application.

ThisWorkbook.Save
    Application.Quit
End Sub

[yarsec]

I think its in an Excel workbook. From Excel I went to Tools-> Macro -> Visual Basic Editor and inserted two modules (saveall and closeall).

[RobDog888]

Public Sub CloseAll()
 
Dim Wb As Workbook
SaveAll
For Each Wb In Workbooks
    If Wb.Name <> ThisWorkbook.Name Then
        Wb.Close savechanges:=True
        Set Wb = Nothing
    End If
    
Next Wb
ThisWorkbook.Save
Application.Quit
 
End Sub

[Sahir]

I think its in an Excel workbook. From Excel I went to Tools-> Macro -> Visual Basic Editor and inserted two modules (saveall and closeall).

If it's in an Excel workbook it should work. Anyway, I am off to bed now. Good night.

[yarsec]

Robdog:

What about this? It seems to work and its simple.

Inserted just 1 module:

Public Sub CloseAll()

ThisWorkbook.Save
Application.Quit

End Sub

Then put the following into Thisnotebook...

Private Sub Workbook_Open()
Run "CloseAll"
End Sub

[RobDog888]

No, as it will only save the "ThisWorkbook" instance and not any others.

Public Sub CloseAll()
 
Dim Wb As Workbook
SaveAll
For Each Wb In Workbooks
    wb.Save
Next
Application.Quit
End Sub

[RobDog888]

Why do you need it to save and close all workbooks upon the Workbook_Open event?

[yarsec]

Thanks! It looks like its working!