1004 - Please wait until Microsoft Excel has finished refreshing the PivotTable report, and then try the command again.

[pedie]

Is there a way for VBA to wait till connection is completely refreshed and not go and execute everything?

'm getting error '1004 - Please wait until Microsoft Excel has finished refreshing the PivotTable report, and then try the command again.'

[FONT=Courier New]Sub RefreshConection()
ThisWorkbook.Connections("MainDbData").Refresh

'run this lines below only after refreshing data from database
[FONT=Courier New]    ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").ClearAllFilters
    ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").CurrentPage = "20"[/FONT]
End Sub[/FONT]

Plese help.
thanks in advance.

 

[AD_Taylor]

In that code you could do something like the following. Its not the most elegant solution but it should work.

Sub RefreshConection()
    ThisWorkbook.Connections("MainDbData").Refresh
    
resumeWait:

    On Error GoTo errWait
        'run this lines below only after refreshing data from database
        ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").ClearAllFilters
        ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").CurrentPage = "20"
    On Error GoTo 0
    
    Exit Sub
    
errWait:
    Application.Wait (Now + TimeValue("00:00:01")) 'Ask Excel to wait for 1 second
    Resume resumeWait 'Resume the code to try again
End Sub

Basically if an error occurs the code will go into the errWait handler. This then causes Excel to wait for a second before sending the code back to try again.

Hope this helps!

 

[pedie]

Hi Ad, thanks for quick reply. I tried the same way with Goto resumeWait instead of resume resumeWait...
is there any different between resume and goto? because it error even when i let excel wait for 15seconds.

Thanks again.

 

[AD_Taylor]

As far as I know Resume clears the error and resets the error handling as well, whereas GoTo will just jump back to the previous line.

 

[pedie]

Ok.thanks for clarifying on that...O tried it again but it is going into a long loop...just dont know why.
If i manually refresh it it takes around 6 seconds where are in vba it is going to 200+ seconds so i ctr + Break to stop it...

 

[AD_Taylor]

Hmm... It could be because the Wait command is halting everything, so even the refresh is stopped. I confess I haven't had much workings with the Wait command.

Try commenting out or removing that line so that the code should run like this:

Sub RefreshConection()
    ThisWorkbook.Connections("MainDbData").Refresh
    
resumeWait:

    On Error GoTo errWait
        'run this lines below only after refreshing data from database
        ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").ClearAllFilters
        ActiveSheet.PivotTables("Top5Pivot").PivotFields("Week").CurrentPage = "20"
    On Error GoTo 0
    
    Exit Sub
    
errWait:
    Resume resumeWait 'Resume the code to try again
End Sub

 

[pedie]

it still is going into endless loop...
i think there should be some work around...i tried googling but of no much help on this topic....