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]

heu...
ben supprime cette ligne
plage1.Value = plage1.Value
et regarde les formule dans les cellule

 

[patricktoulon]

bon tiens c'est automatique
tu rempli A,B,C le reste se fait tout seul

 

[Frensoa]

incroyable! mais pour moi c'est encore de la magie, je ne comprend pas comment vous faites ça.

pouvez-vous m'expliquer un peu la logique du code svp (pour que je puisse le faire évoluer si j'ai des changements de colones, adresses, etc)?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <= 3 Then
        r = Target.Row
        critere = Cells(r, 1) <> "" And Cells(r, 2) <> "" And Cells(r, 3) <> ""
         If critere Then mise_a_jour r Else Cells(r, "D").Resize(, 54).ClearContents
    End If
End Sub

pourquoi le code s'applique-t-il automatiquement? comment sait-il quelles cases sont à remplir? si les cases à remplir changent comment je les adresse? idem pour les cases sources? idem pour la localisation du nom de fichier? pourquoi le fichier ne semble pas avoir de macro à exécuter alors qu'il y a un code indiqué sur "worksheet"? pour les novices en VBA comme moi s'est très difficile et très frustrant de ne pas comprendre les tenants et aboutissant d'un tel code.

merci vraiment beaucoup en tout cas pour la solution.

EDIT:
je n'avais pas vu le code en arrière plan (Général) // mise_a_jour

Sub mise_a_jour(i)
    Dim D$, F$
    Application.ScreenUpdating = False
    D = ThisWorkbook.Path
    F = 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
        plage1.Cells(c).Formula = "='" & D & "\[" & F & "]" & Cells(i, 3) & "'!" & plage2.Cells(c).Address
    Next c
    'plage1.Value = plage1.Value'supprime les formule
End Sub

c'est un peu plus clair comme ça.

en revanche, si il ne trouve pas le fichier source, il n'y a pas moyen d'interrompre l'exécution du code.

 

[patricktoulon]

Bonjour Frensoa

Private Sub Worksheet_Change(ByVal Target As Range) 'événement qui se se produit quand une cellule change dans la feuille
    Dim R As Long
    If Target.Column <= 3 Then 'si la colonne de la cellule qui vient d’être modifiée est < que 3
        R = Target.Row 'la variable R représente la ligne de la cellule
        critere = Cells(R, 1) <> "" And Cells(R, 2) <> "" And Cells(R, 3) <> "" ' critere sera un booleen(true ou false) selon si les 3 cellule sont rempli ou pas
         If critere Then mise_a_jour R Else Cells(R, "D").Resize(, 54).ClearContents 'si les 3 sont rempli on appelle mise a jour sinon on vide la ligne
    End If
End Sub

au cas ou le fichier n’existerait pas

Sub mise_a_jour(i)
    Dim D$, F$
    D = ThisWorkbook.Path    ' le dossier ou se trouve le classeur exécutant  lui  macro lui même
    F = Cells(i, "B")    ' le nom du fichier en "B"
    If Dir(D & F) = "" Then MsgBox " ce fichier n'existe pas on ne va pas plus loin": Exit Sub    ' si le fichier n'existe pas
    Application.ScreenUpdating = False    ' blocage du rafraîchissement d'ecran (accélère le processus)
    Set plage1 = Range(Cells(i, "D"), Cells(i, "BE"))    'création dune variable plage1 désignant la plage "D4:BE4" 4 étant désigne par i qui a été injecté par l’événement change dans l'autre module( de la feuille)
    Set plage2 = Range("L5:BM5")    '    creation variable plage 2 designant la plage source
    For c = 1 To plage1.Columns.Count    ' on boucle de la 1ere colonne de la plage 1  a la derniere
        plage1.Cells(c).Formula = "='" & D & "\[" & F & "]" & Cells(i, 3) & "'!" & plage2.Cells(c).Address    'la formule dans la cellule (x) = formule et adresse de la cellule(meme x) de la plage source
    Next c
    'plage1.Value = plage1.Value 'supprime les formules et mais laisse les valeurs (facultatif )
End Sub

 

[Frensoa]

Bonjour Frensoa

Private Sub Worksheet_Change(ByVal Target As Range) 'événement qui se se produit quand une cellule change dans la feuille
    Dim R As Long
    If Target.Column <= 3 Then 'si la colonne de la cellule qui vient d’être modifiée est < que 3
        R = Target.Row 'la variable R représente la ligne de la cellule
        critere = Cells(R, 1) <> "" And Cells(R, 2) <> "" And Cells(R, 3) <> "" ' critere sera un booleen(true ou false) selon si les 3 cellule sont rempli ou pas
         If critere Then mise_a_jour R Else Cells(R, "D").Resize(, 54).ClearContents 'si les 3 sont rempli on appelle mise a jour sinon on vide la ligne
    End If
End Sub

au cas ou le fichier n’existerait pas

Sub mise_a_jour(i)
    Dim D$, F$
    D = ThisWorkbook.Path    ' le dossier ou se trouve le classeur exécutant  lui  macro lui même
    F = Cells(i, "B")    ' le nom du fichier en "B"
    If Dir(D & F) = "" Then MsgBox " ce fichier n'existe pas on ne va pas plus loin": Exit Sub    ' si le fichier n'existe pas
    Application.ScreenUpdating = False    ' blocage du rafraîchissement d'ecran (accélère le processus)
    Set plage1 = Range(Cells(i, "D"), Cells(i, "BE"))    'création dune variable plage1 désignant la plage "D4:BE4" 4 étant désigne par i qui a été injecté par l’événement change dans l'autre module( de la feuille)
    Set plage2 = Range("L5:BM5")    '    creation variable plage 2 designant la plage source
    For c = 1 To plage1.Columns.Count    ' on boucle de la 1ere colonne de la plage 1  a la derniere
        plage1.Cells(c).Formula = "='" & D & "\[" & F & "]" & Cells(i, 3) & "'!" & plage2.Cells(c).Address    'la formule dans la cellule (x) = formule et adresse de la cellule(meme x) de la plage source
    Next c
    'plage1.Value = plage1.Value 'supprime les formules et mais laisse les valeurs (facultatif )
End Sub

c'est beaucoup plus clair comme ça! merci beaucoup!