TCD pivotTable ADODB Access

Version imprimable

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

Bonjour à tous,

Dans un 1er module j'ai

Code:

1
2
Public Const ADODB_PROVIDER = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;"
Public Const PATH_DB = "E:\BkUpData\Projets\Access\test.accdb"

Dans un autre module j'ai cette fonction qui crée une connexion. Petite particularité le commandText et CmdType sont déjà renseignés et elle est appelée sur l'événement Load du workbook

Code:

1
2
3
4
5
6
7
8
9
10
11
12
Public Function fWkBookCnxAdd()
 
    Dim objWBConnect As WorkbookConnection
 
    Set objWBConnect = ThisWorkbook.Connections.Add( _
    Name:="tcd", Description:="", _
    ConnectionString:=cstADODB_PROVIDER & _
    "Data Source=" & cstPATH_DB, _
    CommandText:="SELECT * FROM qryFactureSumMonthYear", _
    lCmdtype:=xlCmdSql)
 
End Function

Dans un onglet j'ai ajouté un bouton et sur l'événement click j'ai une erreur sur

Set oPtTable = oPivotCache.CreatePivotTable

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
Private Sub cmdTcd_Click()
 
    Dim oPivotCache As PivotCache
    Dim oPtTable As PivotTable
 
    ActiveSheet.Range("A3").CurrentRegion.Clear ' --> suppression du TCD
 
    ' Create a PivotTable cache
    Set oPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal,  _
    SourceData:=ThisWorkbook.Connections("tcd"))  ' ---> Pas d'erreur 
 
    ' Create a tcd.
    Set oPtTable = oPivotCache.CreatePivotTable( _
    TableDestination:=Range("A3"), _
    TableName:="tcd") ' ---> Erreur  d'exécution 1004 - Erreur définie par l'application ou par l'objet
 
    With ActiveSheet.PivotTables("tcd")
        .SmallGrid = False
 
        .AddFields _
        RowFields:="idfacture", _
        RowFields:="libelle", _
        ColumnFields:="PeriodemontYear"
 
        .adddatafields _
        Field:=oPtTable.PivotFields("montant")
     End With
 
End Sub

Merci pour votre aide

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

Pour la version 2010 il faut utiliser PivotCaches.Create et non add
Voir le dernier post de cette discution pour un exemple de code corrigé et qui fonctionne

25/06/2018, 22h19
Invité

Bonsoir,

https://www.developpez.net/forums/d1...i/#post8011314

Merci dysorthographie,

J'ai chargé le zip et regardé le code mais n'ai pas identifié une ligne de code qui puisse apporter une solution à mon erreur car comme précisé

Pas d'erreur
Code:

1 2 3 4 5 6 7 8 9 10 11 Public Function fWkBookCnxAdd() 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
ADODBCnx.png

Pas d'erreur
Code:

1 2 Set oPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal, _ SourceData:=ThisWorkbook.Connections("tcd")) ' ---> Pas d'erreur
En erreur
Code:

1 2 3 4 ' Create a tcd. Set oPtTable = oPivotCache.CreatePivotTable( _ TableDestination:=Range("A3"), _ TableName:="tcd") ' ---> Erreur d'exécution 1004 - Erreur définie par l'application ou par l'objet
Complément d’information après clôture du sujet:

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

25/06/2018, 22h57
Invité

Erreur,je m'en doutais, mais la quelle. Dire erreur ça ne suffit pas. Pour que sa fonctionne il faut transférer les deux module de classe dans ton code.

Ouvres mon fichier et le tien, vas dans VBA, maintiens le click droit de la souris sur un module de classe et fais le glisser sur ton projet VB.ceci fait intéresses toi au sub test du modul1.
25/06/2018, 23h05
devwhopper
Voici les paramètres de la connexion

ADODBCnx.png

Y-a-t-il des erreurs déjà à ce niveau? La chaine de connexion est bien la bonne

le

Citation:

SourceData:=ThisWorkbook.Connections("tcd"))

est il correct dans :
Code:

1 2 Set oPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal, _ SourceData:=ThisWorkbook.Connections("tcd"))
Merci pour ton aide
26/06/2018, 07h07
Invité

Bonjour,

En fait tu n'as pas tester mon fichier en l'état ?
26/06/2018, 07h26
Pierre Fauconnier

Salut.

Perso, je ne vois pas beaucoup d'utilité à créer un TCD par macro. Je n'ai jamais eu besoin de procéder ainsi en plus de 20 ans de métier d'intégrateur de données ;)

Il me semble plus simple de créer le TCD dans un fichier Excel à la main sur une connexion manuelle, puis de gérer éventuellement la liaison à la source Access par code si le fichier voyage et que la connexion évolue.

Pourrais-tu expliquer ton besoin en français (pas en code vba)?
26/06/2018, 07h30
Invité

Bonjour Pierre,

J'ai développé mon module de classe à l'usage de ce forum mais jamais utilisé personnellement.
26/06/2018, 07h38
Pierre Fauconnier

Salut Robert,

Ca confirme bien mon idée que c'est inutile de pratiquer ainsi ;) et c'est pourquoi je propose une solution qui réduit le vba (surtout sur des TCD) et qui limite le VBA à la seule manipulation qui est éventuellement à automatiser, à savoir la modification de la chaine de connexion.

Cette modification peut être réalisée au sein d'une fonction qui en gros reçoit une chaine générique avec entre crochets les données à modifier et qui reçoit des paires clé/Valeur pour les remplacements.
26/06/2018, 08h59
devwhopper

2 pièce(s) jointe(s)
Merci à vous deux pour vos réponses.

Pierre, j'adhère à ta réflexion de ne pas créer dynamiquement les objets car à priori, il n'y a aucune raison de faire une modification dynamique des objets.

Mais comment initialiser la connexion à la base Access avec ThisWorkbook.Connections.

J'ai tenté

Code:

Set oWkBookCnx = ThisWorkbook.Connections(1)

et dans la fenêtre espion j'ai ceci, a priori correct
Pièce jointe 391519

Pour mémoire dans ma liste des connexions je n'ai que tcd avec les paramètres suivants
Pièce jointe 391524

J'initialise le cache

Code:

Set oPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal, SourceData:=oWkBookCnx)

Mais l'affectation du cache au TCD me génère une erreur 1004
Code:

1 2 3 Set oPtTable = oPivotCache.CreatePivotTable( _ TableDestination:=Range("A3"), _ TableName:="tcd")
Merci pour votre aide

il n'est pas nécessaire d'ajouter le cache.

Au départ, tu as deux solutions:

importer les données dans Excel puis connecter ton TCD en local;
ne pas importer tes données et connecter directement le TCD à Access.

Vu ce que proposes comme code, je suppose que c'est la seconde solution que tu as choisie.

Dès lors, à la création du TCD:

tu le connectes manuellement;
si tu dois modifier la connexion, tu utilises la propriété Connection du cache du tcd. Tu as intérêt à travailler avec un objet de type PivotTable pour un code plus lisible. Voici un exemple avec le premier TCD placé sur Feuil1
Code:

1 2 3 4 Dim pt As PivotTable Set pt = Feuil1.PivotTables(1) pt.PivotCache.Connection = "..."
Ta chaîne de connexion peut évidemment être recomposée en mixant constantes et variables. Perso, je préfère une chaine générique dans laquelle je remplace des signets qu'une concaténation.

Fonction de remplacement sur base d'un tableau Clé-Valeur (à mettre dans un module standard Tools et à balader dans tous tes classeurs avec macro. Tu as ainsi une boite à outils disponible et tu y ajoutes petit à petit tes fonctions et procédures génériques . Ca t'évite de réinventer la roue et cela systématise l'écriture de ton code. Tu gagnes du temps à la création et à la maintenance, en plus de rendre ton code plus lisible et plus sytématique.
Code:

1 2 3 4 5 6 7 8 Function ReplaceFromArray(ByVal Source As String, ParamArray KeyValues()) As String Dim Counter As Long For Counter = 0 To UBound(KeyValues) Step 2 Source = Replace(expression:=Source, Find:=KeyValues(Counter), Replace:=KeyValues(Counter + 1), compare:=vbTextCompare) Next Counter ReplaceFromArray = Source End Function
Redirection de la connexion du cache en pointant vers un Access se trouvant dans le même dossier que le fichier
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 Sub ConnectPivotTable() Dim pt As PivotTable Dim ConnectionString As String Dim DBName As String ConnectionString = "OLEDB;Provider=Microsoft.ACE.OLEDB.15.0;User ID=Admin;Data Source=[source];" DBName = ThisWorkbook.Path & "\erp.accdb" ConnectionString = ReplaceFromArray(ConnectionString, "[source]", DBName) Set pt = Feuil1.PivotTables(1) pt.PivotCache.Connection = ConnectionString pt.PivotCache.Refresh End Sub

26/06/2018, 09h45
devwhopper

Merci Pierre pour ton retour et effectivement c'est beaucoup plus simple !

J'ai tout de même deux questions:

Est-il possible de pointer sur le pivotTables en la nommant genre

Code:

Set pt = Feuil1.PivotTables("toto")

Car ne pas pouvoir le désigner c'est pas vraiment pratique

Pour le plaisir de l'esprit, pourrais-tu m'expliquer comment utiliser thisworkbook.connection (1) car dans l'idée ce serait de créer une collection de connection à l'ouverture et de les affecter à l'ouverture du workbook de l'onglet dans lequel il y a le ou les objets pour lesquels j'ai créé la connexion
26/06/2018, 09h48
Pierre Fauconnier

En fait, si tu avais essayé Set pt = Feuil1.PivotTables("toto"), tu aurais découvert par toi-même que ça fonctionne très bien ;)

Peux-tu préciser ta question sur le Thisworkbook.connections? Je n'ai pas compris tes attentes.
26/06/2018, 09h59
devwhopper

L'idée ce serait de créer une collection de connexions à l'ouverture du workbook et de les affecter à l'ouverture de l'onglet dans lequel il y a le ou les objets pour lesquels j'ai créé la connexion

Car dans la déclaration de la connexion il y a aussi la requête

Après j'imaginais un truc du genre j'affecte l'object connection et comme la requête est déjà implémenté dans l'objet les données sont alors automatiquement affichées.

Encore merci pour ton aide
26/06/2018, 10h23
devwhopper

Dernière question, ParamArray peut-il être remplacé par un objet collection sachant qu'un objet collection accepte absolument tous les type de variables mêmes des objets différents dans une même collection.

J'ai par exemple créé une fonction qui clos tous les objets (recordset, command, connexion...) d'une collection

1 pièce(s) jointe(s)

Perso, je créerais un tableau structuré avec les données de connexion, puis je bouclerais sur les lignes du tableau pour les créer

Tableau structuré nommé t_Connections
Pièce jointe 391567

Procédure de création des connexions, qui sera par exemple appelée lors de l'ouverture du classeur
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Sub CreateConnections() Dim cnRow As ListRow Dim cn As WorkbookConnection Dim cnName As String Dim cnString As String Dim cnQuery As String Dim cnType As Long Dim cmType As Long Dim cnDescription As String For Each cnRow In Range("t_Connections").ListObject.ListRows cnName = cnRow.Range(1).Value cnString = cnRow.Range(2).Value cnQuery = cnRow.Range(3).Value cnType = Split(cnRow.Range(4).Value, ":")(0) cmType = Split(cnRow.Range(5).Value, ":")(0) cnDescription = cnRow.Range(6).Value ThisWorkbook.Connections.Add Name:=cnName, Description:=cnDescription, ConnectionString:=cnString, CommandText:=cnQuery, lcmdtype:=cmType Next End Sub
Tu peux trouver les types de connexions dans l'explorateur d'objet VBA (F2). Ce sont les constantes d'énumération xlConnectionType. Les types de commande sont reprises dans les constantes d'énumération xlCmdSql. Le type de la connexion (ODBC ou OLEDB) est donné en début de chaine de connexion.

Au cas ou une connexion du même nom que celle que l'on ajoute existe déjà, Excel suffixe la nouvelle d'un numéro d'ordre.

26/06/2018, 12h05
Pierre Fauconnier

Citation:

Envoyé par devwhopper

Dernière question, ParamArray peut-il être remplacé par un objet collection sachant qu'un objet collection accepte absolument tous les type de variables mêmes des objets différents dans une même collection.

J'ai par exemple créé une fonction qui clos tous les objets (recordset, command, connexion...) d'une collection

Oui, mais dans le cas de la fonction de remplacement de texte, je ne vois pas l'utilité :koi:
26/06/2018, 12h06
devwhopper

Merci Pierre, je prends l'idée qui est excellente

Mais juste pour la beauté du truc, une suggestion pour utiliser thisworkbook.connections?
26/06/2018, 12h20
Pierre Fauconnier

Citation:

Envoyé par devwhopper

Merci Pierre, je prends l'idée qui est excellente

Mais juste pour la beauté du truc, une suggestion pour utiliser thisworkbook.connections?

Pour l'utiliser où, dans le cas d'une reconnexion du tcd sur une connexion existant dans le classeur?

Code:

pt.PivotCache.Connection = ThisWorkbook.Connections("Customers").OLEDBConnection.Connection
26/06/2018, 12h53
devwhopper

Citation:

Envoyé par Pierre Fauconnier

Oui, mais dans le cas de la fonction de remplacement de texte, je ne vois pas l'utilité :koi:

Tu as raison, ma question portait juste sur ParamArray!

Merci pour ta solution que je teste au plus vite

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