wait after workbook is saved

[zico8]

hi,

I need to refresh Query (PQ) after file saving.
Is there any way to check if is fully saved before executing next line?

In below code if any user would switch sheets very quickly I get run-time error.

Private Sub Worksheet_Activate()
ActiveWorkbook.Save
ActiveWorkbook.Connections("Query - Total Query").Refresh
End Sub

 

[VBA Geek]

Looks like you need to use the AfterSave event:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    If Success Then ActiveWorkbook.Connections("Query - Total Query").Refresh
End Sub

 

[zico8]

Thanks VBA Geek,

but it does not solve the problem.

If I very quikly switch between sheets (Ctrl+PgUp, Ctrl+PgDn) I get run-time error.

 

[VBA Geek]

Thanks VBA Geek,

but it does not solve the problem.

If I very quikly switch between sheets (Ctrl+PgUp, Ctrl+PgDn) I get run-time error.

try to UN-check "enable background refresh" so you wont be able to switch between tabs whilst it is refreshing

 

[zico8]

I have this option unchecked and tabs not change during refreshing but error appears.

 

[VBA Geek]

when does the error happen, during the refreshing of the query or during the file getting saved? and what is the error?

 

[zico8]

Run-time error '10044':

Method 'Save' of object '_Workbook' failed.

 

[VBA Geek]

then it has nothing to do with PQ.. your file is not getting saved. probably has become corrupted I guess

 

[Jaafar Tribak]

hi,

I need to refresh Query (PQ) after file saving.
Is there any way to check if is fully saved before executing next line?

In below code if any user would switch sheets very quickly I get run-time error.

Private Sub Worksheet_Activate()
ActiveWorkbook.Save
ActiveWorkbook.Connections("Query - Total Query").Refresh
End Sub

See if this works :

Private Sub Worksheet_Activate()
    [COLOR=#ff0000][B]ThisWorkbook[/B][/COLOR].Save
    [B][COLOR=#ff0000]ThisWorkbook[/COLOR][/B].Connections("Query - Total Query").Refresh
End Sub