Bonjour,

Je travaille sur un code afin de pouvoir mettre à jour les liens contenus dans l'ensemble des fichiers Excel de mon répertoire. J'ai réussi le code qui permet d'ouvrir les fichiers, de modifier certains liens vers d'autres fichiers sources et de sauvegarder le fichier. 

Sauf que voilà, j'ai des rechercheV dans certaines de mes formules et j'aimerai modifier le lien contu dans cette fonction.

Par exemple, j'ai  : 

=RechercheV($D$1;'\\Company\network\Controlling\Axe1\Budget\Budget 2018-2019\Draft\[Hypothèses 2019.xlsx]CA'!$C:$P;8;0)

'Et je souhaite le modifier en :

=RechercheV($D$1;'\\Company\network\Controlling\Axe1\Budget\Budget 2018-2019\Draft\Draft Valide\[Hypothèses 2020.xlsx]CA'!$C:$P;8;0)

'==> Année modifiée
'==> Chemin rallongé (\Draft\Draft Valide\)

Je ne viens évidemment pas bredouille, voilà le code que j'ai tenté  :

Private Sub remplace1()
''\\Company\network\Controlling\Axe1\Budget\Budget 2018-2019\Draft\[Hypothèses 2019.xlsx] <= Lien à changer

'MAJ DU LIEN VERS L'HYP 
Year_ = "2019-2020"
An_inf = left(Year_ , 4)
An_sup = right(Year_ , 4)

ancien_chemin = "2018-2019\Draft\[Hypothèses " & An_inf
nouv_chemin = Year_ & "\Draft\Draft Validé\[Hypothèses " & An_sup

Set plage_recherche = ActiveSheet.Columns("F:AG")
            plage_recherche.Select
                For Each Cell In plage_recherche.SpecialCells(xlCellTypeFormulas)
                    cherche2 = "txt"
                    Set cherche2 = plage_recherche.Cells.SpecialCells(xlCellTypeFormulas).Find(what:=ancien_chemin, LookIn:=xlFormulas, LookAt:=xlPart)
                    If IsNumeric(cherche2) Then Cell.Replace what:=ancien_chemin, replacement:=nouv_chemin
                
               Next
               
End Sub

Je vois bien qu'il tourne et cherche les liens et je pense qu'il ne les trouve pas. Pourtant j'en ai une centaine par feuille.

Peut-être que ce n'est tout simplement pas faisable quand le lien est à l'intérieur d'une fonction? 

Merci d'avance pour vos réponses !

-
Edité par Coding Sunset 24 mai 2019 à 15:53:42

Salut,

Qu'est-ce que tu entends par "il cherche des liens" ?

Plusieurs trucs sur le code :

• utilise .value si tu veux accéder à la valeur d'une cellule ;
• réduis ta plage de recherche car là tu fais une boucle sur plus de 28 millions de cellules et ça va te prendre des années, Find se débrouille tout si tu lui donnes directement la Range (c'est ce que tu fais déjà quand tu l'utilises) ;
• pourquoi faire "Cell.Replace" alors que tu as cherché une cellule avec le Find au dessus et enregsitré dans "cherche2" ?
• est-ce que Replace essaie de remplacer dans les formules ou les valeurs ? Comme il n'y a aucun argument je pense que par défaut c'est dans les valeurs ;
• quand on utilise Find, il faut vérifier si une cellule a été trouvée en vérifiant si il y a bien quelque chose dans la valeur retournée, c'est à dite faire un truc genre "If Not cherche2 Is Nothing Then" ;
• tu peux retirer le "plage_recherche.Select", tu n'es pas obligé de sélectionner une plage pour travailler dessus (tu le fais d'ailleurs très bien ensuite) et il est déconseillé d'utiliser les Select, Activate, Selection, etc... car ils ont un coup en performance et dépendent de l'utilisateur.

Perso la manière la plus simple que j'ai trouvé pour modifier beaucoup de liens comme ça c'est d'ouvrir le fichier à modifier, l'ancien fichier source et le nouveau fichier source.

Puisque le fichier source est ouvert il n'y a que son nom qui apparaît dans les formules, il suffit donc de chercher le nom de l'ancien fichier source et le remplacer par le nouveau (je le fais à la main avec un Ctrl+H mais en VBA ça devrait marcher aussi), puisque le nouveau fichier source est ouvert les valeurs vont se mettre à jour automatique et quand tu le fermeras le chemin se mettra à jour aussi dans les formules.

Stormweaker a écrit:

Salut,

Qu'est-ce que tu entends par "il cherche des liens" ?

Plusieurs trucs sur le code :

• utilise .value si tu veux accéder à la valeur d'une cellule ;
• réduis ta plage de recherche car là tu fais une boucle sur plus de 28 millions de cellules et ça va te prendre des années, Find se débrouille tout si tu lui donnes directement la Range (c'est ce que tu fais déjà quand tu l'utilises) ;
• pourquoi faire "Cell.Replace" alors que tu as cherché une cellule avec le Find au dessus et enregsitré dans "cherche2" ?
• est-ce que Replace essaie de remplacer dans les formules ou les valeurs ? Comme il n'y a aucun argument je pense que par défaut c'est dans les valeurs ;
• quand on utilise Find, il faut vérifier si une cellule a été trouvée en vérifiant si il y a bien quelque chose dans la valeur retournée, c'est à dite faire un truc genre "If Not cherche2 Is Nothing Then" ;
• tu peux retirer le "plage_recherche.Select", tu n'es pas obligé de sélectionner une plage pour travailler dessus (tu le fais d'ailleurs très bien ensuite) et il est déconseillé d'utiliser les Select, Activate, Selection, etc... car ils ont un coup en performance et dépendent de l'utilisateur.

Perso la manière la plus simple que j'ai trouvé pour modifier beaucoup de liens comme ça c'est d'ouvrir le fichier à modifier, l'ancien fichier source et le nouveau fichier source.

Puisque le fichier source est ouvert il n'y a que son nom qui apparaît dans les formules, il suffit donc de chercher le nom de l'ancien fichier source et le remplacer par le nouveau (je le fais à la main avec un Ctrl+H mais en VBA ça devrait marcher aussi), puisque le nouveau fichier source est ouvert les valeurs vont se mettre à jour automatique et quand tu le fermeras le chemin se mettra à jour aussi dans les formules.

Bonjour Stormweaker,

En fait, le but de mon code c'est de trouver une chaine de caractères (qui se trouve être un lien vers un fichier). Si cette chaine est trouvée, elle est remplacée par une autre. D'où l'utilité de mon cherche2 qui est censé vérifier que la cellule contient (ou non) la chaine de caractère à changer.

J'avais effectivement pensé à faire Ctrl + H et modifier moi-même les liens en suivant ta méthiode sauf que je ne cherche pas à remplacer la valeur de cellule mais une chaine de caractère contenue dans les RechercheV et Ctrl + H ne semble pas fonctionner dans ce cas-là.

La valeur de mes cellules est #N/A pour le moment parce qu'elles font un rechercheV d'une date (par exemple juin-2019) dans un fichier où les dates s'arrêtent à Mars 2019.

J'essaye donc trouver un moyen, s'il existe, de modifier uniquement le lien contenu dans les rechercheV de cette feuille 

La méthode Find est beaucoup plus puissante que ça, elle va chercher dans toutes les cellules de la plage que tu donnes, ici plage_recherche et te renvoie la première cellule qui répond aux critères. Dans ton code Find renverra toujours la même cellule, qui sera totalement indépendante de la valeur de Cell.

Avec Ctrl+H c'est possible, il faut cliquer sur le bouton "Options>>" et changer l'option "Regarder dans Formules".

Le code VBA qui est associé au Ctrl+H (donné par l'enregistreur de macro) donne ça :

    Selection.Replace What:="a", Replacement:="b", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Il faut faire quelques modifs et ça marchera chez toi. Et en fait ça remplace bien par défaut dans les formules, j'ai fait une erreur dans mon post précédent.

Stormweaker a écrit:

La méthode Find est beaucoup plus puissante que ça, elle va chercher dans toutes les cellules de la plage que tu donnes, ici plage_recherche et te renvoie la première cellule qui répond aux critères. Dans ton code Find renverra toujours la même cellule, qui sera totalement indépendante de la valeur de Cell.

Avec Ctrl+H c'est possible, il faut cliquer sur le bouton "Options>>" et changer l'option "Regarder dans Formules".

Le code VBA qui est associé au Ctrl+H (donné par l'enregistreur de macro) donne ça :

    Selection.Replace What:="a", Replacement:="b", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Il faut faire quelques modifs et ça marchera chez toi. Et en fait ça remplace bien par défaut dans les formules, j'ai fait une erreur dans mon post précédent.

Hey Storm,

Merci beaucoup, après quelques ajustements, il fonctionne parfaitement !