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: 4
  • TEST_RECAP.xlsx
    11.2 KB · Affichages: 4

Frensoa

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

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

1572337894486.png
 
Dernière édition:

patricktoulon

XLDnaute Barbatruc
Bonjour Frensoa


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

XLDnaute Nouveau
Bonjour Frensoa


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

Discussions similaires

Membres actuellement en ligne

Statistiques des forums

Discussions
287 536
Messages
1 884 452
Membres
163 314
dernier inscrit
cedric.remacle
Haut Bas