CODEWITHSUNDEEP
excelvbapowerquery
John M
John M

Reputation: 217

Excel: disable prompt to save changes on close

I have an excel workbook with macros. I am using a "welcome" sheet to ensure the user enables macros according to the technique illustrated here (http://www.vbaexpress.com/kb/getarticle.php?kb_id=379). Everything works fine until I extend the workbook with Power Query. I created a query to read and transform an Excel table located in the same workbook. The transformed table is then loaded in a new sheet in the same workbook. Now I need to refresh this transformed table with VBA whenever the user saves the workbook. I am using this code for the refresh:

ThisWorkbook.Worksheets("Sheet2").ListObjects("MyTable").QueryTable.Refresh BackgroundQuery:=False

Unfortunately, if the user saves the workbook and closes the workbook immediatly after the saving, a prompt appears to save the changes, even if no changes have been made. For some reason, the refresh of the QueryTable sets ThisWorkbook.Save property to False, and therefore Excel prompts to save the unsaved changes on close.

How can I get rid of the prompt on close if no changes have been made?

Thank you.

Edit:

This is the code of the BeforeSave Event:

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

'Query
ThisWorkbook.Worksheets("Sheet2").ListObjects("MyTable").QueryTable.Refresh BackgroundQuery:=False

'Turn off events to prevent unwanted loops
Application.EnableEvents = False

'Call customized save routine and set workbook's saved property to true
'(To cancel regular saving)
 Call CustomSave(SaveAsUI)
Cancel = True

'Turn events back on an set saved property to true
Application.EnableEvents = True
ThisWorkbook.Saved = True

End Sub

Upvotes: 0

Views: 448

Answers (2)

Notus_Panda
Notus_Panda

Reputation: 2810

In the BeforeClose event, you can save one additional time (just in case the user did not save on their own) and then close with the SaveChanges as False:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Save
    ThisWorkbook.Close SaveChanges:=False
End Sub

If your line is in the BeforeSave event, that should be fine still.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.Worksheets("Sheet2").ListObjects("MyTable").QueryTable.Refresh BackgroundQuery:=False
End Sub

EDIT If you don't want the save the user's changes without them hitting the save button use:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Me.Close SaveChanges:=False
End Sub

If you want to save changes the user makes then use:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Me.Saved = True Then
        ThisWorkbook.Save
    End If
    ThisWorkbook.Close SaveChanges:=False
End Sub

in tandem with:

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.Saved = True
End Sub

In the sheets you want to keep track of. That should cover all the changes made.

Upvotes: 3

Black cat
Black cat

Reputation: 6271

Pls. try this:

Place your code in the Workbook_BeforeSave() Sub and add this to the module:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
Me.Saved = True
End If
End Sub

If the save was successful then it set the Saved property of the workbook as it was not modified after the last save.

Upvotes: 1

Related Questions