BeforeSave Crashing When Closing Workbook

[TylerDove]

Hi all,

If I save my workbook during my session the below code works perfectly, refreshing the query quietly in the background. The problem I am facing is that when I am prompted to save on close the macro will start running (I can see that it starts because of the "retrieving data" status indicator in the bottom right of the excel workbook) and then I will receive a "Microsoft Excel has stopped working" error as the whole thing crashes.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


ActiveWorkbook.RefreshAll


End Sub

I'm using Excel 2016. Can someone please help me fix this?

Thanks!

Tyler

 

[AFPathfinder]

I'd say try referencing specific objects for refresh and see if you still get that problem. You may even try just refreshing one object per test to see if a particular object is causing the crash.

 

[TylerDove]

I'd say try referencing specific objects for refresh and see if you still get that problem. You may even try just refreshing one object per test to see if a particular object is causing the crash.

Hi AFPathfinder,

That's a good suggestion! In my trouble shooting I did try that with the same results. I pulled it down to just the one critical query and still returned the same error.

My hypothesis at the moment is that Excel get's told to close before the query is complete and it panics. I tried to create a wait-time using Application.Wait, but this appears to pause all processes.

The next thing I tried was to throw Do Event after the refresh call in the BeforeSave event. For a moment it seemed to work, but I supposed I don't fully understand what Do Event is supposed to do.

Can you think of any other directions to go?

Thanks!

 

[Fluff]

How about

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
End Sub

Private Sub Workbook_Open()
Application.EnableEvents = True
End Sub

This will prevent the BeforeSave event running when you close. Although you'd need to remember to save manually before closing the workbook

 

[AFPathfinder]

If the workbook is attempting to save before the macro has finished, I don't believe the Do Events or .Wait would help with that. What if you change the event to something else; does it change the outcome? Does the same thing occur if you suppress the event and save normally?

I've had to transport data to new workbooks when I have issues with file size or odd results like yours. It may not be practical, but you might want to try moving to a newly create workbook and see if it gets better.

 

[TylerDove]

Thanks for the help Fluff and AFPathfinder!

I found the problem! My queries had been set to allow refresh in the background. I guess Excel started tripping over itself when there was a query running in the background while it wanted to close. I modified my code to set BackgroundQuery = False on close and the opposite on open.

Private Sub Workbook_BeforeClose(Cancel As Boolean)


ActiveWorkbook.Connections("Query - A").OLEDBConnection.BackgroundQuery = False


End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


ActiveWorkbook.Connections("Query - A").Refresh


End Sub


Private Sub Workbook_Open()


ActiveWorkbook.Connections("Query - B").Refresh
ActiveWorkbook.Connections("Query - A").OLEDBConnection.BackgroundQuery = True


End Sub

Thanks again!

 

[AFPathfinder]

Thanks for providing what your solution was. I'll try to keep that in mind if I run into that in the future.