Excel VBA: Refresh All and Save

Question

I have a very very simply task of wanting to refresh all on the workbook (which only contain 3 tables with external data) and save it every 5 minutes. I currently have the workbook running an auto save every 5 minutes via:

Sub SaveWb()
    ThisWorkbook.Save
    Application.OnTime Now + TimeValue("00:05:00"), "SaveWb"
End Sub

&

Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:05:00"), "SaveWb"
End Sub

And the tables refreshes using the connection properties of background auto refresh in 5 minutes. However, I kept running into the problem where excel would prompt:

"This will cancel a pending data refresh. Continue? [OK] [Cancel]"

I wonder if I can have the refresh running in VBA as well so they can run nicely without holding up the refreshes. While this seem like a easy job with the wb.RefreshAll function but my non-existant vba skill does not allow me to put them together....

Any help is much appreciated!

Jerry Jerry · Accepted Answer · 2017-10-12T21:11:56

I experimented a little and the by editing part of the code and it now works:

Sub SaveWb()
    ThisWorkbook.RefreshAll
    ThisWorkbook.Save
    Application.OnTime Now + TimeValue("00:05:00"), "SaveWb"
End Sub

No more "This will cancel a pending data refresh. Continue?"

Excel VBA: Refresh All and Save

2 Answers