Thursday, 27 July 2017
VBA - Excel sometimes fails to close properly
Answer
Answer
I have a macro in outlook, which helps runs statistics in an excel workbook.
However sometimes it fails to close it properly, and ends up ruining the process, since the workbook is open still, when i run it next time.
This is my method for closing it.
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(strpath)
...
xlWB.Save
xlWB.Close savechanges:=True
xlApp.Quit
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
From my understanding it should do it.
Answer
Did you turn the displayalerts off? Use:
xlApp.DisplayAlerts = False
after you instanciate the Excel application. That prevents Excel from asking for user input ("are you really sure you want to ...?). Such popup could prevent Excel from closing.
Happened to me more than once on an invisable application.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment