Code to Refresh inbound data sheets, then Refresh Pivot Tables

[AFSSkier]

I'm trying 1st refresh the data sheets connected to an outside database.
Then refresh all of the end user's custom pivot tables.

I've tried a few different types of code. But I only get the data sheets to
refresh & not the pivot tables. If I seperate the code into 2 different
CmdButtons, I can get them to work. But I only what one CmdButton.

Private Sub CmdRefreshAll_Click()
'Refresh data sheets for outside data sources
ActiveWorkbook.RefreshAll
'or something else here to refresh only inbound data sheets.

MsgBox "All Data Sheets are updated, click ok to update Pivot tables"

'Refresh Pivot tables
Dim Wks As Worksheet
Dim pvtTable As PivotTable

Application.ScreenUpdating = False

For Each Wks In ActiveWorkbook.Worksheets
For Each pvtTable In Wks.PivotTables
pvtTable.PivotCache.Refresh
Next pvtTable
Next Wks

MsgBox "All Data Sheets & Pivot Tables are updated"

End Sub

I also tried calling a 2nd macro:

Private Sub CmdRefreshAll_Click()
'Refresh data sheets for outside data sources
ActiveWorkbook.RefreshAll
MsgBox "All Data Sheets are updated, click ok to update Pivot tables"
Call RefreshPivots
End Sub

Private RefreshPivots()
'Refresh Pivot tables
Dim Wks As Worksheet
Dim pvtTable As PivotTable

Application.ScreenUpdating = False

For Each Wks In ActiveWorkbook.Worksheets
For Each pvtTable In Wks.PivotTables
pvtTable.PivotCache.Refresh
Next pvtTable
Next Wks
MsgBox "All Data Sheets & Pivot Tables are updated"
End Sub

I'd also like to add the date the sheets were refreshed, in the same code.
I assume it would go something like this.

dt.Range("Z1") = "Refresh date: " & Format(Now(), "mm/dd/yyyy")

 

[Patrick Molloy]

your loop looks good to me. Switch screen updating back to TRUE
What happens if you STEP (F8) through?
nothing looks wrong to me.

 

[AFSSkier]

Patrick,

When I STEP (F8) the code the "ActiveWorkbook.RefreshAll" refreshes ALL
sheets, including the Pivots with the old data (as expected). The
"PivotCache.Refresh" loop refreshes the Pivots with the new data (as
expected).

The "Application.ScreenUpdating" didn't seem to matter TRUE or FALSE.

But if I run it from the CmdButton or F5, I only get the 1st "RefreshAll".
It only updates the data sheets w/new & the Pivots w/old data. It's like it
needs some kind of delay before it executes the "PivotCache.Refresh". I
assumed the 1st MsgBox cmd would take care of that. That's why I also tried
a delay by calling a 2nd sub. In both cases, it's going all the way through
the code. Because I get both MsgBoxes.

Also, how would I include a following into my Dim/For/Next?
dt.Range("Z1") = "Refresh date: " & Format(Now(), "mm/dd/yyyy")

 

[AFSSkier]

I found my problem. I disabled the "Enable Background Refresh" in the
Connection Properties. Now, even my original sub code works.

It seems to be overriding any code to disable it, including any Refresh
BackgroundQuery = False

Patrick, thank you for responding to my post,
Kevin