Discussion :

[devwhopper]

Bonjour à tous,

Dans un 1er module j'ai

Code :

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

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 :

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

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 :

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
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

[Invité]

Bonsoir,

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

[devwhopper]

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 :

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

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 :

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

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

En erreur

Code :

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

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

[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.

[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

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

est il correct dans :

Code :

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

1
2
Set oPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal,  _
    SourceData:=ThisWorkbook.Connections("tcd"))

Merci pour ton aide

[Invité]

Bonjour,

En fait tu n'as pas tester mon fichier en l'état ?

[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)?

[Invité]

Bonjour Pierre,

J'ai développé mon module de classe à l'usage de ce forum mais jamais utilisé personnellement.

[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.

[devwhopper]

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 :

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

Set oWkBookCnx = ThisWorkbook.Connections(1)

et dans la fenêtre espion j'ai ceci, a priori correct


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



J'initialise le cache

Code :

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

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

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

Code :

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

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

Merci pour votre aide

[Pierre Fauconnier]

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 :

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

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 :

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

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 :

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

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

[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 :

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

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

[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.

[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

[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

[Pierre Fauconnier]

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é

[devwhopper]

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

Tu as raison, ma question portait juste sur ParamArray!

Merci pour ta solution que je teste au plus vite

[Pierre Fauconnier]

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




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

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
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.

[devwhopper]

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

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

[Pierre Fauconnier]

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 :

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

pt.PivotCache.Connection = ThisWorkbook.Connections("Customers").OLEDBConnection.Connection