Code VBA actualise plusieurs TCD

Version imprimable

12/06/2018, 14h48
davidytruc

Code VBA actualise plusieurs TCD

Bonjour, je viens vers vous après 3 heures de recherche... Je souhaite actualiser plusieurs TCD par code VBA... Alors c'est très curieux car le premier TCD se met bien à jour mais cela plante à partir du deuxième. Voilà le code :

Sheets("EJ_Agences").Select
ActiveSheet.PivotTables("TCD1").PivotCache.SourceData = Worksheets("Donnees").Range("A1:Z" & DerLi_Donnees2).Address(, , xlR1C1, True)
ActiveSheet.PivotTables("TCD1").PivotCache.Refresh
Sheets("EJ_Volumes").Select
ActiveSheet.PivotTables("TCD2").PivotCache.SourceData = Worksheets("Donnees").Range("A1:Z" & DerLi_Donnees2).Address(, , xlR1C1, True)
ActiveSheet.PivotTables("TCD2").PivotCache.Refresh
Sheets("EJ_GV").Select
ActiveSheet.PivotTables("TCD3").PivotCache.SourceData = Worksheets("Donnees").Range("A1:Z" & DerLi_Donnees2).Address(, , xlR1C1, True)
ActiveSheet.PivotTables("TCD3").PivotCache.Refresh
Sheets("EJ_DRL").Select
ActiveSheet.PivotTables("TCD4").PivotCache.SourceData = Worksheets("Donnees").Range("A1:Z" & DerLi_Donnees2).Address(, , xlR1C1, True)
ActiveSheet.PivotTables("TCD4").PivotCache.Refresh
Sheets("40pc").Select
ActiveSheet.PivotTables("TCD5").PivotCache.SourceData = Worksheets("Donnees").Range("A1:Z" & DerLi_Donnees2).Address(, , xlR1C1, True)
ActiveSheet.PivotTables("TCD5").PivotCache.Refresh

Chaque TCD porte bien le nom entre guillemet et chaque TCD a la même source de données.

Le code plante donc à partir du TCD2

Avez-vous des solutions ?

Merci
12/06/2018, 16h20
78chris

Bonjour

Tous les TCD basés sur une même source partagent le même cache donc inutile de l’actualiser plus d'une fois ! L'actualisation de l'un actualise tous les TCD utilisant ce cache

Inutile de sélectionner une feuille pour actualiser le cache...

Un TCD doit être construit sur un tableau structuré de préférence, pas une plage...

A tester:

Code:

1
2
3
4
5
6
7
8
9
Sub RefreshTCDs()
Dim Tcd As PivotTable
Dim Ws As Worksheet
    For Each Ws In ThisWorkbook.Worksheets
        For Each Tcd In Ws.PivotTables
            Tcd.RefreshTable
        Next Tcd
    Next Ws
End Sub

12/06/2018, 17h43
78chris

RE

INUTILE !

Si la source a un grand nombre de lignes la répétition inutile de l'actualisation du même cache sature sans doute...
12/06/2018, 19h44
davidytruc

... suite...

Merci pour vos réponses... en effet j'avais lu que le TCD est alimenté par un cache...
J'imagine qu'il s'agit d'une formule dans le gestionnaire de nom... je vais donc tenter d'actualiser la plage de ce nom avec mon code et ensuite je vais donner comme source de données ce nom à mon TCD.
Sinon oui j'ai choisi cette méthode car au départ j'avais bien créé un tableau et je n'avais en effet pas à m'embêter à modifier la plage de donnée du TCD, celui-ci étant basé sur le tableau directement.
Le problème est que mon fichier contient 50 000 lignes de la colonne A à Z et que la mise à jour des données et leur actualisation en vba, ou pas d'ailleurs, prend un temps fou sur mon ordi.
Ce temps est divisé par 10, sérieusement, lorsque le tableau est supprimé et le TCD basé une plage fixe.
Voilà pour les explications.
Je tente mon code demain, je n'ai pas mon ordi ce soir.
Merci en tous cas.
Je reviens vers vous si je ne trouve pas de solution.
Cordialement
David
12/06/2018, 22h13
78chris

RE

Le cache est automatiquement créé et n'est référencé nulle part sauf en VBA .

N'aurais tu pas défini le tableau au delà de la plage remplie ? Un tableau ne doit JAMAIS contenir de lignes vides : il s'adapte automatiquement...

On peut éventuellement utiliser l'ancienne méthode avec une formule nommée à base de DECALER.

En relisant ton code, je pense qu'il démultiplie en fait les caches en redéfinissant un cache pour chaque TCD...
14/06/2018, 08h43
davidytruc

Problème résolu

Voilà comment j'ai résolu mon problème

' On met à jour la plage des TCD, on réactive les calculs automatiques et on actualise les TCD
ActiveWorkbook.Names("TCDdonnees").RefersToR1C1 = "=Donnees!R1C1:R" & DerLi_Donnees2 & "C26"
Application.Calculation = xlAutomatic
Sheets("EJ_Agences").Select
ActiveSheet.PivotTables("TCD1").PivotCache.Refresh

sachant que les TCD sont tous basés sur le nom "TCDdonnees"...

La formule DECALER était celle que j'utilisais avant mais comme précisé dans mon message précédent, cette solution prenait beaucoup trop de temps en calcul processeur.
La mise à jour automatique du fichier par code prenait ainsi 11 minutes.
En supprimant DECALER et en affectant une plage au "TCDdonnees" mon traitement ne prend plus que 1mn et 20s... gain non négligeable...

Merci en tout cas, vous m'avez bien aiguillé.

Cordialement