XL 2016 lien vers une cellule dans un fichier externe à partir d'une cellule contenant le nom du fichier

[Frensoa]

Bonjour,
je cherche à faire le lien entre les cellules de fichiers externes vers une feuille principale. tous mes fichiers cibles sont au même format c'est à dire que je cherche les valeurs toujours au même endroit dans le même onglet de mes fichiers cibles. la seule chose qui change c'est le nom du fichier (et potentiellement le chemin chaque année).
je voudrait pouvoir avoir une formule générique faisant référence à une cellule contenant le nom du fichier (colonne B)(et éventuellement une autre pour le chemin d'accès) pour n'avoir qu'à actualiser cette cellule à chaque création de fichier.
aujourd’hui je suis obligé manuellement d'ouvrir chaque fichier créé, d'aller chercher la cellule qui m'intéresse et ensuite d'étirer ma formule pour recopier le lien sur la plage qui m’intéresse:
='C:\FH_2019\[FH_2019_AT02.xlsx]DATA'!L5
en essayant un concatener du type =CONCATENER("C:\FH_2019";CELLULE("contenu";$B4);"DATA'!";"L5") cela ne calcule rien et me renvoi simplement le texte du concatener (en B4 le nom du fichier cible)

voir le fichier ZIP avec les fichiers sources. sur TEST_RECAP.xlsx la formule que je cherche à travailler.

si quelqu'un a une piste je suis preneur.

bonne journée.

François

 

[patricktoulon]

bonjour
fait le concat dans une fonction vba tu te cassera moins la tete
OU
crée un name avec la formule et dans ta cellule "=lenomduname"

 

[xUpsilon]

Bonjour,

Bien d'accord avec patrick, les & sont quand même plus simples à employer, et surtout le fait de stocker directement ton chemin dans une variable rend l'utilisation de celle-ci bien plus pratique qu'en passant par la valeur d'une cellule. Quand tu manipules plusieurs classeurs, pour garder de la visibilité, il ne faut pas hésiter à faire des déclarations de variables claires dans son code, plutot que d'appeler on ne sait trop quoi à chaque fois et devoir revérifier 20 fois ses termes

Bonne continuation

 

[Frensoa]

bonjour
fait le concat dans une fonction vba tu te cassera moins la tete
OU
crée un name avec la formule et dans ta cellule "=lenomduname"

Bonjour,
merci pour ces réponses à tous les deux. je ne suis pas familier du VBA (ça viendra un jour), par contre pour la deuxième partie de la réponse, pourriez-vous être plus explicite svp? je ne vois pas bien comment mettre en œuvre cette solution.

 

[patricktoulon]

re
je suis en train de tester laisse moi quelques minutes

 

[patricktoulon]

re
bon je viens d'essayer mes deux méthodes et en formule elle renvoie #VALEUR alors q'en vba elle donne le bon résultat
donc si quelqu'un connait la solution ...
sinon se sera VBA

Public Function GetVal_on_closed_fich(ByVal chemin As String, ByVal fichier As String, ByVal feuille As String, cel As String) As Variant
    Dim Rc$
    MsgBox cel
    Rc = "R" & Range(cel).Row & "C" & Range(cel).Column
     GetVal_on_closed_fich = ExecuteExcel4Macro("'" & chemin & "\[" & fichier & "]" & feuille & "'!" & Rc)
End Function

Sub test()
    MsgBox GetVal_on_closed_fich("C:\Users\polux\DeskTop\", "cible.xlsx", "Feuil1", [B4].Value)
End Sub

exemple
j'ai "D6" ecrit en [B4]
je vais donc chercher la valeur de D6 dans un fichier
je teste la meme chose avec evaluate (rien de concluant pour le moment)

 

[Frensoa]

je suis vraiment trop ignorant en VBA, je n'arrive pas à comprendre le cheminement du code.

à tout hasard, j'ai tenté une autre solution (à mon niveau en VBA) qui ne donne pas le résultat attendu mais je ne sais pas si il y aurait une astuce pour la rendre efficiente:
dans ma colonne nom de fichier, j'ai fait un lien hypertexte vers le fichier source (en B4). ensuite, je me suis mis en D4 et j'ai essayé la fonction "enregistrement de macro". ensuite j'ai cliqué sur mon lien hypertexte puis en D4 j'ai fait = puis cliqué sur la cellule qui m'intéresse dans le fichier cible, puis F4 pour virer les $ en trop puis étirer ma formule. // fin de macro.
quand j'execute ensuite la macro, ça crée bien les formules, mais ça fait toujours référence au lien du fichier qui était en B4 au moment de la création de la macro même si je change ensuite le contenu de B4 et que je relance la macro ensuite.

Sub test_import_valeur_V2()
'
' test_import_valeur_V2 Macro
'

'
    ActiveCell.Offset(0, -2).Range("A1").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Windows("TEST_RECAP.xlsx").Activate
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=[FH_2019_AT01.xlsx]DATA!R5C[8]"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:BB1"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:BB1").Select
End Sub

je ne sais pas si ça peut être une piste...

en tout cas merci pour votre temps.

 

[Frensoa]

ci-dessous le format des deux fichiers (en haut le fichier récap, en bas le fichier source)

 

[patricktoulon]

re
quézacoca!!???

ActiveCell.Offset(0, -2).Range("A1").Select

 

[Frensoa]

re
quézacoca!!???

ActiveCell.Offset(0, -2).Range("A1").Select

je n'en sais rien, je ne comprend pas le VBA, je n'ai pas les bases. c'est le code généré par excel quand je fais enregistrer les actions.

 

[patricktoulon]

re
et oui mais moi j'ai pas tes fichiers et et je ne sais pas de ou tu est parti
et je ne vois pas comment l'enregistreur te donne cette ligne c'est pas possible ou j'ai zapé un truc la

 

[patricktoulon]

bon on va faire court
repond a cette question
est ce bien la plage "L2:BM2" que tu veux récupérer en première ligne ????

 

[Frensoa]

bon on va faire court
repond a cette question
est ce bien la plage "L2:BM2" que tu veux récupérer en première ligne ????

j'avais mis mes fichiers de bases en pièce jointe de mon premier message.

L2:BM2 c'est juste un intitulé, les valeurs que je cherche à récupérer, ce sont celles contenues dans les cellules L5:BM5 de l'onglet DATA dans mes fichiers cibles.
je cherche à les coller dans mon fichier récap sur la plage D4:BE4 pour la première cible, D5:BE5 pour la seconde, D6:BE6 pour la troisième, etc..

 

[patricktoulon]

ok je regarde

bon ben voila

Sub test()
    Application.ScreenUpdating = False
    dossier = ThisWorkbook.Path'remplace ici par  le chemin de ton dossier si les fichier ne sont pas dans le même dossier que recap
    For i = 4 To Cells(Rows.Count, "A").End(xlUp).Row
        fichier = Cells(i, "B")
        Set plage1 = Range(Cells(i, "D"), Cells(i, "BE"))
        Set plage2 = Range("L5:BM5")    'plage source
        For c = 1 To plage1.Columns.Count
            Set cel = plage2.Cells(c)
            Rc = "R" & cel.Row & "C" & cel.Column
            plage1.Cells(c).Formula = "='" & dossier & "\[" & fichier & "]DATA'!" & Rc
        Next c
        plage1.Value = plage1.Value
    Next i
End Sub

testé sur les exemple fournis

 

[Frensoa]

ok, j'arrive à peu près à comprendre là.
en revanche, ce code colle la valeur, mais moi je voudrais que ça crée un lien dynamique vers la cellule cible puisque les fichiers sources sont mis à jour continuellement.
j'aimerais exécuter cette commande uniquement à chaque création de ligne (qui s'étale ponctuellement sur l'année) et donc de fichier.
par ailleurs, y a-t-il une combine pour interrompre le code en cas d'erreur? (j'ai essayé de lancer le code avec des fichiers manquants et ce n'était pas une bonne idée)
en tous cas, il faut vraiment que je me forme au VBA.

mais c'est quand même fou qu'on ne puisse pas génerer cette adresse simplement via une formule se référant à la valeur d'une cellule!

merci en tout cas pour votre temps sur le sujet.