XL 2019 Remplacer liaison par une variable

S

sum01

XLDnaute Occasionnel
Bonjour le Forum,

Une galère à essayer de trouver une solution pour remplacer le nom d'un fichier dans une formule qui pointe sur un autre classeur. En effet, le nom du fichier change chaque mois. Lorsque on change de mois, il faut modifier manuellement la formule et modifier le nom du fichier avec la bonne date. Je suis parti du principe quant créer un petite table dans un fichier et je fais pointer la formule sur cette petite table pour récupérer le nom du fichier, cela sera bien plus simple que de retoucher les formule.

Au lieu d'avoir
=SOMME.SI([20230930_variable.xlsx]TableExemple2!$A$1:$B$20;A1;[20230930_variable.xlsx]TableExemple2!$B$1:$B$20)
Mais plutôt
=SOMME.SI([TABLE'!A1]TableExemple2!$A$1:$B$20;A1;[TABLE'!A1]TableExemple2!$B$1:$B$20)

Cela me permettrait de ne modifier que la table est de mettre à jour tout les autres fichiers qui vont pointer sur ce fichier principale
Merci d'avance pour votre aide
 

Pièces jointes

  • 20230930_variablerecup.xlsx
    11.1 KB · Affichages: 5
  • 20230930_variable.xlsx
    8.8 KB · Affichages: 5
Solution
sylvanu
Bonjour,
Par pitié, utilisez la balise pour le code ( </> à droite de la balise GIF ) c'est plus lisible.
Votre code ne peut pas marcher, par ex :
VB: 
Chemin = Sheets("Table").[B1] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table").[B2] ' et son nom
Feuille = Sheets("Table").[B3] ' et le nom de la feuille à récupérer
Chemin = Sheets("Table").[B4] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table").[B5] ' et son nom
Feuille = Sheets("Table").[B6]
Chemin vaut le contenu de B1, puis vous l'écrasez et lui donnez la valeur de B4, une variable ne peut pas avoir plusieurs valeurs.

Le plus simple est de dupliquer votre macro en changeant les variables ( B4 pour B1, B5 pour B2 ... )
Puis vous faites une macro...
Trier par date Trier par votes
S

sum01

XLDnaute Occasionnel
sylvanu à dit:
Bonjour Sum,
En Table A2 vous mettez le chemin du fichier, et dans vos formules vous remplacez le chemin par INDIRECT(Table!A2).

Fonction INDIRECT - Support Microsoft

Comment utiliser la fonction INDIRECT dans Excel pour modifier la référence à une cellule dans une formule sans modifier la formule elle-même.
support.microsoft.com support.microsoft.com
Cliquez pour agrandir...
Bonjour Sylvanu,

Merci pour votre réponse.
J'ai effectué la modification mais la formule renvoie #valeur!

Merci encore pour votre aide
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re,
Dans Table A2 j'ai mis :
VB: 
C:\Users\PC_PAPA\Desktop\XLD\[20230930_variable.xlsx]TableExemple2
Dans TableExemple B1 :
Code: 
=SOMME.SI(INDIRECT("'"&Table!$A$3&"'!$A$1:$B$20");A1;INDIRECT("'"&Table!$A$3&"'!$B$1:$B$20"))
et ça marche, mais bien respecter la syntaxe.
Par contre il faut que les deux fichiers soient ouverts. Peut être y a t-il une solution par formule...
Sinon on peut le faire en VBA.
 

Pièces jointes

  • 20230930_variablerecup (1).xlsx
    8.9 KB · Affichages: 2
Vote positif 0 Vote négatif
S

sum01

XLDnaute Occasionnel
sylvanu à dit:
Re,
Dans Table A2 j'ai mis :
VB: 
C:\Users\PC_PAPA\Desktop\XLD\[20230930_variable.xlsx]TableExemple2
Dans TableExemple B1 :
Code: 
=SOMME.SI(INDIRECT("'"&Table!$A$3&"'!$A$1:$B$20");A1;INDIRECT("'"&Table!$A$3&"'!$B$1:$B$20"))
et ça marche, mais bien respecter la syntaxe.
Par contre il faut que les deux fichiers soient ouverts. Peut être y a t-il une solution par formule...
Sinon on peut le faire en VBA.
Cliquez pour agrandir...
Bonjour Sylanu,

Merci pour votre réponse et cela fonctionne très bien. En effet, il faut être extrêment attentif à la synthaxe. Par contre, j'ai essayé de répliquer sur d'autres structures de formule sans succès. Il y a tout un logique derrière que je ne maîtrise pas assez mais à force d'essayer sur déjà cette solution, j'y arriverais
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
sum01 à dit:
Il y a tout un logique derrière
Cliquez pour agrandir...
Chaque caractère à son importance.:)

Mais si vous avez beaucoup de calculs à effectuer, il serait peut être bon de passer par une solution VBA.
En VBA on ouvre le fichier on copie toutes les données de la Feuil1 dans la Feuil1 du fichier de réception et on referme le fichier.
Ensuite il vous suffit de faire les calculs sur cette feuille 1 puisque c'est une image du fichier des données.
Ce qui simplifie vos formules et évite Indirect.
Qui plus ait, vous n'aurez pas à toucher au VBA puisqu'il sera indépendant de toutes vos formules.
 
Vote positif 0 Vote négatif
S

sum01

XLDnaute Occasionnel
Bonjour Sylvanu,

En effet, mais c'est la magie de ce logiciel que je trouve tellement fascinant. il n'y a une seule solution mais il faut être précis. En effet votre piste est intelligente et logique plutôt que de m'embêter avec des formules. Je me lance.
Merci encore pour votre aide qui m'a fait avancer à grands pas.
Bon dimanche
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re,
Le post #5 n'est pas une solution, donc ne le mettez pas en vert, cela induitait en erreur le futur lecteur.

En PJ un essai avec :
VB: 
Sub UpdateSheet()
    Dim Chemin$, Fichier$, Feuille$, FichierCourant$, Tablo
    Application.ScreenUpdating = False              ' on fige l'écran
    Chemin = Sheets("Table").[B1]                   ' On récupère le chemin du fichier à traiter
    Fichier = Sheets("Table").[B2]                  ' et son nom
    Feuille = Sheets("Table").[B3]                  ' et le nom de la feuille à récupérer
    FichierCourant = ThisWorkbook.Name              ' On mémorise le nom du fichier courant
    Sheets("TableExemple2").Cells.ClearContents     ' On efface la feuille de réception
    On Error GoTo Fin
    Workbooks.Open Chemin & Fichier                 ' On ouvre le fichier
    Windows(Fichier).Activate                       ' Fichier devient actif
    Sheets(Feuille).Select                          ' Ainsi que la feuille
    Tablo = [A1].CurrentRegion                      ' On récupère toutes les données de A1 jusqu'à colonne=vide et ligne=vide
    ActiveWorkbook.Close False                      ' On ferme le fichier sans enregistrer
    Workbooks(FichierCourant).Activate              ' On active le fichier courant et on colle les données.
    Sheets(Feuille).[A1].Resize(UBound(Tablo, 1), UBound(Tablo, 2)) = Tablo
    Exit Sub
Fin:
    MsgBox "Une erreur s'est produite." & Chr(10) & "Vérifier les différents noms, et leur syntaxe."
End Sub

En feuille Table vous renseignez B1,B2,B3 puis vous appuyez sur le bouton.
Toutes les données seront rapatriées en feuille TableExemple2.
Et donc en feuille TableExemple1 les formules n'ont plus à se référer à un fichier externe.
 

Pièces jointes

  • 20230930_variablerecup (V1).xlsm
    19.7 KB · Affichages: 2
Vote positif 0 Vote négatif
S

sum01

XLDnaute Occasionnel
sylvanu à dit:
Re,
Le post #5 n'est pas une solution, donc ne le mettez pas en vert, cela induitait en erreur le futur lecteur.

En PJ un essai avec :
VB: 
Sub UpdateSheet()
    Dim Chemin$, Fichier$, Feuille$, FichierCourant$, Tablo
    Application.ScreenUpdating = False              ' on fige l'écran
    Chemin = Sheets("Table").[B1]                   ' On récupère le chemin du fichier à traiter
    Fichier = Sheets("Table").[B2]                  ' et son nom
    Feuille = Sheets("Table").[B3]                  ' et le nom de la feuille à récupérer
    FichierCourant = ThisWorkbook.Name              ' On mémorise le nom du fichier courant
    Sheets("TableExemple2").Cells.ClearContents     ' On efface la feuille de réception
    On Error GoTo Fin
    Workbooks.Open Chemin & Fichier                 ' On ouvre le fichier
    Windows(Fichier).Activate                       ' Fichier devient actif
    Sheets(Feuille).Select                          ' Ainsi que la feuille
    Tablo = [A1].CurrentRegion                      ' On récupère toutes les données de A1 jusqu'à colonne=vide et ligne=vide
    ActiveWorkbook.Close False                      ' On ferme le fichier sans enregistrer
    Workbooks(FichierCourant).Activate              ' On active le fichier courant et on colle les données.
    Sheets(Feuille).[A1].Resize(UBound(Tablo, 1), UBound(Tablo, 2)) = Tablo
    Exit Sub
Fin:
    MsgBox "Une erreur s'est produite." & Chr(10) & "Vérifier les différents noms, et leur syntaxe."
End Sub

En feuille Table vous renseignez B1,B2,B3 puis vous appuyez sur le bouton.
Toutes les données seront rapatriées en feuille TableExemple2.
Et donc en feuille TableExemple1 les formules n'ont plus à se référer à un fichier externe.
Cliquez pour agrandir...
Bonjour Sylanu,

Merveilleux, c'est encore mieux, mille fois mieux !!!! Et cela simplifie grandement le travail de mise à jour et maintenance des fichiers. ! C'est parfait !
Mille fois merci pour votre aide pour optimiser la solution.
C'est noté pour la fermeture du post et ferai attention les prochaines fois
Bon dimanche à vous
 
Vote positif -1 Vote négatif
S

sum01

XLDnaute Occasionnel
sylvanu à dit:
Re,
Le post #5 n'est pas une solution, donc ne le mettez pas en vert, cela induitait en erreur le futur lecteur.

En PJ un essai avec :
VB: 
Sub UpdateSheet()
    Dim Chemin$, Fichier$, Feuille$, FichierCourant$, Tablo
    Application.ScreenUpdating = False              ' on fige l'écran
    Chemin = Sheets("Table").[B1]                   ' On récupère le chemin du fichier à traiter
    Fichier = Sheets("Table").[B2]                  ' et son nom
    Feuille = Sheets("Table").[B3]                  ' et le nom de la feuille à récupérer
    FichierCourant = ThisWorkbook.Name              ' On mémorise le nom du fichier courant
    Sheets("TableExemple2").Cells.ClearContents     ' On efface la feuille de réception
    On Error GoTo Fin
    Workbooks.Open Chemin & Fichier                 ' On ouvre le fichier
    Windows(Fichier).Activate                       ' Fichier devient actif
    Sheets(Feuille).Select                          ' Ainsi que la feuille
    Tablo = [A1].CurrentRegion                      ' On récupère toutes les données de A1 jusqu'à colonne=vide et ligne=vide
    ActiveWorkbook.Close False                      ' On ferme le fichier sans enregistrer
    Workbooks(FichierCourant).Activate              ' On active le fichier courant et on colle les données.
    Sheets(Feuille).[A1].Resize(UBound(Tablo, 1), UBound(Tablo, 2)) = Tablo
    Exit Sub
Fin:
    MsgBox "Une erreur s'est produite." & Chr(10) & "Vérifier les différents noms, et leur syntaxe."
End Sub

En feuille Table vous renseignez B1,B2,B3 puis vous appuyez sur le bouton.
Toutes les données seront rapatriées en feuille TableExemple2.
Et donc en feuille TableExemple1 les formules n'ont plus à se référer à un fichier externe.
Cliquez pour agrandir...
Bonjour Sylvnau,

J' ai ouvert mon ancien post et votre solution VBA car cette semaine j'ai voulu étendre les informations à récupérer. Ma démarche était initialement simple me disant que je vais répliquer les lignes de code tout en modifiant les variables. Ma pauvreté intellectuelle est grande ici car évidement cela ne fonctionne pas. Il y aurait une solution de créer une 2ème macro mais si je veux garder la même pour récupérer 2 fichiers sources se trouvant à des endroits différents [B1] et B4].

Encore une fois merci pour votre aide
Très bon week-end à vous.

Sub UpdateSheet()
Dim Chemin$, Fichier$, Feuille$, FichierCourant$, Tablo, Dim Chemin1$, Fichier1$, Feuille1$, FichierCourant1$, Tablo1,

Application.ScreenUpdating = False ' on fige l'écran

Chemin = Sheets("Table").[B1] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table").[B2] ' et son nom
Feuille = Sheets("Table").[B3] ' et le nom de la feuille à récupérer
Chemin = Sheets("Table").[B4] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table").[B5] ' et son nom
Feuille = Sheets("Table").[B6]

FichierCourant = ThisWorkbook.Name ' On mémorise le nom du fichier courant
FichierCourant1 = ThisWorkbook.Name
Sheets("TableExemple2").Cells.ClearContents ' On efface la feuille de réception
Sheets("TableExemple3").Cells.ClearContents

On Error GoTo Fin

Workbooks.Open Chemin & Fichier ' On ouvre le fichier
Workbooks.Open Chemin & Fichier

Windows(Fichier).Activate ' Fichier devient actif
Windows(Fichier1).Activate

Sheets(Feuille).Select ' Ainsi que la feuille
Sheets(Feuille1).Select

Tablo = [A1].CurrentRegion ' On récupère toutes les données de A1 jusqu'à colonne=vide ligne=vide
Tablo1 = [A1].CurrentRegion ' On récupère toutes les données de A1 jusqu'à colonne=vide et ligne=vide
ActiveWorkbook.Close False ' On ferme le fichier sans enregistrer

Workbooks(FichierCourant).Activate ' On active le fichier courant et on colle les données.
Workbooks(FichierCourant1).Activate

Sheets(Feuille).[A1].Resize(UBound(Tablo, 1), UBound(Tablo, 2)) = Tablo
Sheets(Feuille1).[A1].Resize(UBound(Tablo1, 1), UBound(Tablo1, 2)) = Tablo1
Exit Sub

Fin:
MsgBox "Une erreur s'est produite." & Chr(10) & "Vérifier les différents noms, et leur syntaxe."
End Sub
 
Vote positif 0 Vote négatif
sylvanu

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonjour,
Par pitié, utilisez la balise pour le code ( </> à droite de la balise GIF ) c'est plus lisible.
Votre code ne peut pas marcher, par ex :
VB: 
Chemin = Sheets("Table").[B1] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table").[B2] ' et son nom
Feuille = Sheets("Table").[B3] ' et le nom de la feuille à récupérer
Chemin = Sheets("Table").[B4] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table").[B5] ' et son nom
Feuille = Sheets("Table").[B6]
Chemin vaut le contenu de B1, puis vous l'écrasez et lui donnez la valeur de B4, une variable ne peut pas avoir plusieurs valeurs.

Le plus simple est de dupliquer votre macro en changeant les variables ( B4 pour B1, B5 pour B2 ... )
Puis vous faites une macro qui va exécuter UpdateSheet puis UpdateSheet2.
Code: 
Sub UpdateSheet()
    ' Macro initiale
End Sub
Sub UpdateSheet2()
    ' Macro modifiée
End Sub

Sub LanceUpdateSheet()
    Call UpdateSheet
    Call UpdateSheet2
End Sub
 
Vote positif 0 Vote négatif
Solution
S

sum01

XLDnaute Occasionnel
sylvanu à dit:
Bonjour,
Par pitié, utilisez la balise pour le code ( </> à droite de la balise GIF ) c'est plus lisible.
Votre code ne peut pas marcher, par ex :
VB: 
Chemin = Sheets("Table").[B1] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table").[B2] ' et son nom
Feuille = Sheets("Table").[B3] ' et le nom de la feuille à récupérer
Chemin = Sheets("Table").[B4] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table").[B5] ' et son nom
Feuille = Sheets("Table").[B6]
Chemin vaut le contenu de B1, puis vous l'écrasez et lui donnez la valeur de B4, une variable ne peut pas avoir plusieurs valeurs.

Le plus simple est de dupliquer votre macro en changeant les variables ( B4 pour B1, B5 pour B2 ... )
Puis vous faites une macro qui va exécuter UpdateSheet puis UpdateSheet2.
Code: 
Sub UpdateSheet()
    ' Macro initiale
End Sub
Sub UpdateSheet2()
    ' Macro modifiée
End Sub

Sub LanceUpdateSheet()
    Call UpdateSheet
    Call UpdateSheet2
End Sub
Cliquez pour agrandir...
Bonjour Sylanu,

Mille excuse en effet.
J'avais effectivement modifié initialement Table en Table1 dans B4, B5 et B6 comme ci-dessous.
Cependant, j'ai appliqué votre solution et ça marche du tonnerre de feu. Merci beaucoup pour cela ! Votre aide m'a été très précieuse.

Fichier = Sheets("Table").[B2] ' et son nom
Feuille = Sheets("Table").[B3] ' et le nom de la feuille à récupérer
Chemin = Sheets("Table1").[B4] ' On récupère le chemin du fichier à traiter
Fichier = Sheets("Table1").[B5] ' et son nom
Feuille = Sheets("Table1").[B6][/CODE]
 
Vote positif 0 Vote négatif

Discussions similaires

C
  • Résolu(e)
XL 2019 Recherchev avec même valeur
Réponses
5
Affichages
195
job75
job75
O
  • Question
XL 2016 Enlever une chaîne de caractères d'une cellule dans une formule SOMME.SI
Réponses
3
Affichages
135
JHA
J
O
  • Question
XL 2016 Enlever une chaîne de caractères d'une cellule dans une formule SOMME.SI
Réponses
1
Affichages
181
JHA
J
S
  • Résolu(e)
Microsoft 365 Copier automatiquement sur une ligne sur une autre feuille si la date est supérieur à une date définie
2
Réponses
17
Affichages
985
S.J
S
Salmander
  • Question
Autres Remplacer une zone de texte par un nom du gestionnaire de noms
Réponses
13
Affichages
714
Salmander
Salmander
C
  • Résolu(e)
XL 2021 Macro dans Macro
2
Réponses
26
Affichages
924
Constantin
C
oguruma
POWERQUERY :: Ranking par catégorie :: TopN variable d'une liste
Réponses
0
Affichages
202
oguruma
oguruma
T
  • Question
XL 2016 macro pour vider le contenu d'un tableau synthese ( avant d’actualiser par les nouvelles données )
Réponses
7
Affichages
292
TooFatBoy
TooFatBoy
P
  • Question
Microsoft 365 Tableau et condition inclusive comptabilité par mois
Réponses
1
Affichages
303
merinos-BernardEtang
merinos-BernardEtang
D
  • Résolu(e)
XL 2019 Nouveau Numéro
2 3 4
Réponses
57
Affichages
1 K
patricktoulon
patricktoulon

Membres actuellement en ligne

15 membres et 174 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
315 098
Messages
2 116 198
Membres
112 681
dernier inscrit
romain38