Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
Windows XP, Excel 2002 SP 3
I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
From VBA's help for .refreshall:
Remarks Objects that have the BackgroundQuery property set to True are refreshed in the background. Maybe you want to change property to true. Lou Sanderson wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
'/-------------------------------------/
Sub PTRefresh() 'Refresh all pivot tables in this workbook one at a time ' Dim iSheets As Integer, x As Integer Dim iPivot As Integer, strCurrentSheet As String On Error GoTo Exit_PTRefresh 'Count number of sheets in workbook iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.name If Windows.Count = 0 Then GoTo Exit_PTRefresh For x = 1 To iSheets 'go to a worksheet to refresh pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'refresh all pivot tables on this worksheet one at a time For iPivot = 1 To ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iPivot).RefreshTable Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that you were originally at Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate Exit_PTRefresh: Application.DisplayAlerts = True End Sub '/-------------------------------------/ HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Lou Sanderson" wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
Background refresh is set to true for the query the pvt table is based off of
- didnt see such an option for the pvt table itself. Its strange that the ..refreshall is not working for me b/c most of the posts on this topic say to do that... "Dave Peterson" wrote: From VBA's help for .refreshall: Remarks Objects that have the BackgroundQuery property set to True are refreshed in the background. Maybe you want to change property to true. Lou Sanderson wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
Rightclick on the pivottable|Table Options
Bottom right corner has an option for background Query. Uncheck it. Or get them all with a macro: Option Explicit Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.PivotCache.BackgroundQuery = False Next PT Next wks End Sub Lou Sanderson wrote: Background refresh is set to true for the query the pvt table is based off of - didnt see such an option for the pvt table itself. Its strange that the .refreshall is not working for me b/c most of the posts on this topic say to do that... "Dave Peterson" wrote: From VBA's help for .refreshall: Remarks Objects that have the BackgroundQuery property set to True are refreshed in the background. Maybe you want to change property to true. Lou Sanderson wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
Ok, I see what you mean. That option is greyed out.
The pvt table is based on an external data query on a seperate page - so the data source for the pvt table is an excel data range, vice external data. "Dave Peterson" wrote: Rightclick on the pivottable|Table Options Bottom right corner has an option for background Query. Uncheck it. Or get them all with a macro: Option Explicit Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.PivotCache.BackgroundQuery = False Next PT Next wks End Sub Lou Sanderson wrote: Background refresh is set to true for the query the pvt table is based off of - didnt see such an option for the pvt table itself. Its strange that the .refreshall is not working for me b/c most of the posts on this topic say to do that... "Dave Peterson" wrote: From VBA's help for .refreshall: Remarks Objects that have the BackgroundQuery property set to True are refreshed in the background. Maybe you want to change property to true. Lou Sanderson wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
OK, I'm close now
I slightly modified your code to: Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.refreshtable Next PT Next wks End Sub and it almost works. Not every tab on my spreadsheet has a pvt table, so this errors if it comes across a page w/o a pvt table. how can I account for that? thanks for your help. "Dave Peterson" wrote: Rightclick on the pivottable|Table Options Bottom right corner has an option for background Query. Uncheck it. Or get them all with a macro: Option Explicit Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.PivotCache.BackgroundQuery = False Next PT Next wks End Sub Lou Sanderson wrote: Background refresh is set to true for the query the pvt table is based off of - didnt see such an option for the pvt table itself. Its strange that the .refreshall is not working for me b/c most of the posts on this topic say to do that... "Dave Peterson" wrote: From VBA's help for .refreshall: Remarks Objects that have the BackgroundQuery property set to True are refreshed in the background. Maybe you want to change property to true. Lou Sanderson wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
Gary,
I tried this - didnt get any result. Nothing happened. Used the debug to step through, and it goes fine up until * (below) and then jumps from that line to the error handling. I'm not sure why... "Gary L Brown" wrote: '/-------------------------------------/ Sub PTRefresh() 'Refresh all pivot tables in this workbook one at a time ' Dim iSheets As Integer, x As Integer Dim iPivot As Integer, strCurrentSheet As String On Error GoTo Exit_PTRefresh 'Count number of sheets in workbook * iSheets = ActiveWorkbook.Sheets.Count 'remember current sheet strCurrentSheet = ActiveSheet.name If Windows.Count = 0 Then GoTo Exit_PTRefresh For x = 1 To iSheets 'go to a worksheet to refresh pivot tables Sheets(x).Activate 'turn warning messages off Application.DisplayAlerts = False 'refresh all pivot tables on this worksheet one at a time For iPivot = 1 To ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iPivot).RefreshTable Next 'turn warning messages on Application.DisplayAlerts = True Next 'return to worksheet that you were originally at Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate Exit_PTRefresh: Application.DisplayAlerts = True End Sub '/-------------------------------------/ HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Lou Sanderson" wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
I don't have any other suggestions.
Good luck, Lou Sanderson wrote: Ok, I see what you mean. That option is greyed out. The pvt table is based on an external data query on a seperate page - so the data source for the pvt table is an excel data range, vice external data. "Dave Peterson" wrote: Rightclick on the pivottable|Table Options Bottom right corner has an option for background Query. Uncheck it. Or get them all with a macro: Option Explicit Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.PivotCache.BackgroundQuery = False Next PT Next wks End Sub Lou Sanderson wrote: Background refresh is set to true for the query the pvt table is based off of - didnt see such an option for the pvt table itself. Its strange that the .refreshall is not working for me b/c most of the posts on this topic say to do that... "Dave Peterson" wrote: From VBA's help for .refreshall: Remarks Objects that have the BackgroundQuery property set to True are refreshed in the background. Maybe you want to change property to true. Lou Sanderson wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
When I ran this code against a workbook that had worksheets with no pivottables,
it ran successfully. Lou Sanderson wrote: OK, I'm close now I slightly modified your code to: Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.refreshtable Next PT Next wks End Sub and it almost works. Not every tab on my spreadsheet has a pvt table, so this errors if it comes across a page w/o a pvt table. how can I account for that? thanks for your help. "Dave Peterson" wrote: Rightclick on the pivottable|Table Options Bottom right corner has an option for background Query. Uncheck it. Or get them all with a macro: Option Explicit Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.PivotCache.BackgroundQuery = False Next PT Next wks End Sub Lou Sanderson wrote: Background refresh is set to true for the query the pvt table is based off of - didnt see such an option for the pvt table itself. Its strange that the .refreshall is not working for me b/c most of the posts on this topic say to do that... "Dave Peterson" wrote: From VBA's help for .refreshall: Remarks Objects that have the BackgroundQuery property set to True are refreshed in the background. Maybe you want to change property to true. Lou Sanderson wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
refreshing pivot tables with a macro
Yes, you are right.
I had a problem, but it was something else, the code worked correctly. Thank you for your help Dave. -Lou "Dave Peterson" wrote: When I ran this code against a workbook that had worksheets with no pivottables, it ran successfully. Lou Sanderson wrote: OK, I'm close now I slightly modified your code to: Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.refreshtable Next PT Next wks End Sub and it almost works. Not every tab on my spreadsheet has a pvt table, so this errors if it comes across a page w/o a pvt table. how can I account for that? thanks for your help. "Dave Peterson" wrote: Rightclick on the pivottable|Table Options Bottom right corner has an option for background Query. Uncheck it. Or get them all with a macro: Option Explicit Sub testme() Dim wks As Worksheet Dim PT As PivotTable For Each wks In ActiveWorkbook.Worksheets For Each PT In wks.PivotTables PT.PivotCache.BackgroundQuery = False Next PT Next wks End Sub Lou Sanderson wrote: Background refresh is set to true for the query the pvt table is based off of - didnt see such an option for the pvt table itself. Its strange that the .refreshall is not working for me b/c most of the posts on this topic say to do that... "Dave Peterson" wrote: From VBA's help for .refreshall: Remarks Objects that have the BackgroundQuery property set to True are refreshed in the background. Maybe you want to change property to true. Lou Sanderson wrote: Windows XP, Excel 2002 SP 3 I have a simple macro that I run to refreshs all my external datasources in my spreadsheet. Recently I've added some pivot tables as well. I would like my macro to refresh the pivot tables if possible - currently it does not. Any suggestions? Here is my macro sub macro1() application.displayalerts = false sheets("sheet1").select range("a1").select activeworkbook.refreshall application.displayalerts = true msgbox("refresh complete") end sub Thanks, Lou Sanderson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relinking Pivot Tables to Main Table | Excel Worksheet Functions | |||
pivot tables not refreshing correctly | Excel Worksheet Functions | |||
Pivot Tables | Excel Discussion (Misc queries) | |||
Data Cubes and Pivot Tables | Excel Worksheet Functions | |||
Multiple Pivot tables from One selection | Excel Discussion (Misc queries) |