Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Refresh inbound data sheets, then Refresh Pivot Tables
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") -- Thanks, Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Refresh inbound data sheets, then Refresh Pivot Tables
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" wrote: 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") -- Thanks, Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Refresh inbound data sheets, then Refresh Pivot Tables
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") -- Thanks, Kevin "Patrick Molloy" wrote: 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" wrote: 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") -- Thanks, Kevin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Refresh inbound data sheets, then Refresh Pivot Tables
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 "Patrick Molloy" wrote: 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" wrote: 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") -- Thanks, Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables and replacing data, then refresh it | Excel Discussion (Misc queries) | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
How can I refresh data in pivot tables lcoated in separate tab | Excel Discussion (Misc queries) | |||
Pivot Tables - Missing Data after refresh | Excel Worksheet Functions | |||
Pivot Table REFRESH Flaw -- Saves Old Data in Selection Area AFTER REFRESH | Excel Programming |