Equivalent de INDIRECT pour un fichier fermé SANS MACRO

Bonjour,

Je vous sollicite pour un petit coup de pouce car malgré mes tentatives je ne trouve pas la solution...

Je souhaiterais faire évolue mon logiciel et pouvoir changer de nom du classeur ou créer une nouvelle feuille avec un nouveau classeur sans recopier et modifier toutes les formules car il y en a vraiment beaucoup.

Jusqu'à present je reprenais cellule par cellule pour modifier les formules ce qui me prends énormement de temps.

Par exemple avec cette formule :

Nom du classeur : [Caravelles.xls]CAR 3

Changement de nom du nouveau classeur en: [Grillons.xls]Grillons D1

1/ Comment pouvoir changer de nom sans à l'avenir devoir modifier toutes les formules manuellement?

=SI(OU(NB.SI(BSP1;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Régime'!$B$6)=1;NB.SI(BSP1;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Régime'!$B$7)=1;NB.SI(BSP1;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Régime'!$B$8)=1);'[Caravelles.xls]CAR 3'!F$11;'[Caravelles.xls]CAR 3'!F$11+'[Caravelles.xls]CAR 3'!F$24)+F45+F49

2/J'ai également essayer cette formule "INDIRECT" en insérant l'adresse complète du classeur dans la cellule "N2" mais cela me donne "Ref#"...car j'utilise la feuille avec des feuille fermés.

Ce qui donne ca avec le nom du classeur en "N2" mais ne fonctionne pas:

=SI(OU(NB.SI(BSP1;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Normal'!$B$6)=1;NB.SI(BSP1;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Normal'!$B$7)=1;NB.SI(BSP1;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Normal'!$B$8)=1);INDIRECT(N2&"!F$10");INDIRECT(N2&"!F$10")+INDIRECT(N2&"!F$16")+SI(OU(NB.SI(Base_SPoisson;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Normal'!$B$6)=1;NB.SI(Base_SPoisson;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Normal'!$B$7)=1;NB.SI(Base_SPoisson;'C:\Users\Alex\Desktop\SEMAINE 16\[Menu.xls]Menu Normal'!$B$8)=1);0;INDIRECT(N2&"!F$22"))+F47+F51)

3/ Peut-on utiliser une macro en remplacement du "indirect"? J'ai lu qu'il existait la fonction IndirectEx ou pull?

Merci d'avance pour votre aide

Bonjour et

jyconnaisrien a écrit :

3/ Peut-on utiliser une macro en remplacement du "indirect"? J'ai lu qu'il existait la fonction IndirectEx?

Oui, c'est la seule solution à ma connaissance.

Faute de fichier de ta part, voici un exemple ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub
    Range("B2").FormulaR1C1 = "='" & ThisWorkbook.Path & "\data\[" & Range("B1").Value & ".xlsx]MaFeuilleSource'!R5C5"
End Sub

Autre version plus générique

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("xxxx")) Is Nothing Then
     Dim OldValue As Variant
     Application.EnableEvents = False
     Application.Undo
     OldValue = Target.Value
     Application.Undo
     Application.EnableEvents = True
     If OldValue <> Target.Value Then
        For Each ws In Worksheets
           ws.Cells.Replace What:=OldValue, Replacement:=Target.Value, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next
     End If
  End If
End Sub

Sinon, pour le fait que la fonction indirect ne fonctionne pas, cela peut être dû au fait qu'il n'y a pas les ' dans la construction du lien.

@JoyeuxNoël ... indirect ne fonctionnera pas si le fichier source est fermé

@ jyconnaisrien ... exemple illustrant la macro ci-dessus

Steelson a écrit :

@JoyeuxNoël ... indirect ne fonctionnera pas si le fichier source est fermé

J'avais zappé ça. C'est dommage d'ailleurs.

Merci pour votre réponse Steelson

Je partage mes fichiers

1 fichiers effectifs semaine1 sur lequel il y les formules

1 dossier contenant des fichiers fermés

J'utilise la feuille CAR3 pour l'exemple mais je ferais de meme pour toutes les feuilles

Simplifie le fichier et sa source à la plus simple expression.

Voici les fichiers

Bonjour,

Indirect() vers classeur fermé sans macro ça n'existe pas.

Tant qu'à mettre une macro, autant modifier la source :

Sub changeLien()
    Dim ancien As String, nouveau As String
    ancien = "c:\excel\classeur1.xls"
    nouveau = "c:\excel\classeur2.xls"
    ActiveWorkbook.ChangeLink ancien, nouveau, xlExcelLinks
End Sub

et toutes tes formules sont mises à jour, sans utiliser indirect.

Tu peux mettre cette macro dans un classeur indépendant, ouvrir et activer celui à modifier avant de lancer la macro.

Ce fichier peut être un xlsx qui restera un xlsx, mais à jour.

eric

Merci Eriiic pour ta réponse

Mais je ne comprends pas ta macro... le soucis est que les formules de mon fichier doivent se mettre à jour si je change l'adresse. C'est pour cela que j'etais parti à construire mes formules avec "indirect".

Mais la condition suppémentaire est que j'utilise les données de fichiers fermés.

Je joins ma formule ma formule

En "N2" j'ai inscrit l'adresse du classeur externe

Fait Données / Modifier les liaisons.

Tu vois tous les classeurs liés, c'est là qu'il faut agir pour que toutes les formules se mettent à jour.

Manuellement ou par macro Teste...

eric

Bonjour Eriiic,

@ jyconnaisrien : Mets cette macro dans la feuille correspondante, réponse donnée ici https://forum.excel-pratique.com/viewtopic.php?p=865870#p865870 avec un exemple !

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("N2")) Is Nothing Then
     Dim OldValue As Variant
     Application.EnableEvents = False
     Application.Undo
     OldValue = Target.Value
     Application.Undo
     Application.EnableEvents = True
     If OldValue <> Target.Value Then
        For Each ws In Worksheets
           ws.Cells.Replace What:=OldValue, Replacement:=Target.Value, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next
     End If
  End If
End Sub

Ah mais j'insiste.

Modifier les liens dans la collection LinkSources mets tout à jour en 1 ligne, sans devoir boucler ni faire 10000 remplacements si c'est le nombre de formules..

eric

Bonjour,

Encore merci à vous deux de me donner un coup de pouce...

1/Eriiic, j'ai tenté ta méthode mais je ne vois pas les formules se mettre à jour??? je t'ai joins la deuxième capture d'écran

2/Steelson, J'ai testé ta macro, si je teste avec formules indirectes, elle ne fonctionne pas quand les classeurs sont fermés. Je t'ai joins la première capture d'écran

Je suis intéressé par la méthode d'Eriiiiic

En ce qui me concerne, il ne faut pas utiliser INDIRECT mais faire une formule normale avec le fichier. La macro changera dans la formule le nom. Regarde l'exemple simple fourni, il n'y a jamais INDIRECT. C'est une façon justement de le contourner, de ne pas l'utiliser.

Nouveau test, je ne vois toujours pas les formules se mettent à jour si je mets l'adresse en "N2"

Je joins la capture d'écran

Peut être un problème d'adresse?

ok je viens de comprendre!!!!

C'est bien l'adresse....je viens d'utiliser la méthode d'Eriiic est ca fonctionne!

Bonjour,

Ah ben quand même !

Et cerise sur le gâteau, tu peux garder tes classeur sans macros en gardant cette macro dans un autre classeur, et en traitant l'actif.

Alors, je n'ai pas bien fait d'être insistant ?

eric

Merci à vous deux pour votre aide...cela m’évite un travail colossal manuellement!!!