lien entre fichier avec nom de feuille variable

BZH56

BZH56

XLDnaute Occasionnel
Bonjour le forum
dans un fichier résultat (test1) , je veux récupérer par liaison le contenu d'une feuille d' un autre fichier (test2)en sachant que cette feuille sera sélectionnée en tapant son nom(ce sera une liste déroulante).
exemple : dans mon fichier résultat , je tape 'janvier ' et je récupère les données de janvier de l'autre fichier etc..
voir PJ

Précision importante: le 2 ème fichier (test 2)peut être ferme et la fonction Indirect ne marche qu'avec un fichier ouvert.. cela sent le VBA incontournable..

normalement le numéro de ligne sera toujours le même,mais au cas ou, comment faire pour se positionner sur la première ligne des données
Merci et bonne journée a tous
BZH56
 

Pièces jointes

  • test1.xls
    50.5 KB · Affichages: 52
  • test2.xls
    43.5 KB · Affichages: 51
  • test1.xls
    50.5 KB · Affichages: 55
  • test2.xls
    43.5 KB · Affichages: 60
  • test1.xls
    50.5 KB · Affichages: 60
  • test2.xls
    43.5 KB · Affichages: 59
Dernière édition:
Trier par date Trier par votes
K

kjin

XLDnaute Barbatruc
Re : lien entre fichier avec nom de feuille variable

Bonjour,
Dans le module de la feuille....
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Byte, j As Byte
If Target.Address <> "$C$2" Then Exit Sub
For i = 3 To 11
    For j = 5 To 35
        With Cells(j, i)
            .FormulaR1C1 = "='x:\xxx\xxx\[test2.xls]" & [C2] & "'!R" & j - 3 & "C" & i
'            .Value = .Value 'décoche si tu souhaites ne conserver que la valeur
        End With
    Next
Next
End Sub
....en remplaçant x:\xxx\xxx\ par le véritable chemin du fichier test2.xls et en t'assurant au préalable de la concordance des noms de feuilles (Février et Fevrier !)
A+
kjin
 
Vote positif 0 Vote négatif
BZH56

BZH56

XLDnaute Occasionnel
Re : lien entre fichier avec nom de feuille variable

kjin à dit:
Bonjour,
Dans le module de la feuille....
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Byte, j As Byte
If Target.Address <> "$C$2" Then Exit Sub
For i = 3 To 11
    For j = 5 To 35
        With Cells(j, i)
            .FormulaR1C1 = "='x:\xxx\xxx\[test2.xls]" & [C2] & "'!R" & j - 3 & "C" & i
'            .Value = .Value 'décoche si tu souhaites ne conserver que la valeur
        End With
    Next
Next
End Sub
....en remplaçant x:\xxx\xxx\ par le véritable chemin du fichier test2.xls et en t'assurant au préalable de la concordance des noms de feuilles (Février et Fevrier !)
A+
kjin
Cliquez pour agrandir...

:rolleyes::rolleyes:merci Kjin , je récupère bien les données de l'autre classeur mais peut on améliorer car ce code oblige a saisir en C2 pour actualiser... Si le mois ne change pas , comment actualiser les données modifiées sur l'autre classeur comme avec un copier /collage spécial /liaison
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lien entre fichier avec nom de feuille variable

Bonjour BZH56, salut kjin,

Une autre solution est de conserver les formules de liaisons et de modifier juste le mois.

Dans le code de la feuille :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Byte, mois As String
If Intersect(Target, [C2]) Is Nothing Then Exit Sub
For i = 1 To 12
  mois = Format("1/" & i, "mmmm")
  If LCase([C2]) = mois Then GoTo 1
Next
[C2] = "Janvier": Exit Sub
1 For i = 1 To 12
  mois = Format("1/" & i, "mmmm")
  If LCase([C5].Formula) Like "*]" & mois & "'!*" Then Exit For
Next
Application.DisplayAlerts = False 'si le chemin d'accès n'est pas correct
[C:K].Replace mois, [C2], xlPart
End Sub
A+
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lien entre fichier avec nom de feuille variable

Re,

Au cas où il y aurait des noms de mois dans le chemin d'accès, il vaut mieux utiliser :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Byte, mois As String
If Intersect(Target, [C2]) Is Nothing Then Exit Sub
For i = 1 To 12
  mois = Format("1/" & i, "mmmm")
  If LCase([C2]) = mois Then GoTo 1
Next
[C2] = "Janvier": Exit Sub
1 For i = 1 To 12
  mois = Format("1/" & i, "mmmm")
  If LCase([C5].Formula) Like "*]" & mois & "'!*" Then Exit For
Next
Application.DisplayAlerts = False 'si le chemin d'accès n'est pas correct
[C5:K35].Replace "]" & mois & "'!", "]" & [C2] & "'!", xlPart
End Sub
A+
 
Dernière édition:
Vote positif 0 Vote négatif
BZH56

BZH56

XLDnaute Occasionnel
Re : lien entre fichier avec nom de feuille variable

Bonjour JOB75
Merci de ta contribution et en attendant , j'avais crée un bouton de rafraichissement qui affiche aussi la date , heure de la dernière actualisation.
J ai voulu insérer ton test de chemin d’accès et là... bug...
pour précision , les feuilles mois sont bien dans un classeur unique

le code de Kjin75 avec mes ajouts
Code: 
Private Sub CommandButton1_Click() 'Bouton de rafraichissement de données
Application.ScreenUpdating = False

Range("C2").Select
    Selection.Copy
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        
    Range("G1").Select              'affichage de la derniere actualisation
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("C2").Select
   
Application.ScreenUpdating = True
     
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Byte, j As Byte
If Target.Address <> "$C$2" Then Exit Sub
'Application.DisplayAlerts = False 'si le chemin d'accès n'est pas correct
For i = 3 To 5
    For j = 5 To 7
        With Cells(j, i)
            .FormulaR1C1 = "='C:\Users\papa\Documents\[test2.xls]" & [C2] & "'!R" & j - 3 & "C" & i
            .Value = .Value 
        End With
    Next
Next


End Sub

Qu'en penses tu ?
merci
A@
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lien entre fichier avec nom de feuille variable

Bonjour le fil, le forum,

Si tous les mois ne sont pas créés dans le fichier source, il vaut mieux ceci :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [C2]) Is Nothing Then Exit Sub
Dim F$, F1$, i As Byte, mois$
F = Mid([C5].Formula, 2)
F1 = Left(F, InStrRev(F, "]")) & [C2] & "'!R65536C256"
If IsError(ExecuteExcel4Macro(F1)) Then
  F1 = Left(F, InStrRev(F, "]")) & "Janvier'!R65536C256"
  If Not IsError(ExecuteExcel4Macro(F1)) Then [C2] = "Janvier"
  Exit Sub
End If
For i = 1 To 12
  mois = Format("1/" & i, "mmmm")
  If LCase(F) Like "*]" & mois & "'!*" Then Exit For
Next
Application.ScreenUpdating = False
[C5:K35].Replace "]" & mois & "'!", "]" & [C2] & "'!", xlPart
End Sub
A+
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lien entre fichier avec nom de feuille variable

Bonjour BZH56,

Nos messages se sont croisés.

A mon avis, plutôt qu'un bouton, il vaut mieux une liste de validation (avec les mois) en C2.

Ma macro est faite justement pour ça.

A+
 
Vote positif 0 Vote négatif
BZH56

BZH56

XLDnaute Occasionnel
Re : lien entre fichier avec nom de feuille variable

job75 à dit:
Bonjour BZH56,

Nos messages se sont croisés.

A mon avis, plutôt qu'un bouton, il vaut mieux une liste de validation (avec les mois) en C2.

Ma macro est faite justement pour ça.

A+
Cliquez pour agrandir...

Job75,
en effet , j'avais effectivement déjà rajoute la liste de validation pour éviter ces problèmes d’accès . par contre
pour le rafraichissement des données , lorsque le gestionnaire a saisi son choix de mois , il ne va plus rien sélectionner pendant tous le mois courant courant alors que le fichier peut évoluer plusieurs par jour.
Actuellement , je fais un copier/collage spécial/avec liaison mais la formule est 'en dur' et le mois suivant , si j'oublie de réactualiser la liaison avec le mois suivant , le fichier renvoie toujours les données du mois précèdent .
J'essaie donc d'automatiser tout cela ..
A+
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lien entre fichier avec nom de feuille variable

Re,

Je ne comprends pas ce que vous voulez dire.

Paramétrez la liaison pour que quand vous ouvrez le fichier test1.xls les valeurs se mettent à jour automatiquement.

Mais je vois que ma macro précédente ne fonctionne pas si test2.xls est ouvert.

Alors utiliser :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [C2]) Is Nothing Then Exit Sub
Dim F$, t As Boolean, F1$, F2$, v, i As Byte, mois$
F = Mid([C5].Formula, 2)
t = InStr(F, "'!")
F1 = Left(F, InStrRev(F, "]")) & [C2] & "'!R65536C256"
F2 = Left(F, InStrRev(F, "]")) & [C2] & "!IV65536"
If t Then v = ExecuteExcel4Macro(F1) Else v = Evaluate(F2)
If IsError(v) Then
  F1 = Left(F, InStrRev(F, "]")) & "Janvier" & "'!R65536C256"
  F2 = Left(F, InStrRev(F, "]")) & "Janvier" & "!IV65536"
  If t Then v = ExecuteExcel4Macro(F1) Else v = Evaluate(F2)
  If Not IsError(v) Then [C2] = "Janvier"
  Exit Sub
End If
For i = 1 To 12
  mois = Format("1/" & i, "mmmm")
  If LCase(F) Like "*]" & mois & "*" Then Exit For
Next
Application.ScreenUpdating = False
[C5:K35].Replace "]" & mois, "]" & [C2], xlPart
End Sub
A+
 
Vote positif 0 Vote négatif
BZH56

BZH56

XLDnaute Occasionnel
Re : lien entre fichier avec nom de feuille variable

job75 à dit:
Re,

Je ne comprends pas ce que vous voulez dire.

Paramétrez la liaison pour que quand vous ouvrez le fichier test1.xls les valeurs se mettent à jour automatiquement.

Mais je vois que ma macro précédente ne fonctionne pas si test2.xls est ouvert.

Alors utiliser :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [C2]) Is Nothing Then Exit Sub
Dim F$, t As Boolean, F1$, F2$, v, i As Byte, mois$
F = Mid([C5].Formula, 2)
t = InStr(F, "'!")
F1 = Left(F, InStrRev(F, "]")) & [C2] & "'!R65536C256"
F2 = Left(F, InStrRev(F, "]")) & [C2] & "!IV65536"
If t Then v = ExecuteExcel4Macro(F1) Else v = Evaluate(F2)
If IsError(v) Then
  F1 = Left(F, InStrRev(F, "]")) & "Janvier" & "'!R65536C256"
  F2 = Left(F, InStrRev(F, "]")) & "Janvier" & "!IV65536"
  If t Then v = ExecuteExcel4Macro(F1) Else v = Evaluate(F2)
  If Not IsError(v) Then [C2] = "Janvier"
  Exit Sub
End If
For i = 1 To 12
  mois = Format("1/" & i, "mmmm")
  If LCase(F) Like "*]" & mois & "*" Then Exit For
Next
Application.ScreenUpdating = False
[C5:K35].Replace "]" & mois, "]" & [C2], xlPart
End Sub
A+
Cliquez pour agrandir...

merci de ces infos et pour terminer ce post , juste une dernière demande
Vous me dites "Paramétrez la liaison !!!" et là, je ne comprends pas et je ne sais pas faire..
a+
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : lien entre fichier avec nom de feuille variable

Re,

Je suis sous Excel 2010 mais ce doit être pareil sous 2007.

Onglet Données => Modifier les liens => Invite de démarrage et choisissez l'option qui vous convient.

A+
 
Vote positif 0 Vote négatif

Discussions similaires

L
  • Résolu(e)
Microsoft 365 Pb de valeur de variable entre Sub
Réponses
5
Affichages
203
loreak
L
A
  • Résolu(e)
XL 2016 Macros sur nom de fichier variable
Réponses
5
Affichages
416
oguruma
oguruma
R
  • Résolu(e)
Microsoft 365 Lien variable dans nom feuille classeur
Réponses
6
Affichages
503
randle008
R
G
  • Question
XL 2019 Tableau VBA, lien entre fichiers
Réponses
5
Affichages
354
Compte Supprimé 979
C
B
  • Question
XL 2013 Transposition de filtres multiples sur plusieurs feuilles
Réponses
11
Affichages
279
BIROULIG
B
G
  • Question
Microsoft 365 Saut de ligne dans une cellule
Réponses
3
Affichages
158
GClaire
G
M
  • Question
XL 2010 Récupération de données et les transformer avec Power Query ?
Réponses
3
Affichages
259
DJunqueira
D
F
  • Question
XL 2019 Remplissage de champs dans doc Word à partir d'excel
Réponses
4
Affichages
520
Staple1600
Staple1600
H
  • Question
Autres mise a jour automatique feuille excel vba
Réponses
2
Affichages
298
HANN
H
V
  • Question
XL 2019 ActiveSheet avec nom feuille contenu dans une cellule
Réponses
3
Affichages
578
Staple1600
Staple1600

Membres actuellement en ligne

13 membres et 111 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 318
Messages
2 087 209
Membres
103 493
dernier inscrit
Vidal Salvador