Draaitabel automatisch verversen met een macro bij het openen MS Office Excel Werkboek
15 okt
Het is mogelijk een draaitabel of meerdere draaitabellen in Excel automatisch te vernieuwen met een Macro door gebruik te maken van de workbook_open procedure. Dit kan bijvoorbeeld gewenst zijn indien je een overzicht maakt gekoppeld aan een externe gegevensbron dat naar eindgebruikers gestuurd dient te worden.
Code uitvoeren bij uitvoer van Excel (workbook_open)
Open de werkmap waar je een actie wilt laten uitvoeren bij het openen. Gebruik de toetscombinatie ALT+F11 om naar Microsoft Visual Basic te gaan (Macro’s zijn Visual Basic scripts die worden uitgevoerd). Ga naar ThisWorkbook in het VBA Project van je Excel bestand. Dubbelklik op ThisWorkbook om naar de code editor te gaan. Hier kun je een procedure maken genaamd workbook_open. Bij het uitvoeren van Excel wordt de code in deze procedure automatisch uitgevoerd (indien macro’s aan staan voor het bestand).
Sub workbook_open()
MsgBox "Deze code wordt uitgevoerd bij openen"
End Sub
Automatisch verversen van alle draaitabellen in een bestand
Met onderstaande script kun je alle werkbladen in een bestand doorlopen en kijken of er draaitabellen in betreffende werkblad staan. Indien dit zo is worden de tabellen ververst. Dit ongeacht de naam van het tabblad, de naam van de draaitabel of de volgorde waarin het geheel is opgebouwd. Door de code in de zojuist gemaakte workbook_open procedure te plaatsen, worden alle draaitabellen ververst bij het openen van het werkboek.
Sub workbook_open()
'Auteur: Rob Cilissen
'Datum: 20081014
'Script doorloopt alle tabbladen op zoek naar alle draaitabellen en ververst deze.
Dim I, X As Integer
For I = 1 To Sheets.Count
If Sheets(I).PivotTables.Count > 0 Then
For X = 1 To Sheets(I).PivotTables.Count
Sheets(I).PivotTables(X).PivotCache.Refresh
Next X
End If
Next I
MsgBox "Alle draaitabellen in dit bestand zijn voorzien van nieuwe data."
End Sub
Let op dat macro’s uitgevoerd kunnen worden in je bestand. Zie ook: Macro wordt niet uitgevoerd in MS Office Excel 2007.
mmm en wat als je draaitabel op een ander werkblad staat dan de data???
En hoe ververs je de draaitabel wanneer deze op een ander sheet staat???
@André:
Ofwel je hebt een draaitabel met een externe gegevensbron als dataset of je draaitabel heeft de data in een tabblad staan in het bestand. In beide gevallen beschrijft dit artikel het verversen van de draaitabel en niet de dataset. Hiermee bedoel ik dus het opnieuw genereren van de draaitabel op basis van die dataset. Het script zoals hierboven genoemd werkt voor beide.
Zoals hierboven omschreven doorloopt het script alle bladen en ververs/vernieuwd alle draaitabellen die worden gevonden. Het maakt dus niet uit op welk blad de draaitabel staat.
Succes!
Hallo Rob,
Ik heb het script gebruikt en het werkt perfect. Nu heb ik echter ook een aantal database queries en daarmee zou ik ook graag alle tabs tegelijk vernieuwen. Is dit mogelijk?
Beste Rob,
Ik heb via via dit script gevonden en toegpast, werkt uitstekend. Nu heb ik hem in een excel sheet toegepast welke ook een x aantal database velden (op verschillende tabbladen) ververst op het moment van openen.
Is het mogelijk om een soort vertraging in te bouwen zodat eerst de database tabbladen ververst worden en dat daarna pas het script in werking gaat? Nu ververst hij eerst de draaitabellen en daarna pas de database bladen, en staan mijn nieuwe gegevens er nog niet in.
Met vriendelijke groet,
Richard
Hoi Richard,
Ik heb al een tijd niets met vba gedaan. Voor mijn gevoel zou moeten kunnen wat je vraagt, maar ik kan je op dit moment helaas niet helpen.
Groet,
Rob
Waarom zou je dit niet simpel weg doen door:
Sub Macro_Alles_vernieuwen()
ActiveWorkbook.RefreshAll
End Sub
Deze Macro kun je vervolgens aan een knop koppelen. Dit beandwoord tevens ook de vraag van Richard. Hij zal er nu niks aan hebben want ik zie ook dat dit een hele oude bericht is maar zal denk ik wel de volgende gaan helpen die hiernaar opzoek is.