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

Frensoa

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

Pièces jointes

  • FH_2019.zip
    38.8 KB · Affichages: 5
  • TEST_RECAP.xlsx
    11.2 KB · Affichages: 7

xUpsilon

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

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

XLDnaute Barbatruc
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
VB:
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)
;)
 
Dernière édition:

Frensoa

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

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

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

1572277687470.png
 

Frensoa

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

XLDnaute Barbatruc
ok je regarde

bon ben voila
VB:
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

demo3.gif
 

Frensoa

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

Discussions similaires