Excel PivotTables Auto refresh on interval

[PerK]

Ik heb nu een workbook met 62 Sheets. Vrijwel alle sheets bevatten een PivotTable die hun data uit de Sheet DATA halen (deze data word om de 30 minuten gerefreshed uit Exact Globe).

Bij het openen van dit bestand(workbook) refreshed hij automatisch alle data en refreshed hij alle PivotTables. Echter, als hij na 30 minuten(de ingestelde tijd interval) opnieuw de (Raw)Data refreshed dan worden de PivotTables niet meegenomen met updaten.

Nu heb ik op internet een VBA code gevonden:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("DATA").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Ik heb voor elke sheet en pivottable de regel:

Worksheets("DATA").PivotTables("PivotTable1,2, 3 etc!").PivotCache.Refresh

Deze moet als het goed is alle in de code aangegeven pivottables refreshen. Dat doet hij ook (wat aanzienlijk veel langer duurt dan het refreshen van de pivottables bij het openen van het bestand). Maar hij geeft na ong. 23 code regels een Runtime error....

Is er geen andere manier of code om alle PivotTables te refreshen d.m.v. een time interval?

[Lustucru]

en wat is die runtime error dan precies?

Ik heb voor elke sheet en pivottable de regel:

Worksheets("DATA").PivotTables("PivotTable1,2, 3 etc!").PivotCache.Refresh

En dat kan sowieso korter:

code:

    Dim pc As PivotCache
    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next pc

[ Voor 78% gewijzigd door Lustucru op 11-10-2013 14:13 ]

[PerK]

En dat kan sowieso korter:

code:

    Dim pc As PivotCache
    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next pc

Mag ik vragen wat deze code precies doet? Je beschouwd de PivotTables nu als pc's in deze code?

[Lustucru]

Nope. 'pc' is de -willekeurige naam- van de variabele die door de foreach lus succcesievelijk verwijst naar alle draaitabellen in het workbook. Vervang 'pc' door 'GeelgerandeSnuitkever' en het werkt precies hetzelfde.

offtopic:
Het is wel zo beleefd om eerst antwoord te geven op een vraag voordat je twee nieuwe stelt

[PerK]

Ik wil nog even mededelen dat de volgende code:

Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc

Precies op het moment dat de 'auto' refresh klaar is alle PivotTables refreshed. Dit gaat dusdanig snel dat de gerefreshde data nog niet compleet in Excel staat. Als je report filters op je PivotTable heb kunnen die wel eens resetten(eerst geven ze waarde '0' aan en na de volgende refresh weer 'All').Erg lastig als je ca. 65 sheets met pivottables heb. Dit was bij mij het geval.
Ik heb daarom de volgende codes gebruikt om de PivotTables om de zoveel tijd te refreshen. (5 minuten na de data refresh)


Code in ThisWorkbook

Private Sub Workbook_Open()
Call Delay
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Jan
End Sub

In een Standard Module:

Public vartimer As Variant
Const TimeOut = 30 'in minutes

Sub Kees()

Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCaches
PC.Refresh
Next PC

Call Delay
End Sub

Sub Delay()
vartimer = Format(Now + TimeSerial(0, TimeOut, 0), "hh:mm:ss")
If vartimer = "" Then Exit Sub
Application.OnTime TimeValue(vartimer), "Kees"
End Sub

Sub Jan()
On Error Resume Next
Application.OnTime earliesttime:=vartimer, _
procedure:="Kees", schedule:=False
On Error GoTo 0
End Sub