Créer une liaison entre deux classeurs excel

Résolu

plastickman Messages postés 64 Date d'inscription Statut Membre Dernière intervention -
plastickman Messages postés 64 Date d'inscription Statut Membre Dernière intervention - 9 nov. 2011 à 12:38

Bonjour à tous,
Au travail, j'utilise un classeur excel par client avec toutes les informations qui lui sont dédiées.
J'en ai créé un nouveau appelé "résultat" que je place dans le même dossier que les tous les autres qui me sert à synthétiser les principales informations de chaque classeur (client). Pour récupérer les infos dans les cellules qui m'intéressent j'utilise le code suivant:

Sub ExtractRefresh()
Dim ScanFic As Office.FileSearch
Dim NomFic As Variant
Dim Nbr As Long
Set ScanFic = Application.FileSearch
With ScanFic
.NewSearch
.LookIn = ActiveWorkbook.Path
.SearchSubFolders = True
.Filename = xls
.MatchTextExactly = True

Nbr = .Execute
For Each NomFic In .FoundFiles
If Right(NomFic, 13) = "resultats.xls" Then GoTo suite
Workbooks.Open Filename:=NomFic
With Workbooks("resultats.xls").Sheets("diagnostics")
.Range("D65536").End(xlUp).Offset(1, 0).Value = ActiveWorkbook.Sheets("Renseignements").Range("D17").Value
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
suite:
Next
End With
End Sub

Ce dernier marche très bien, mais il m'oblige à le relancer dès que j'ai effectué une modification dans un classeur. Je souhaiterais remplacer les valeurs importer dans "résultat" par un lien vers la cellule d'origine, de façon à ce qu'elle soit automatiquement modifiée à l'ouverture suivante lorsque il y à lieu. Je ne sais pas par quoi remplacer le ".value" de la ligne:

ActiveWorkbook.Sheets("Renseignements").Range("D17").Value

pour que ça fonctionne.
Merci encore une fois pour votre aide,

jp

A voir également:

Liaison entre classeur excel
Liste déroulante excel - Guide
Word et excel gratuit - Guide
Déplacer colonne excel - Guide
Si ou excel - Guide
Nombre de jours entre deux dates excel - Guide

8 réponses

Réponse 1 / 8

plastickman Messages postés 64 Date d'inscription Statut Membre Dernière intervention 6

Je ne sais pas comment te remercier.
Tout marche parfaitement bien et en plus j'ai appris un paquet de chose.
J'ai un super outil maintenant!!!
Encore merci,

jp

Réponse 2 / 8

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

essaie avec :
.Range("D65536").End(xlUp).Offset(1, 0).Formula = "='D:\ton chemin\[ton fichier]Renseignements'!$A$1"

eric

Réponse 3 / 8

plastickman Messages postés 64 Date d'inscription Statut Membre Dernière intervention 6

Bonjour,
Ce code fonctionne bien mais le problème est qu'il me demande de préciser manuellement chaque nom de fichier (j'en ai environ 700...). J'ai essayé la ligne suivante, mais ça ne marche pas:

.Range("D65536").End(xlUp).Offset(1, 0).Formula = "=' C:\Documents and Settings\jp\Bureau\EXERCICE 2012\test" & ActiveWorkbook.Name & " \Renseignements'!A1"

Qu'en penses-tu?

Réponse 4 / 8

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

Avec ton nom de fichier xxx.xls dans une variable fich par exemple ça serait :
.Range("D65536").End(xlUp).Offset(1, 0).Formula = "='D:\ton chemin\[" & fich & "]Renseignements'!$A$1"

eric

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 8

plastickman Messages postés 64 Date d'inscription Statut Membre Dernière intervention 6

J'ai utilisé la variable "NomFic" et ça ne marche toujours pas.

.Range("D65536").End(xlUp).Offset(1, 0).Formula = "='D:\ton chemin\[" & NomFic & "]Renseignements'!$A$1"

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

ça ne marche pas ne veut rien dire...
Ca te donne quoi comme formule dans la cellule ?
eric

Réponse 6 / 8

plastickman Messages postés 64 Date d'inscription Statut Membre Dernière intervention 6

C'est vrai que je n'ai pas été très clair...
Voilà la formule dans la première cellule de la colonne A, "111103" étant le nom d'un fichier (client):

=' C:\Documents and Settings\jp\Bureau\EXERCICE 2012\test\[C:\Documents and Settings\jp\Bureau\EXERCICE 2012\test\111103\111103.xls] \[Renseignements]Renseignements'!A1

J'en déduit que ma variable "NomFic" intègre déjà le chemin (ce que je ne comprends pas...) et je n'explique pas pourquoi on retrouve également [Renseignements] avant "Renseignements A1.

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

J'avais écrit Avec ton nom de fichier xxx.xls dans une variable fich par exemple
Donc traite la chaine :
par exemple si ton nom de fichier est toujours de la forme 123456.xls alors :
fich = right(nomfich,10)

eric

Réponse 7 / 8

plastickman Messages postés 64 Date d'inscription Statut Membre Dernière intervention 6

Bon, je crois que ce n'est pas gagné...
J'ai rajouté en tête:

Dim Fich As Variant

en suite, comme le nom de mes fichiers ne fait pas toujours le nombre de caractères j'ai mis:


Fich = ActiveWorkbook.Name
.Range("D65536").End(xlUp).Offset(1, 0).Formula = "=' C:\Documents and Settings\jp\Bureau\EXERCICE 2012\test\[" & Fich & " ]Renseignements'!A1"

J'obtiens dans ma cellule:

='C:\Documents and Settings\jp\Bureau\EXERCICE 2012\test\[111103 nom du client.xls]Renseignements'!A1

Ce qui me semble bon, et pourtant, ça ne trouve pas la valeur de cellule concernée, et pire, si je sauvegarde, le chemin indiqué dans ma cellule est écrit deux fois lorsque j'ouvre à nouveau mon classeur, ce qui donne:

='C:\Documents and Settings\jp\Bureau\EXERCICE 2012\test\ C:\Documents and Settings\jp\Bureau\EXERCICE 2012\test\[111103 nom du client.xls ]Renseignements'!A1

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

Fabrique tes formules avec les fichiers concernés fermés, ça devrait le faire.
J'ai cru lire qcq part que tu en avais 200, tu ne vas peut-être pas tous les ouvrir...

Si le fichier est ouvert la partie chemin est masquée par excel et rajoutée à sa fermeture. Dans ce cas la formule est : =[Classeur1]Feuil1!$A$1

Réponse 8 / 8

plastickman Messages postés 64 Date d'inscription Statut Membre Dernière intervention 6

Bonjour,
J'ai bien cru que c'était bon, mais c'était trop beau...
J'ai fait comme tu m'as dit:
J'ai conservé en tête de code:

Dim Fich As Variant

En suite, j'ai transformé ma ligne:

Fich = ActiveWorkbook.Name
.Range("D65536").End(xlUp).Offset(1, 0).Formula = "=[Fich]Renseignements!A1"

Le problème étant qu'il ne trouve pas à quoi correspond ma variable "Fich", il me demande à nouveau via une fenêtre le fichier correspondant.
La bonne nouvelle est que lorsque je lui montre le classeur que je veux, la liaison se fait bien. Ce qui est surprenant, c'est que dans ma cellule je retrouve:

=[Fich]Renseignements!A1

Je m'attendais à retrouver:

=[nom du classeur choisi]Renseignements!A1

Je ne vois pas comment éviter la variable "Fich", le but de la routine étant d'ouvrir mes 700 fichiers et les répertorier les uns en dessous des autres comme base de donnée.

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

Bonjour,

Ta formule doit être une chaine de charactère.
Quand tu insères une variable il faut fermer ton début de chaine avec : "
insérer ta variable avec : & nom_de_variable &
ouvrir le début de la chaine finale avec "

donc :
Fich = ActiveWorkbook.Name
.Range("D65536").End(xlUp).Offset(1, 0).Formula = "=[" & Fich & "]Renseignements!A1"

et évite d'utiliser as variant, type tes variables :
Dim Fich As String
D'une part c'est plus rapide, d'autre part ça peut te faire ressortir des erreurs

eric

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

PS :
Tu utilises

For Each NomFic In .FoundFiles

Base toi sur nomfich, tu as tout dedans : chemin+nom_fichier.
Extrait le path et nomfich
Et n'ouvre pas le fichier, c'est une perte de temps

eric

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 275

PS2 :

Je vois que tu rames pas mal dans la manipulation des chaines et ça fait déjà 4 jours qu'on est dessus...
Sur la base de ton code du début essaie avec :

Sub ExtractRefresh() 
    Dim ScanFic As Office.FileSearch 
    Dim NomFic As Variant 
    Dim Nbr As Long, p As Long 
    Dim path As String, fich As String 
    Set ScanFic = Application.FileSearch 
    With ScanFic 
        .NewSearch 
        .LookIn = ActiveWorkbook.path 
        .SearchSubFolders = True 
        .Filename = "xls" 
        .MatchTextExactly = True 
        Nbr = .Execute 
        For Each NomFic In .FoundFiles 
            If Right(NomFic, 13) = "resultats.xls" Then GoTo suite 
            'recherche position du dernier \ 
            p = InStrRev(NomFic, "\") 
            ' extraction du path et du nom de fichier 
            path = Left(NomFic, p) 
            fich = Mid(NomFic, p + 1, 50) 
            With Workbooks("resultats.xls").Sheets("diagnostics") 
                .Range("D65536").End(xlUp).Offset(1, 0).Formula = "='" & path & "[" & fich & "]Renseignements'!$A$1" 
            End With 
suite: 
        Next 
    End With 
End Sub

C'est ça que tu voulais ?

eric

Votre réponse