Discussion :

[Pierre Fauconnier]

Même dans un tableau non typé, tu passes ce que tu veux

[Pierre Fauconnier]

La connexion est mémorisée dans le fichier. Elle peut être rafraîchie à l'ouverture, voire même selon un intervalle de temps déterminé. Les propriétés de la connexion et/ou du TCD permettent de préciser les options de MAJ.






Rafraîchir un tcd revient à rafraîchir son cache, et donc tous les TCD s'appuyant sur le même cache.

Les TCD vers un tableau structuré Excel ou une plage du même classeur ont le même cache (par défaut, sauf manip assez tordue pour utiliser des caches différents).

Les TCD pointant vers la même une source externe (même élément de thisworkbook.connections) ont le même cache. Chaque connexion a son propre cache, et donc deux TCD pointant vers la même table ou source Access au travers de deux connexions différentes ont donc chacun leur cache.

Si la MAJ est rapide, ça ne coûte pas grand chose de tourner sur tous les caches du classeur pour les mettre à jour:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub RefreshAllPivotCaches(Optional wb As Workbook)
  Dim pc As PivotCache
 
  On Error Resume Next
  If wb Is Nothing Then Set wb = ActiveWorkbook
  For Each pc In wb.PivotCaches
    pc.Refresh
    If Err <> 0 Then
      MsgBox "Problème de mise à jour du cache de TCD: " & pc.CommandText
      Err = 0
    End If
  Next
End Sub

[devwhopper]

Pierre,

Ok
Les connexions sont persistantes

Question
J'ai par contre une incompréhension sur l'association TCD/Connexion. Faut-il à l'ouverture du fichier Excel, affecter/réinitialiser la connexion au TCD ou cette liaison est-elle aussi persistance?

[devwhopper]

Pierre, tes réponses m'ont été d'une grande utilité pour y voir plus clair.

Pour boucler sur le sujet, voici le code répondant à mon post initial. Certes pas optimisé mais ça donne une idée

Sur l'événement load du workbook j'ai

Code :

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

1
2
3
4
Private Sub Workbook_Open()
    fWkBookCnxDelAll
    fWkBookCnxInitAll  
End Sub

Et dans un module les fonctions suivantes

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
 
Public Const ADODB_PROVIDER = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;"
Public Const PATH_DB = "E:\test.accdb"
 
Public Function fWkBookCnxDelAll()
    Dim oWkBookCnx As WorkbookConnection
 
    For Each oWkBookCnx In ThisWorkbook.Connections
        oWkBookCnx.Delete
    Next oWkBookCnx
End Function
 
 
Public Function fWkBookCnxInitAll()    
    Dim objWBConnect As WorkbookConnection
 
    Set objWBConnect = ThisWorkbook.Connections.Add( _
    Name:="tcd", Description:="", _
    ConnectionString:=ADODB_PROVIDER & _
    "Data Source=" & PATH_DB, _
    CommandText:="SELECT * FROM qryFactureSumMonthYear", _
    lCmdtype:=xlCmdSql)
End Function

Sur l'événement du bouton (cmdAddTCD) de la feuille active je lance le code suivant:

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
Private Sub cmdAddTCD_Click()
 
    Dim oCnx As WorkbookConnection
    Dim oPc As PivotCache
    Dim oPt As PivotTable
 
    ActiveSheet.Range("G4").CurrentRegion.Clear '
 
    ' Create a PivotTable cache
    Set oCnx = ThisWorkbook.Connections("tcd")
    Set oPc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, _
    SourceData:=oCnx)
 
    ' Create a tcd.
    Set oPt = oPc.CreatePivotTable( _
    TableDestination:=ActiveSheet.Range("G4"), _
    TableName:="tcd")
 
    With oPt
        .SmallGrid = False
 
        .AddFields _
        RowFields:="idfacture", _
        RowFields:="libelle", _
        ColumnFields:="PeriodemontYear"
 
        .AddDataField _
        Field:=oPt.PivotFields("montant") _
     End With
End Sub

Et voilà un nouveau TCD



Complément d’information après clôture du sujet:

Pour la version 2010 il faut utiliser PivotCaches.Create et non add

[devwhopper]

Encore un grand merci Pierre pour toutes ces informations qui me permettent de mieux appréhender le monde fabuleux des TCD/Charts.

[Pierre Fauconnier]

Les connexions des caches sont persistantes aussi.

La mise à jour d'une connexion (<> rafraîchissement des données) n'est nécessaire qu'en cas de déplacement de la source et ou de la cible empêchant de trouver le chemin de la source.

Tant que le chemin peut être reconstitué, en ce compris le nom du fichier Access, et que la requête sql sous-jacente peut être utilisée, il n'y a pas de nécessité de mettre à jour les connexions.

Par contre, il peut être utile de cocher la propriété "mise à jour à l'ouverture" pour exposer dans le TCD les données mises à jour (<> la connexion mise à jour).

Je t'ai donné les codes de rafraîchissement parce que tu les demandais "pour le fun", mais dans les faits, tant que tu ne déplaces rien, il n'y a rien à faire du côté des connexions.

[Pierre Fauconnier]

Salut.

On s'est peut-être mal compris sur la signification de persistante. Lorsque je disais que la connexion était persistante, je voulais signifier qu'elle est mémorisée dans Excel, pas qu'elle est ouverte. Je n'ai pas été assez précis sur ce terme. Désolé.

Normalement, la connexion est ouverte au moment de la mise à jour et uniquement pour le temps de la mise à jour.