XL 2019 Remplacer liaison par une variable

[sum01]

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

 

[sylvanu]

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

 

[sum01]

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

Bonjour Sylvanu,

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

Merci encore pour votre aide

 

[sylvanu]

Re,
Dans Table A2 j'ai mis :

C:\Users\PC_PAPA\Desktop\XLD\[20230930_variable.xlsx]TableExemple2

Dans TableExemple B1 :

=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.

 

[sum01]

Re,
Dans Table A2 j'ai mis :

C:\Users\PC_PAPA\Desktop\XLD\[20230930_variable.xlsx]TableExemple2

Dans TableExemple B1 :

=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.

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

 

[sylvanu]

Il y a tout un logique derrière

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.

 

[sum01]

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

 

[sylvanu]

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 :

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.

 

[sum01]

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 :

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.

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

 

[sum01]

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 :

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.

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

 

[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 :

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.

Sub UpdateSheet()
    ' Macro initiale
End Sub
Sub UpdateSheet2()
    ' Macro modifiée
End Sub

Sub LanceUpdateSheet()
    Call UpdateSheet
    Call UpdateSheet2
End Sub

 

[sum01]

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 :

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.

Sub UpdateSheet()
    ' Macro initiale
End Sub
Sub UpdateSheet2()
    ' Macro modifiée
End Sub

Sub LanceUpdateSheet()
    Call UpdateSheet
    Call UpdateSheet2
End Sub

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]