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

Salmander
  • Question
Autres Remplacer une zone de texte par un nom du gestionnaire de noms
Réponses
9
Affichages
227
Staple1600
Staple1600
C
  • Résolu(e)
XL 2021 Macro dans Macro
2
Réponses
26
Affichages
378
Constantin
C
P
  • Question
Microsoft 365 Tableau et condition inclusive comptabilité par mois
Réponses
1
Affichages
139
merinos
merinos
J
  • Question
Autres [Google Sheets] Recherchev, index, equiv sur liste déroulante - A l'aide !
Réponses
2
Affichages
197
Jaykerz
J
P
  • Question
XL 2021 Remplacer des formules matricielle par code VBA
Réponses
12
Affichages
229
TooFatBoy
TooFatBoy
I
  • Question
XL 2019 Suite à une formule, enlever le doublon dans le résultat avec fonction SI
Réponses
2
Affichages
93
IderMTC
I
A
  • Résolu(e)
Microsoft 365 Référence automatique de tableaux structurés
Réponses
12
Affichages
622
AlexiaC
A
R
  • Question
Microsoft 365 Aide formule complexe
Réponses
3
Affichages
233
sylvanu
sylvanu
M
  • Résolu(e)
Power Query Convertir un fichier PdF en Excel et restitution d'un état de synthèse
Réponses
10
Affichages
458
Merlin258413
M
G
  • Résolu(e)
XL 2019 Aide formule SI
Réponses
2
Affichages
233
guillef
G

Membres actuellement en ligne

10 membres et 161 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 209
Messages
2 086 266
Membres
103 168
dernier inscrit
isidore33