RefreshAll BeforeSave

user_19191551 0
Viewed 67

I am using power query to combine several tables together and output some info to another table, all within the same workbook. I would like to ensure the output table is always up-to-date when the file is saved and don't want to depend on ppl using the 'Refresh All' button. Ideally I would have the queries update when the file is being saved. For each query I have disabled the 'Enable background refresh' option in the query properties. I then have added the following vba code to ThisWorkbook:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.RefreshAll
End Sub

This does cause the queries to be updated before the file is saved. However if a user clicks on the save button (causing the queries to update & file saved), and then closes the file they are prompted to save the file again as if something has been edited... I have tried adding a wait command to the BeforeSave method after the refresh however the same behaviour occurs. We could of course just save it again or close without saving (since it was saved when we first hit the save button) however both are not ideal (file is on a network drive, so even though it is only a couple MB it takes several seconds to save, and not a fan of closing without saving).

Any tips for stopping Excel from asking us to save a workbook that we just saved?

Edit

From comments and suggested answers it seems that BeforeSave can be inconsistent. I did want to allow users to be able to close the workbook without saving so wanted to avoid using BeforeClose(for example a user made large changes to the workbook and wanted to undo everything). By manually setting the workbook saved state to true in Aftersave seems to have resolved the issue; atleast in my testing so far (even though it should already be true since it just saved?).

Option Explicit

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

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.RefreshAll
End Sub
powerquery vba excel
1 Answers

Workbook.RefreshAll

  • It seems like the BeforeSave isn't always working as expected so your feedback is most welcome.
Option Explicit

Private DoNotBeforeSave As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Me
        If Not .Saved Then
            .RefreshAll
            DoNotBeforeSave = True
            .Save
            Debug.Print "Just refreshed and saved via 'BeforeClose'. Closing!"
        Else
            Debug.Print "No action taken via 'BeforeClose'. Closing!"
        End If
    End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not DoNotBeforeSave Then
        With Me
            If Not .Saved Then
                .RefreshAll
                Debug.Print "Just refreshed and saved via 'BeforeSave'!"
            Else
                Cancel = True
                Debug.Print "No action taken via 'BeforeSave'!"
            End If
        End With
    End If
End Sub
user_98140690
Related