Cloud and Microsoft technologies enthusiast architect in Switzerland RSS 2.0
# Thursday, January 25, 2007

In this project, I had to work on an Excel file getting its data from a database in order to generate a document usable for the end-user. The problem was that a lot of pivot tables were included everywhere in the document making the manual update of these tables really painful. So, my problem was to automate the process of this update in order to refresh all the pivot tables in all the worksheets. In finally wrote a little piece of code that could be triggered either manually or automatically after an event, for example.

As it could be also useful for you, here is the VBA code :

' Scans all the Pivot tables contained in the file and refresh the data
Public Sub RefreshPivotTables()
   Dim aWorksheet As Worksheet
   Dim aPivotTable As PivotTable

   ' Find the pivot tables in all worksheets
   For Each aWorksheet In Worksheets
      ' If a worksheet contains more than one pivot table, refresh all of them
      For Each aPivotTable In aWorksheet.PivotTables
         ' Updates also the range of data to be sure that the pivot table uses all the data
         ' If the range of data never changes, it is not necessary to call the sub below.
         RefreshPivotTableRange aPivotTable
         aPivotTable.PivotCache.Refresh
      Next aPivotTable
   Next aWorksheet
End Sub

' Adapts a pivot table data range
Private Sub RefreshPivotTableRange(aPivotTable As PivotTable)
On Error Resume Next
   Dim newRange As Range
   Dim WorksheetName As String

   ' Get the worksheet name
   WorksheetName = Left(aPivotTable.SourceData, InStr(aPivotTable.SourceData, "!") - 1)
   ' The GetDataRangeForWorksheet is a sub that returns the new range of data in the given worksheet.
   Set newRange = GetDataRangeForWorksheet(Worksheets(WorksheetName))
   aPivotTable.SourceData = WorksheetName & "!" & newRange.Address(ReferenceStyle:=xlR1C1)
End Sub

Thursday, January 25, 2007 1:11:51 PM (GMT Standard Time, UTC+00:00)  #    Comments [0] -
Programming | Technical | VBA
All comments require the approval of the site owner before being displayed.
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):

Live Comment Preview
Google Cloud Platform Certified Professional Cloud Architect
Ranked #1 as
French-speaking SharePoint
Community Influencer 2013
Navigation
Currently Reading :
I was there :
I was there :
I was exhibiting at :
I was there :
I was a speaker at :
I was a speaker at :
I was a speaker at
(January 2013 session):
I was a speaker at :
I was a speaker at :
United Nations (UN) SharePoint Event 2011
I was a speaker at :
I was there !
I was there !
I was there !
I was there !
Archive
<May 2022>
SunMonTueWedThuFriSat
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2022
Yves Peneveyre
Sign In
Statistics
Total Posts: 290
This Year: 0
This Month: 0
This Week: 0
Comments: 20
Themes
Pick a theme:
All Content © 2022, Yves Peneveyre
DasBlog theme 'Business' created by Christoph De Baene (delarou)