Actualisation de tous les tableaux croisés dynamiques dans mon classeur excel avec une macro

Ce code VBA permettra d'actualiser tous les tableaux croisés dynamiques et graphiques dans le classeur.

Sub RefreshAllPivotTables()

Dim PT As PivotTable
Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets

        For Each PT In WS.PivotTables
          PT.RefreshTable
        Next PT

    Next WS

End Sub

Un autre non-programatic option est:

• Clic droit sur chaque tableau croisé dynamique
• Sélectionnez options de la Table
• Cochez la Actualisation à l'ouverture' option.
• Cliquez sur le bouton OK

Cela actualiser le tableau croisé dynamique à chaque fois que le classeur est ouvert.

Dans certaines circonstances, vous pourriez vouloir faire la différence entre un tableau croisé dynamique et de ses PivotCache. Le Cache a sa propre méthode d'actualisation et de ses propres collections. Nous avons donc pu avoir actualisé tous les PivotCaches à la place des Tableaux croisés dynamiques.

La différence? Lorsque vous créez un Tableau croisé dynamique vous est demandé si vous voulez sur la base d'un tableau précédent. Si vous dites non, ce Tableau croisé dynamique obtient son propre cache et double la taille de la source de données. Si vous dites oui, vous gardez votre Classeur petit, mais vous ajouter à une collection de Tableaux croisés dynamiques qui partagent une même cache. L'ensemble de la collection sera mise à jour lors de l'actualisation d'une seule de Tableau croisé dynamique dans cette collection. Vous imaginez donc quelle est la différence peut-être entre rafraîchissante chaque cache dans le Classeur, par rapport à rafraîchissant chaque Tableau croisé dynamique dans le Classeur.

Vous avez un Tableaux croisés dynamiques collection sur le VB Feuille de calcul objet. Donc, une petite boucle de ce genre de travail:

Sub RefreshPivotTables()
    Dim pivotTable As PivotTable
    For Each pivotTable In ActiveSheet.PivotTables
        pivotTable.RefreshTable
    Next
End Sub

Notes de la tranchée:

1. Rappelez-vous d'enlever la protection de toutes les protégés de feuilles avant de mettre à jour le tableau croisé dynamique.
2. Enregistrer souvent.
3. Je vais y réfléchir de plus et mise à jour en cours... 🙂

Bonne chance!

Même nous rafraîchir la connexion et, en retour, il va vous rafraîchir tous les pivots sont liés.

Pour ce code, j'ai créé trancheuse de table présente dans Excel:

Sub UpdateConnection()
Dim ServerName As String
Dim ServerNameRaw As String
Dim CubeName As String
Dim CubeNameRaw As String
Dim ConnectionString As String
ServerNameRaw = ActiveWorkbook.SlicerCaches("Slicer_ServerName").VisibleSlicerItemsList(1)
ServerName = Replace(Split(ServerNameRaw, "[")(3), "]", "")
CubeNameRaw = ActiveWorkbook.SlicerCaches("Slicer_CubeName").VisibleSlicerItemsList(1)
CubeName = Replace(Split(CubeNameRaw, "[")(3), "]", "")
If CubeName = "All" Or ServerName = "All" Then
MsgBox "Please Select One Cube and Server Name", vbOKOnly, "Slicer Info"
Else
ConnectionString = GetConnectionString(ServerName, CubeName)
UpdateAllQueryTableConnections ConnectionString, CubeName
End If
End Sub
Function GetConnectionString(ServerName As String, CubeName As String)
Dim result As String
result = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & CubeName & ";Data Source=" & ServerName & ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2"
'"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & CubeName & ";Data Source=" & ServerName & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"
GetConnectionString = result
End Function
Function GetConnectionString(ServerName As String, CubeName As String)
Dim result As String
result = "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" & CubeName & ";Data Source=" & ServerName & ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2"
GetConnectionString = result
End Function
Sub UpdateAllQueryTableConnections(ConnectionString As String, CubeName As String)
Dim cn As WorkbookConnection
Dim oledbCn As OLEDBConnection
Dim Count As Integer, i As Integer
Dim DBName As String
DBName = "Initial Catalog=" + CubeName
Count = 0
For Each cn In ThisWorkbook.Connections
If cn.Name = "ThisWorkbookDataModel" Then
Exit For
End If
oTmp = Split(cn.OLEDBConnection.Connection, ";")
For i = 0 To UBound(oTmp) - 1
If InStr(1, oTmp(i), DBName, vbTextCompare) = 1 Then
Set oledbCn = cn.OLEDBConnection
oledbCn.SavePassword = True
oledbCn.Connection = ConnectionString
oledbCn.Refresh
Count = Count + 1
End If
Next
Next
If Count = 0 Then
MsgBox "Nothing to update", vbOKOnly, "Update Connection"
ElseIf Count > 0 Then
MsgBox "Update & Refresh Connection Successfully", vbOKOnly, "Update Connection"
End If
End Sub

Si vous êtes à l'aide de MS Excel 2003 puis allez dans affichage->barre d'outils->Tableau croisé dynamique à Partir de cette barre d'outils que nous pouvons faire actualiser en cliquant ! ce symbole.