Liaison relatif

Le 14/03/2018 à 22:45

Bonsoir à tous,

J'ai un soucis que je n'arrive pas à trouver une solution.

J'ai un fichier excel lié une table Access stocker dans un sous dossier /BDD, afin de pouvoir déplacer facilement ce fichier, j'ai définis un chemin relatif dans les différentes connexion à la BDD et dans mes différentes maccros.

.\Gestion\BDD\CEF.accdb

Ca fonctionne parfaitement mais bien sûr c'était trop beau, si j'utilise ces fichiers sur un autre pc, cela ne fonctionne plus du tout Hors je vais héberger ces fichiers sur dropbox ou on sera deux à l'utiliser sur plusieurs pc dont les chemins absolu ne sont pas du tout pareil sur les différents pc.

Peut-on définir autrement le chemin afin qu'il définisse lui même dans quel lecteur il se trouve en fonction du pc ?

Merci pour votre aide:)

Bonne soirée.

Le 15/03/2018 à 07:08

Bonjour,

Une piste avec "FileDialog()".

A l'ouverture du classeur, la recherche du chemin de la base de données Access est faite dans le registre, si la fonction "GetSetting()" ne retourne pas de chemin, une boite de message demande si on veux définir le dossier par défaut et si "oui", "FileDialog()" est utiliser pour le choix du dossier et il est ensuite inscrit dans le registre à cette adresse : HKEY_CURRENT_USER\Software\VB and VBA Program Settings (boite "rechercher" entrer "regedit"). Ceci fait, à la prochaine ouverture le chemin sera récupéré dans le registre sans aucune intervention de l'utilisateur :

Private Sub Workbook_Open()

    Dim Chemin As String

    Chemin = GetSetting(ThisWorkbook.Name, "Chemin", "Dossier", "")

    If Chemin = "" Then

        If MsgBox("Voulez-vous définir le chemin par defaut de la base de données Access ?", vbQuestion + vbYesNo) = vbYes Then

            With Application.FileDialog(4)

                If .Show = -1 Then

                    Chemin = .SelectedItems(1) & "\"
                    SaveSetting ThisWorkbook.Name, "Chemin", "Dossier", Chemin

                End If

            End With

        End If

    End If

    MsgBox Chemin
    'DeleteSetting ThisWorkbook.Name '<--- suppression dans le registre

    'ici la suite de ton code comme par exemple l'ouverture de la base de données...
    '...
    '...

End Sub
Le 15/03/2018 à 09:07

Super merci j'ai réussi à faire fonctionner ainsi juste une petite erreur sur une seule liaison que je n'ai pas encore trouvé la raison mais ca va venir

Le 15/03/2018 à 14:54

J'ai un petit soucis avec cette solution. Si je ferme le fichier et que je le réouvre, il me confirme le chemin qui a été indiqué, par contre mes maccros ne fonctionne plus, j'ai une erreur:

erreur générale impossible d'ouvrir la clé de registre 'temporary (volatile) excel

Apparemment un problème de droit? Mais je n'arrive pas à trouver une solution, as-tu déjà eu le cas?

Le 15/03/2018 à 15:55

J'ai trouvé finalement une autre solution qui fonctionne avec une fonction qui recherche le répertoire ou se trouve le fichier et l'affiche sur une cellule, j'utilise cette cellule pour établir mes liens dans mes macros, ca fonctionne.

Juste les liens avec les connexion avec ma BDD , j'affiche certains tables de ma BDD sur mon excel mais je ne sais pas comment définir dans les paramétrés de la requête qui doit chercher le lien de la cellule X pour trouver la bdd?

Voici la requete de la connexion avec mon lien relatif que j'utilisai mais qui ne fonctionne pas si je change de pc donc :

rovider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=.\Gestion\BDD\CEF.accdb;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

Comment puis je définir le Data Source=.\Gestion\BDD\CEF.accdb , pour utiliser plutôt la valeur de la celulle X qui à le lien vers la BDD?

Le 15/03/2018 à 16:02

Re,

Au boulot et chez moi, ça fonctionne nickel pour l'inscription et la récup dans la base de registre !

Ton code est estropié, postes quelque chose de plus complet !

Le 15/03/2018 à 16:32

Mais c'est le code complet

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=.\Gestion\BDD\CEF.accdb;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False

C'est le code de la connection que Excel a créer via l'onglet "donnees" dans excel, j'ai choisis la base de donnée acces dans mes fichiers et j'ai afficher la table

Mais le lien étant absolu, je l'avais d'abord remplacer en relatif avec .\Gestion\BDD\CEF.accdb mais cela ne fonctionne donc pas en passant d'un pc à l'autre. J'aimerai pouvoir remplacer ce .\Gestion\BDD\CEF.accdb par la valeur de la cellule qui contient le chemin absolu du fichier Access défini pas une fonction à l'ouverture du fichier. Mais je ne sais pas comment le définir dans ce type de code, je suis un peu perdu

Le 15/03/2018 à 18:21

Re,

Testes et adapte :

Dim Base As String

Base = Range("A1").Value

A = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & Base & ";Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False"
Le 15/03/2018 à 19:52

Oui d'accord mais comment faire l'appel de cette variable dans l'onglet Connexions de Excel, car pour modifier la connexion je clique sur la connexion existante,cliquez sur propriété et dans définition se trouve l'appel de la table, mais là je peux pas mettre du code VBA donc je sais pas comment faire.

Car donc sur une feuille qui s’appelle "client", cette connexion affiche ma table une table de ma bdd access, de la je l'utilise pour diverses choses dans mon excel. Ou alors peut-on afficher autrement cette table directement en vba (car si je sais l'affiche en VBA, mon problème ne se pose plus) et lui dire de l'afficher sur cette feuille "client" par exemple? Je suis novice dans le domaine, je chipote mais je ne sais pas trop ce qui est possible.

Le 16/03/2018 à 08:05

Bonjour,

Utilise l'enregistreur de macro puis intègres le chemin de ta base ensuite, tu supprime ton tableau et tu lances ta macro, ça devrait fonctionner. Ci-dessous le code que j'ai adapté en l'ayant pas mal épuré après enregistrement avec l'enregistreur et qui fonctionne :

Sub Test()

    Dim Base As String

    Base = Range("A1").Value

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" _
        & "Data Source=" & Base _
        & ";Mode=Share Deny Write;", "Extended Properties="""";" _
        & "Jet OLEDB:System database="""";" _
        & "Jet OLEDB:Engine Type=5;" _
        & "Jet OLEDB:Database Locking Mode=0;" _
        & "Jet OLEDB:Global Partial Bulk Ops=2;" _
        & "Jet OLEDB:Global Bulk Transactions=1;"), Destination:=Range("$A$10")).QueryTable

        .CommandType = xlCmdTable
        .CommandText = Array("Feuil1")
        .PreserveFormatting = True
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = Base
        .ListObject.DisplayName = "Mon_tableau_Importé"
        .Refresh BackgroundQuery:=False

    End With

End Sub
Le 16/03/2018 à 13:53

Re,

Sinon, tu peux utiliser ADO avec "CopyFromRecordset". Une piste, adaptes les variables et testes :

Private Sub Importer()

    Dim ConnectBase As Object
    Dim Rs As Object
    Dim Fe As Worksheet
    Dim Cel As Range
    Dim Base As String
    Dim NomTable As String
    Dim SQL As String

    Set Fe = Worksheets("Feuil1")
    Set Cel = Fe.Range("A10") 'colle les enregistrement à partir de "A10" de "Feuil1"

    Base = Fe.Range("A1") 'chemin et nom de la BBD en "A1" de "Feuil1"
    NomTable = "Nom_De_La_Table" 'adapter le nom de la table

    'récup de tous les enregistrement, si seulement une partie est nécessaire, adapter la requête
    SQL = "SELECT * FROM " & NomTable & ";"

    'connecte la base avec ADO
    ConnecterBase ConnectBase, Base, Rs

    With Rs

        .CursorType = 1
        .LockType = 3
        .Open SQL, ConnectBase
        Cel.CopyFromRecordset Rs 'copie les enregistrements à partir de la cellule indiquée

    End With

    'fermeture de la connexion
    ConnectBase.Close

    Set ConnectBase = Nothing
    Set Rs = Nothing

End Sub

Private Sub ConnecterBase(ConnectBD As Object, _
                          FichierBase As String, _
                          Optional Rs)

    Set ConnectBD = CreateObject("ADODB.Connection")

    If Not IsMissing(Rs) Then
        Set Rs = CreateObject("ADODB.Recordset")
    End If

    With ConnectBD

        .Provider = "Microsoft.Jet.OLEDB.4.0"

        .ConnectionString = FichierBase
        .Open

    End With

End Sub
Le 16/03/2018 à 18:29

oui ca fonctionne mais ca résouds pas mon problème ^^^

Car en faite, cette macro permet de créer cette table sans préciser directement la source de la BDD acces mais quand cette table est créer sur le classeur, elle prends le lien qui est sur le pc avec laquel on créer cette table mais si on ouvre le fichier sur un autre pc, ca sera toujours le même lien que sur le pc d'orgine, donc la table ne fonctionne plus sauf si on relance cette macro, j'arrive donc au même points. Une autre piste?

Le 17/03/2018 à 07:38

Bonjour,

Tu dis :

J'ai trouvé finalement une autre solution qui fonctionne avec une fonction qui recherche le répertoire ou se trouve le fichier et l'affiche sur une cellule, j'utilise cette cellule pour établir mes liens dans mes macros, ca fonctionne.

Et maintenant tu dis :

...mais quand cette table est créer sur le classeur, elle prends le lien qui est sur le pc avec laquel on créer cette table mais si on ouvre le fichier sur un autre pc, ca sera toujours le même lien que sur le pc d'orgine...

J'ai du mal à te suivre !

Le 17/03/2018 à 08:30

C'est bien ca quand j'ouvre le fichier sur un autre pc, j'ai une fonction qui détermine ou est ce fichier dans une cellule

Si j'utilise ta macros, ca crée bien la table avec le lien défini dans la celulle mais si entre temps je change de pc, ce n'est pas pour ce que cette table se réactualise avec le lien de cellule, la table garde le lien du fichier sur le pc avec lequel on a lancé la macro pour créer une première fois cette table.

Donc j'ai le même problème, le lien n'est plus correct, sauf bien sûr si je relance cette macro mais ca veut dire que je peux pas utiliser un même fichier sur deux pc différents ou a chaque fois je dois relancer s cette macro pour avoir la table correcte mais il y a 7/8 tables comme ceci, c'est un peu lourds comme fonctionnement.

Moi j'aimerai si c'est possible bien sûr, que les paramétrés de l'affichage de la table intégre le fait d'aller à chaque fois chercher le lien sur cette cellule qui est mis à jour automatiquement à l'ouverture du classeur, ainsi les tables sont toujours correctes, peu importe le pc avec lequel ou l'ouvre.

Le 17/03/2018 à 12:16

Donc, on en revient à ce que j'ai proposé dès le début à savoir, demander le chemin à l'utilisateur la première fois ou alors, utiliser une fonction de scan pour trouver la base de données mais selon la taille du ou des disques de stockage ça risque de prendre des plombes !

Donc, demander le chemin du dossier et le mémoriser dans ta cellule au lieu de la base de registre :

Private Sub Workbook_Open()

    Dim Chemin As String

    Chemin = Worksheets("Feuil1").Range("A1").Value 'à adapter !

    If Chemin = "" Then

        If MsgBox("Voulez-vous définir le chemin par defaut de la base de données Access ?", vbQuestion + vbYesNo) = vbYes Then

            With Application.FileDialog(4)

                If .Show = -1 Then

                    Chemin = .SelectedItems(1) & "\"
                    Worksheets("Feuil1").Range("A1").Value = Chemin

                End If

            End With

        End If

    End If

    MsgBox Chemin

    'ici la suite de ton code comme par exemple l'ouverture de la base de données...
    '...
    '...

End Sub
Rechercher des sujets similaires à "liaison relatif"