Changer la source (lien ODBC) d'un tableau croisé dynamique

J'ai créé un tableau croisé dynamique, qui va chercher ses renseignement dans un fichier Fox-pro (a l'aide d'un lien ODBC). J'aimerais rediriger ma requête vers un autre fichier source, est-ce possible ou dois-je recréer une requête?

Attention à ne pas mélanger 2 choses : A) La source des données (définir) B) L'Utilisation d'une source de données dans une requête spécifique pour un objet « QueryTable» ou « PivotCache» **********LA SOURCE DES DONNÉES************** Dans le panneau de configuration de ton ordinateur, après un clic sur l'icône « Sources de données (ODBC) », une fenêtre s'ouvre et à partir de celle-ci, tu peux définir 3 types de sources de données. Elles peuvent être définies aussi directement à partir d'Excel (barre de menu/données/ données externes /créer une requête - sans utiliser le panneau de configuration) A) Sources de données utilisateur B) Sources de données Fichier C ) Source de données système La seule différence entre A et B, c'est l'endroit où les informations reliées à L'ODBC sont conservées. Pour une source de données « Utilisateur », c'est la base de registre de l'ordinateur local qui joue le rôle de fichier DSN... c'est pourquoi cette source de données créée sera disponible seulement à partir de cet ordinateur puisque l'information est liée à la base de registre de ce dernier. L'utilisation d'un Fichier DSN permet une plus grande flexibilité et disponibilité au niveau d'un réseau.... Pour ce qui est d'une source de données système, elle s'adresse à une base de données sécurisée comme les fichiers de type MDW pour l'accès avec mot de passe et avec une autorisation particulière pour un usager ou pour un groupe d'usagers... Que la source de données soit enregistrée dans la base de registre ou dans un fichier DSN, elle contient essentiellement ce type d'informations : 1) définition du fichier source des données (chemin et ledit fichier) 2) pilote associé au type de fichier de ta source de données. Le contenu d'un fichier DSN ressemble à ceci ... c'est le même type d'informations qui est enregistré dans la base de registre lorsqu'il n'y a pas de fichier DSN. '--------------- [ODBC] DRIVER=Microsoft Access Driver (*.mdb) UID=admis UserCommitSync=Yes Threads=3 SafeTransactions=0 PageTimeout=5 MaxScanRows=8 MaxBufferSize=2048 FIL=MS Accès DriverId=25 DefaultDir=C:\ODBC\ DBQ=C : \ODBC\Comptoir.mdb » -- -- ------ ------ ------ Si tu voulais, tu pourrais facilement le créer par programmation puisqu'il ne s'agit que d'un vulgaire fichier texte éditable avec par exemple NotePad.exe ... Évidemment, les éléments de ce fichier peuvent être modifiés le cas échéant sans aucun problème... Avantage de ce fichier dsn : il peut être facilement copié sur un serveur réseau (ordinateur) et être ainsi disponible à plusieurs utilisateurs qui à leur tour pourront définir localement des requêtes. Ils n'auront pas besoin de réinventer la roue chaque fois! De plus, si une modification doit être effectuée comme le changement du chemin du fichier source, il est possible d'éditer et de modifier directement le fichier DSN. Toutes les NOUVELLES requêtes en tiendront compte. **UTILISER LA SOURCE DES DONNÉES - DANS UN QUERYTABLE** Lorsque l'on crée un « QueryTable» dans un classeur, il faut se rappeler que ce « QueryTable» s'enregistre dans le fichier Excel au moment de la sauvegarde. Il y a deux « Propriétés essentielles » à un objet « ObjetQuery» : 1 ) Connexion : C'est une chaîne de caractères chargée d'établir (ouvrir) la connexion entre la base des données à l'aide du pilote que l'on a choisi (Access, Oracle, Excel, Sql ...) lors de l'élaboration du QueryTable. 2 ) CommandText : C'est en fait une chaîne de caractères représentant la requête que l'on a créée lorsque la fenêtre de Query était ouverte. Lorsque le classeur possède des TableQuery déjà enregistrés et que l'on décide de modifier le nom du Classeur Source des données, il faut obligatoirement modifier les 2 chaînes de texte nommées précédemment pour que la TableQuery retrouve sa fonctionnalité. Et comme des données ne sont pas visibles dans une cellule, on peut les retrouver à l'aide d'une procédure. Voici une macro qui devrait faire le travail pour tous les "QueryTable" contenus dans un classeur. Évidemment, cela suppose que les données proviennent de la même base de données source. Cette astuce est réservée aux utilisateurs Excel2000 car la propriété CommandTexte est une nouveauté de celle-ci. ------------------------------------------- 

sub Query_Et_NomFichierModifie()
 
 Dim OldName As String, NewName As String
 Dim Sh As Worksheet, Qt As QueryTable
 
 ' À saisir manuellement si nécessaire...
 OldName = ThisWorkbook.FullName
 NewName = "D\Toto\MonFichierQuery.xls"
 
 For Each Sh In Worksheets
  For Each Qt In Sh.QueryTables
    If InStr(Qt.Connection, OldName) > 0 Then
      Qt.Connection = Replace(Qt.Connection, _
        OldName, NewName)
      Qt.CommandText = Replace(Qt.CommandText, _
        Left(OldName, Len(OldName) - 4), _
        Left(NewName, Len(NewName) - 3))
      Qt.Refresh False
    End If
  Next
 Next
 'Sauvegarde du fichier
 ThisWorkbook.Save
 Set Sh = Nothing: Set Qt = Nothing
 
 end sub

'------------------------------------------- Si tu veux voir dans une cellule la chaîne de "Connexion" et la chaîne de "CommandText" d'un objet Query dans une feuille nommée "Toto" dans mon exemple : With Worksheets("Feuil1") 'Nom Feuille à redéfinir .Range("A1") = Worksheets("Toto").QueryTables(1).Connection .Range("A5") = Worksheets("Toto").QueryTables(1).CommandText End With Une chaîne de connexion ressemble à ceci : (une seule ligne) ODBC;DSN=MS Access Database;DBQ=C:\Mes documents\Comptoir.mdb; DefaultDir=C:\Mesdocuments\;DriverId=281;Exclusive=1; FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;ReadOnly=0;UID=admin; Une Chaîne de "CommandText" ressemble à ceci : SELECT Fournisseurs.Société, Fournisseurs.Adresse, Fournisseurs.Ville, Fournisseurs.Pays FROM `C:\ODBC\bernard 2\Comptoir`.Fournisseurs Fournisseurs Observe dans les 2 cas, on retrouve le chemin complet du fichier source de la requête. Si l'on doit changer son nom, il est normal que la mise à jour du querytable n'arrive pas à retrouver le fichier contenant les données sources. La procédure donnée dans ce message n'a que pour but que de 

substituer la partie de la  chaîne  qui a été modifiée. C'est le même principe que de 
substituer une section d'une chaîne de   caractères dans une cellule avec une formule.    Que ton ODBC soit utilisé dans le cas d'un "QueryTable" ou d'un "PivotCache" (.(cube  Olap),   c'est exactement le même principe...ils possèdent tous les 2 les propriétés "Connexion"  et  "CommandText" sauf que l'objet auquel se réfère ces propriétés est différent..    Voici une procédure qui s'applique soit à un Pivotcache ou à un QueryTable pour   retrouver  les chaînes de ces 2 propriétés :    Si ton objet est un QueryTable :    Tu dois adapter le nom des feuilles et l'index de ton querytable. Si tu fais un clic droit  dans   une cellule dans la plage du résultat de ton querytable, et que tu choisis dans le menu   contextuel "Propriété", tu vas pouvoir visualiser le nom complet du querytable. Tu peux  l'utiliser en lieu et place de l'index dans le code.  '------------------------  
sub UnQueryTable()
  With Worksheets("Feuil2")
    .Range("A1") = Worksheets("Feuil1").QueryTables(1).Connection
    .Range("A5") = Worksheets("Feuil1").QueryTables(1).CommandText
  End With
 
 end sub
  '------------------------    Si c'est un PivotCache :    Pour obtenir le nom du tableau croisé dynamique, tu peux cliquer sur le bouton "Tableau  croisé  dynamique" de la barre d'outils du même nom, et choisir dans le sous-menu :: "Option de la  table..." Le nom complet peut être utilisé à la place de l'index pour identifier  correctement le  "PivotCaches" dans la procédure.    '------------------------  


sub UnPivotCache()
  With Worksheets("Feuil2")
    .Range("A1") = ThisWorkbook.PivotCaches(1).Connection
    .Range("A5") = ThisWorkbook.PivotCaches(1).CommandText
  End With
 
 end sub

 A )  Après leur création, les objets "QueryTable" ou "PivotCache" sont totalement indépendants du type de sources de données utilisées. Les sources de données seront utiles pour les   nouveaux QueryTable ou PivotCache ... mais ils sont totalement inutiles pour les queryTable  ou PivotCache déjà définis.    Toute l'information dont a besoin un "QueryTable" ou "PivotCache" est enregistré dans le  classeur au moment de la sauvegarde. Les modifications faites à un fichier DSN ou   directement dans la base de registre ne modifieront pas la donne relative à ces objets.    B )  Il faut faire attention avec la procédure visant à modifier les chaînes de "Connexion" et  "CommandText" d'un "QueryTable" ou d'un "PivotCache". C'est une présentation générale   de ce que pourrait être une procédure type. Dans le détail, vous aurez sûrement remarqué   que dans la chaîne de connection, on utilise le nom du fichier et son EXTENSION comme   ceci : DBQ=C:\Mes documents\Comptoir.mdb, l'extension du fichier dans la chaîne   "CommandText" n'est pas inscrite dans la chaîne de texte :  " FROM `C:\ODBC\bernard 2\Comptoir`.Fournisseurs Fournisseurs "   La procédure suggérée ne fait pas ce type de distinction... faudrait voir à l'adapter.    Pour ceux qui auraient tendance à trouver cela difficile, on peut simplifier le processus :    A ) Éditer la chaîne actuelle de connection dans la Cellule A1  B ) Éditer la chaîne actuelle de CommandText dans la Cellule A2  C ) Modifier parcimonieusement les informations concernant le changement de nom du   fichier source et son nouveau chemin  le cas échéant directement dans les cellules A1 et A2.    D ) Réaffecter aux propriétés "Connection" et "CommandText" , les chaînes de texte  modifiées.  Si on peut faire cela pour extraire les chaînes de connection et de CommandText :    With Worksheets("Feuil1") 'Nom Feuille à redéfinir    .Range("A1") = Worksheets("Toto").QueryTables(1).Connection    .Range("A2") = Worksheets("Toto").QueryTables(1).CommandText  End With    Après avoir modifié manuellement les chaînes de caractères on peut faire ceci :    With Worksheets("Feuil1") 'Nom Feuille à redéfinir     Worksheets("Toto").QueryTables(1).Connection = .Range("A1")     Worksheets("Toto").QueryTables(1).CommandText =.Range("A2")      .QueryTables(1).Refresh False  End With    Les modifications seront conservées après l'enregistrement du fichier.

Auteur :

Mots clefs associés à cette page : , , , , , , , ,