Problème Excel plantage

**bil_home** · 26/05/2015, 15h50

Bonjour,
Je travaille actuellement sur l'automatisation d'un outil. étant pour le travail je ne peux vous le joindre.
le fichier contient un onglet source de 3000 ligne pour 130 colonne.
après j'ai pas mal de tcd et d'autre onglet qui se base dessus. (peut etre 20 30)
Le code vba me permet de creer le fichier source a partir de plusieurs fichier, réaliser un traitement (création de colonne supplémentaire dans la source) mise à jour de tous les tcd, copier coller des tcd en valeur.
le fichier à la base faisait 4Mo et maintenant il fait 50mo. j'ai continuer mon projet et il a passer la barre de 60 mo . par contre maintenant il plante à l'ouverture.

Je vous contacte car c'est assez critique, urgent et je ne vois pas la raison

et je n'ai pas de ligne et colonne en trop.

que me conseillez vous ?

le problème semble survenir lorsque je souhate mettre à jour mes tcd

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
Sub Update_tcd()
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Sheets("Reponse").Select
 
'dimension onglet
nbligrep = Sheets("Reponse").Cells(Rows.Count, 1).End(xlUp).Row
nbcolrep = Sheets("Reponse").Cells(1, Columns.Count).End(xlToLeft).Column
 
 
Sheets("tcd_control").Select
ActiveSheet.PivotTables("tcd_control").SourceData = "reponse!R1C1:R" & nbligrep & "C" & nbcolrep 'mise à jour du tcd contol
ActiveSheet.PivotTables("tcd_control").PivotCache.Refresh
 
Sheets("tcd_1").Select
For i = 1 To 10
ActiveSheet.PivotTables("tcd" & i).SourceData = "reponse!R1C1:R" & nbligrep & "C" & nbcolrep 'mise à jour des tcd
ActiveSheet.PivotTables("tcd" & i).PivotCache.Refresh
Next
 
 
Sheets("tcd_control").Select
 
Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
End Sub

merci par avance

**parmi** · 26/05/2015, 15h52

Bonjour,

Quelle version utilises-tu ?

**bil_home** · 26/05/2015, 15h58

Excel 2013, j'ai ajouter du code là haut car je pense que le problème survient au moment de mettre à jour tous les TCD

plus précisément au moment où je met à jour la source avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveSheet.PivotTables("tcd_control").SourceData = "reponse!R1C1:R" & nbligrep & "C" & nbcolrep 'mise à jour du tcd contol

j'effectue la mise à jour car j'ai ajouter une colonne à la fin de la source. cette étape me permet de pouvoir acceder à cette colonne dans mes TCD.
auparavant cette étape ne plantait pas. il est possible que Excel est arriver à bout

est il possible que mon fichier contiennent du cache inutile qui pourrait explique cette surcharge.

**vinc_bilb** · 26/05/2015, 16h25

Bonjour,

Oui, par défaut, les data sont sauvegardées sauf erreur avec la PivotTable ....

Sous Excel Anglais: PivotTable -> Options -> Data: bien décocher 'Save source data with file' et 'enable show details'
Sous VBA, (EDIT)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
pvtTable.SaveData = False

**bil_home** · 26/05/2015, 16h42

Merci l'ami de ta réactivité. avant de regarder ton message jai fait cette manipulation et bingo mon fichier vient de passer à 18MO et je n'ai pas encore fini.

peux tu me donner des explication sur ces valeur :
- enregistrer les données sources avec le fichier
- Activier l'affichage des détail
- nombre d'élément à retenir

pour l'instant je fait 2 sur 3 (je n'ai pas fait activer l'affichage des détail)

quels sont les inconvénients avec cette manipulation ?

je viens de m’apercevoir d'un problème avec cette manip. quand je rouvre le fichier, les tcd ne sont pas accessible. je dois faire une actualisation. quand je l'enregistre et je le rouvre, mes slices disparaît.
pour résoudre cela j'ai mis actualisation à l'ouverture. j'ai garder enregistrer les données sources pour mon tcd qui utilise les slices. par contre je ne comprend pas pourquoi une fois sur deux mes slices disparaissent.

**vinc_bilb** · 27/05/2015, 01h57

et ben .... toujours des compromis pas faciles

Ce que j'ai compris:
- enregistrer les données sources avec le fichier: Excel sauve par défaut une copie locale des data avec le TCD (autant de fois qu'il y a de TCD, même si les data source sont les mêmes!)
C'est en particulier ce qui permet de voir le TCD même si les données sont sur un autre fichier, non accessible par exemple ....
- Activier l'affichage des détail: permet quand on double click sur un TCD, de recréer le données source! Et oui, si c'était confidentiel, c'est raté

Les TCD .... difficile de s'en passer quand même, non? Bon courage

**bil_home** · 27/05/2015, 09h17

Oui tout à fais. Les tcd me sont indispensables. En faite je vais même en avoir 50 je pense dans le fichier finale. Actuellement je fais fasse à un réel problème d'optimisation lorsque je met à jour les tcd qui me prend 3 à 4 min d'attente. Est il possible de modifier les tcd par vba pour le même cache pour tout les tcd ? Ma source est presente dans le fichier et elle est identique pour tout les tcd.
Merci

**joe.levrai** · 27/05/2015, 09h51

Bonjour,

est-ce que 50 TCD déjà tous chargés sont nécessaires ?
d'ailleurs, est-ce que 50 TCD sur la même source de données ne pourraient pas se transformer en "moins de TCD" mais agencés différements (voir rendus dynamiques avec des segments et/ou du VBA pour jouer sur les filtres) ?

ne pourrais-tu pas générer les TCD par VBA à la réalisation d'une action par l'utilisateur ?

**bil_home** · 27/05/2015, 12h05

Bonjour,

les Tcd sont bien nécessaire car l'outil est pour un utilisateur lambda.
Pour info, l'outil a la base contenait bien 50 Tcd sans macro et le fichier ne faisait que 5 Mo.
chaque Tcd apporte une vue. une vue qui est définit à l'aide d'un tableau de bord sous forme de segment relier à un tcd de controle. les autre tcd se mette à jour par rapport à celui ci via un code que j'ai trouver sur internet qui se situe sur le programme de l'onglet :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
 
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
 
Application.EnableEvents = False
Application.ScreenUpdating = False
 
For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain And ws.Name Like "tcd_*" Then
                pt.ManualUpdate = True
                Set pf = pt.PivotFields(pfMain.Name)
                        bMI = pfMain.EnableMultiplePageItems
                        With pf
                            .ClearAllFilters
                            Select Case bMI
                                Case False
                                    .CurrentPage = pfMain.CurrentPage.Value
                                Case True
                                    .CurrentPage = "(All)"
                                    For Each pi In pfMain.PivotItems
                                        .PivotItems(pi.Name).Visible = pi.Visible
                                    Next pi
                                    .EnableMultiplePageItems = bMI
                            End Select
                        End With
                        bMI = False
 
                Set pf = Nothing
                pt.ManualUpdate = False
            End If
        Next pt
    Next ws
Next pfMain
 
Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

re,

j'ai 55 pivotcache dans mon workbook. il m'en faudrait un pour tous les TCD si c'est possible.
comment peut on faire cela ?
merci

**joe.levrai** · 27/05/2015, 12h13

En partant de ton TCD principal, on affecte l'index de son cache aux index des autres caches

je n'ai pas testé, j'ai écris ça en m'appuyant de l'explorateur d'objet et de l'aide

dis nous si ça fonctionne, ça pourrait bien m'intéresser un jour

mais j'ai pas de matière pour le tester (et un peu la flemme de créer un environnement favorable)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub ReunionPvtCache()
 
Dim Pvt As PivotTable
Dim Sh As Worksheet
 
For Each Sh In ActiveWorkbook.Worksheets
  For Each Pvt In Sh.PivotTables
      Pvt.CacheIndex = Sheets("FeuilleAvecTcdPrincipal").PivotTables(1).CacheIndex
  Next Pvt
Next Sh
 
End Sub

**bil_home** · 27/05/2015, 12h37

tu es un génie !!!!
merci beaucoup

pour ne pas faire la même erreur comment je dois procéder pour créer un tcd avec le même cache. je dois forcément utilisé du code ?
j'avais un programme pour mettre à jour les tcd. il faut que je le modifie du coup. j'ai un peux de mal avec les pivotCache car je ne vois pas comment il se définit (son nom)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sub Update_tcd()
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
Sheets("Reponse").Select
 
'dimension onglet
nbligrep = Sheets("Reponse").Cells(Rows.Count, 1).End(xlUp).Row
nbcolrep = Sheets("Reponse").Cells(1, Columns.Count).End(xlToLeft).Column
 
 
Sheets("tcd_control").Select
ActiveSheet.PivotTables("tcd_control").SourceData = "reponse!R1C1:R" & nbligrep & "C" & nbcolrep 'mise à jour du tcd contol
ActiveSheet.PivotTables("tcd_control").PivotCache.Refresh
 
Sheets("tcd_1").Select
For i = 1 To 10
ActiveSheet.PivotTables("tcd" & i).SourceData = "reponse!R1C1:R" & nbligrep & "C" & nbcolrep 'mise à jour des tcd
ActiveSheet.PivotTables("tcd" & i).PivotCache.Refresh
Next
 
 
Sheets("tcd_control").Select
 
Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
End Sub

**joe.levrai** · 27/05/2015, 12h47

Je ne sais pas

je pensais que dès lors que la source était identique, les TCD utilisaient le même cache

il doit y avoir un process précis à respecter pour que les caches soient nativement identiques quand les TCD sont crées, mais lequel ?

tu testes et tu nous dis ?

**vinc_bilb** · 27/05/2015, 15h15

Bonjour,

Pour regarder le nombre de caches effectivement utilisés dans un Workbook:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveWorkbook.PivotCaches.Count

Voir également (intéressant) en anglais: https://support.office.com/Unshare-a-data-cache-between-PivotTable-reports si ça peut aider

**bil_home** · 27/05/2015, 15h57

Oui j'ai bien vérifier et je suis passé de 55 à 1.

J'ai un bouton update tcd avec lequel je voudrais mettre à jour tous mes tcd avec une nouvelle source data. actuellement les Tcd ont un seul pivotcache et je voudrais modifier le source data de ce pivotcache afin de mettre à jour toute ma base. la base reste le meme tableau mais avec plus de ligne et potentiellement plus de colonne si rajout.

architecture : 50 Tcd a partir de 1 pivotcache qui se base sur 1 source data
je voudrais mettre a jour la source data pour prendre en charge le nombre de ligne que possède le tableau source.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
'dimension onglet
nbligrep = Sheets("Reponse").Cells(Rows.Count, 1).End(xlUp).Row
nbcolrep = Sheets("Reponse").Cells(1, Columns.Count).End(xlToLeft).Column
source = "reponse!R1C1:R" & nbligrep & "C" & nbcolrep

j'ai vraiment du mal à m'en sortir avec les pivotcache

**joe.levrai** · 27/05/2015, 16h09

Si tu fais un Refresh sur le pivotcache (puisqu'il n'y en a plus qu'un seul) ça devrait être bon non ? ça va mettre à jour tout tes TCD ?

**bil_home** · 27/05/2015, 16h45

il faut pas au préalable modifier le sourcedata du pivotcache. cette partie là me bloque.
la formule pour le refresh c'est bien :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets(1).PivotTables(1).PivotCache.Refresh

du coup il faut que je le fasse sur tout les tcd ?

j'ai vraiment du mal avec les pivotcache

**vinc_bilb** · 27/05/2015, 17h52

Bonjour,

Si vous scrutez la collection PivotTables (et bien avec un 's"), vous avez accès à tous les TCD du classeur j'imagine ..... comme (non testé)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
For each Pvt In ThisWorkbook.PivotTables
   Pvt .PivotCache.Refresh
Next Pvt

**bil_home** · 27/05/2015, 18h35

je ne pense pas que ce code va permettre de modifier le pivotcache par contre il peut permettre de mettre à jour les tcd.
en essayant ce code, le programme n'entre pas dans la boucle. du coup je l'ai modifier comme cela

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub test3()
Dim Pvt As PivotTable
Dim Sh As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For Each Sh In ActiveWorkbook.Worksheets
  For Each Pvt In Sh.PivotTables
   Pvt.PivotCache.Refresh
Next Pvt
Next Sh
 
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 
End Sub

le problème c'est que ça a durer 7min après j'ai arreter. le code smble bien tourner avec le debug mais prend du temps. bizarrement quand j'ai arreter il était dans le code ci dessous qui me permet d'avoir les filtre similaire sur tout les tcd. ce code est présent dans la feuille de controle. en arretant le progamme les tcd se sont vider du coup.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
 
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
 
Application.EnableEvents = False
Application.ScreenUpdating = False
 
For Each pfMain In ptMain.PageFields
    bMI = pfMain.EnableMultiplePageItems
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain And ws.Name Like "tcd_*" Then
                pt.ManualUpdate = True
                Set pf = pt.PivotFields(pfMain.Name)
                        bMI = pfMain.EnableMultiplePageItems
                        With pf
                            .ClearAllFilters
                            Select Case bMI
                                Case False
                                    .CurrentPage = pfMain.CurrentPage.Value
                                Case True
                                    .CurrentPage = "(All)"
                                    For Each pi In pfMain.PivotItems
                                        .PivotItems(pi.Name).Visible = pi.Visible
                                    Next pi
                                    .EnableMultiplePageItems = bMI
                            End Select
                        End With
                        bMI = False
 
                Set pf = Nothing
                pt.ManualUpdate = False
            End If
        Next pt
    Next ws
Next pfMain
 
Application.EnableEvents = True
Application.ScreenUpdating = True
 
End Sub

**vinc_bilb** · 28/05/2015, 10h21

Bonjour

le problème c'est que ça a durer 7min après j'ai arreter. le code smble bien tourner avec le debug mais prend du temps. bizarrement quand j'ai arreter il était dans le code ci dessous qui me permet d'avoir les filtre similaire sur tout les tcd. ce code est présent dans la feuille de controle. en arretant le progamme les tcd se sont vider du coup.

Non testé, mais je pense que votre procédure test3 génère un évènement qui déclenche l'appel de Worksheet_PivotTableUpdate.
Suggestion: rajouter un Application.EnableEvents = False dans test3 et mettre un point d'arret dans Worksheet_PivotTableUpdate pour vérifier comment et quand on passe dedans

On progresse quand même, non?

**bil_home** · 28/05/2015, 11h30

oui c'est sur, je suis passé d'un fichier qui voulais même plus s'ouvrir à un fichier presque bon.

je ne connaissais pas cette ligne de code : Application.EnableEvents = False
depuis que je l'ai mise plus de passage dans la fonction de filtrage. Good
la mise à jour des tcd marche et prend par contre 4 min... moins Good
le hic reste ce que je pensais: si j'ajoute des ligne dans ma source, les tcd ne le prend pas en compte. je dois modifier le cache afin de pouvoir prendre en compte la taille de la source :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
nbligrep = Sheets("Reponse").Cells(Rows.Count, 1).End(xlUp).Row
nbcolrep = Sheets("Reponse").Cells(1, Columns.Count).End(xlToLeft).Column
source = "reponse!R1C1:R" & nbligrep & "C" & nbcolrep

j'arrive pas a trouver comment. il y a le code qui permet de créer un cache mais du coup j'ai deux cache et après les tcd se vide.

Problème Excel plantage

Macros et VBA Excel

Discussions similaires

Partager

Partager