VBA to Refresh Connections

[happyhungarian]

Hi, I have code that automatically does a RefreshAll when a worksheet is activated. My issue though is that I need the refreshes to happen in a certain order since pivotables are based off of connection refreshes. I would need all connections to be refreshed first and then all pivotables to be refreshed.

Thanks!

Jesse

 

[happyhungarian]

I've tried the following but it's still not working


Private Sub Worksheet_Activate()


Call RefreshConsolidation


DoEvents


Call AllWorkbookPivots


End Sub








The RefreshConsolidation code is:

Sub RefreshConsolidation()


ActiveWorkbook.Connections("Query - Append2").Refresh

End Sub

 

[John_w]

What isn't working? Do you get any errors? What type of connections are they - ODBC, OLEDB, WORKSHEET, or another type?

 

[happyhungarian]

I thought the DoEvents would pause the macro long enough for the ActiveWorkbook.Connections("Query - Append2").Refresh to finish before the PivotTables refreshed but it's not. There are no errors. So the Pivots refresh off of the old data on the Append2 rather than the new. The Append2 query is simply an append operation of multiple tables on my worksheet that I have turned into Connections. So everything is housed within the workbook. I'm wondering if I need to add a backgroundquery=false operation somewhere in the code???

 

[happyhungarian]

I figured it out... I just disabled Background Refresh on my Append2 query