TCD pivotTable ADODB Access

Version imprimable

Voir 40 message(s) de cette discussion en une page

26/06/2018, 14h39
Pierre Fauconnier

1 pièce(s) jointe(s)

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

Pièce jointe 391640
26/06/2018, 14h57
devwhopper
Pierre, merci encore pour tout, ça marche impec.

Je profite de ta super expertise pour avoir des précisions:

La connexion associée à un objet TCD / CHART est-elle persistante ou faut-il la réinitialiser à chaque ouverture du fichier Excel?

Pour rafraichir le TCD quelle méthode utilises-tu
Code:

1 2 Set pvtTable = Worksheets("Feuil2").Range("A3").PivotTable pvtTable.RefreshTable
vs

Code:

Worksheets("Feuil2").PivotTables("tcd").PivotCache.Refresh

1 pièce(s) jointe(s)

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.

Pièce jointe 391690

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:

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

26/06/2018, 15h56
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?
26/06/2018, 16h01
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, 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:

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

Et dans un module les fonctions suivantes

Code:

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:

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

26/06/2018, 16h24
devwhopper

Encore un grand merci Pierre pour toutes ces informations qui me permettent de mieux appréhender le monde fabuleux des TCD/Charts.
01/07/2018, 12h27
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.

Voir 40 message(s) de cette discussion en une page