Discussion :

[Froggy007]

J'ai à traduire en français l'ensemble d'une application excel écrite en Anglais.
Il y a plusieurs feuilles, dans lesquelles se trouvent de nombreuses formules.
Dans ces formules se trouvent des noms faisant référence à des cellules nommées (ou des champs nommés).

Je souhaite changer le nom d'une cellule nommée et le remplacer par un autre nom dans toutes les formules où il se trouve.

Je pensais :
1) identifier les cellules avec formules
2) chercher dans ces cellules lesquelles renferment le nom que je souhaite changer
3) le remplacer par le nouveau nom

Mais je ne vois pas comment faire cela en VB. Quelqu'un pourrait-il m'aider ?
Merci.

[Froggy007]

Je travaille sur un classeur où j'ai 2 types de nom de cellules :

1) ceux que j'appelerai "Globaux" qui sont définis pour le classeur au complet et apparaissent dans toutes les feuilles de calculs (lorsque l'on clique sur la flèche de la liste déroulante située en A1); et

2) ceux que j'appelerai "Locaux" qui sont définis sur une feuille uniquement (on les voit apparaître sur le menu déroulant A1 de la feuille en question, mais pas ailleurs dans le classeur).

Lorsque l'on obtient la liste des cellules nommées par le code suivant, TOUS les noms sont listés et ceux spécifiques à une feuille sont précédés du nom de la feuille suivi d'un point d'exclamation (exemple : Feuil1!NomCrééIci) alors qu'un nom global s'écrira "NomGlobal".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub ListerToutesLesCellulesNomméesAvecLeurAdresse()
 
    Set NewSheet = Worksheets.Add
 
    i = 1
    For Each nm In ActiveWorkbook.Names
        NewSheet.Cells(i, 1).Value = nm.Name
        NewSheet.Cells(i, 2).Value = "'" & nm.RefersTo
        i = i + 1
    Next
 
    NewSheet.Columns("A:B").AutoFit
 
End Sub

Problème :
Je cherche à renommer / supprimer des noms de façon automatisée.
Pour les noms "globaux", pas de problème, mais pour les noms "locaux" même en essayant d'activer la feuille où ils sont, ça ne marche pas. Le code que j'utilise actuellement est le suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
Sub RemplacerEtSupprimerNomsOEKN()
 
    Dim i, j As Integer
    Dim Cible, PathName, FileName, FileToWorkWith, FeuilleOuNomCellule As String
    Dim Classeur As Workbook
    Dim Feuille As Worksheet
 
' S'assurer que le nom à traiter est bien dans le workbook
' Voir pour les noms de cellules spécifiques à une feuille ???
 
  ' Chemin d'accès et Nom du fichier à traiter
    PathName = "C:\Data\"
    FileName = "DataTest.xls"
    FileToWorkWith = PathName & FileName
 
  ' S'assurer que le workbook à traiter est ouvert
  ' S'assurer que c'est lui qui est le workbook actif
 
    MsgBox IsFileOpen(FileToWorkWith)
    If IsFileOpen(FileToWorkWith) _
    Then
      ' Afficher un message indiquant que le fichier est ouvert et actif.
        MsgBox FileName & " : " & "déjà ouvert et actif !"
    Else
      ' Afficher un message indiquant que le fichier n'était pas ouvert
      ' qu'il va être ouvert et activé
        MsgBox FileName & " : " & "va être ouvert !"
        Workbooks.Open FileToWorkWith
    End If
 
  ' Comme le fichier est ouvert et en mémoire, plus besoin du pathway, donc
  ' on désigne le fichier sur lequel on va travailler, uniquement par son nom
  ' sans intégrer le pathway !!!
    Workbooks(FileName).Activate
    Set Classeur = ActiveWorkbook
 
    For i = 1 To UBound(TableauDesNoms)
 
      ' On met en correspondance l'ancien et le nouveau nom qui est destiné à le remplacer
        MsgBox TableauDesNoms(i)
 
      ' Vérifier si le nom comprend le nom d'une feuille du classeur
      ' en vérifiant la présence du caractère "!" dans la chaine de caractère
      ' du nom traité
 
        If InStr(1, TableauDesNoms(i), "!") > 0 Then
            FeuilleOuNomCellule = Left(TableauDesNoms(i), InStr(1, TableauDesNoms(i), "!") - 1)
            MsgBox FeuilleOuNomCellule
            Workbooks(FileName).Worksheets(FeuilleOuNomCellule).Activate
            Cible = Classeur.Names(TableauDesNoms(i)).RefersTo
        Else
            Cible = Classeur.Names(TableauDesNoms(i)).RefersTo
        End If
 
        If TableauDesNouveauxNoms(i) <> "X" Then
          ' Pour chacune des feuilles du workbook actif, remplace l'ancien nom par le nouveau
            For j = 2 To Sheets.Count
                Sheets(j).Cells.Replace What:=TableauDesNoms(i), Replacement:=TableauDesNouveauxNoms(i), LookAt:=xlPart
            Next j
          ' Supprime ensuite l'ancien nom
            Classeur.Names(TableauDesNoms(i)).Delete
          ' et renomme la plage initiale en lui affectant la même adresse
            Classeur.Names.Add Name:=TableauDesNouveauxNoms(i), RefersTo:=Cible
          Else
          ' Supprime le nom inutilisé du workbook
            Classeur.Names(TableauDesNoms(i)).Delete
        End If
 
    Next i
 
End Sub

Ça plante à la ligne :
Cible = Classeur.Names(TableauDesNoms(i)).RefersTo

Pouvez-vous m'aider ?
Merci

[Maxence HUBICHE]

Bonjour,

Déjà, à la place de

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
    Dim i, j As Integer
    Dim Cible, PathName, FileName, FileToWorkWith, FeuilleOuNomCellule As String

Il serait préférable de faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
    Dim i As Integer, j As Integer
    Dim Cible As String, PathName As String, FileName As String, FileToWorkWith As String, FeuilleOuNomCellule As String

Afin de t'éviter d'avoir du variant partout (i,Cible, PathName, FileName et FileToWorkWith sont en variant avec ta définition)

Ensuite, nulle part je n'ai vu la définition de TableauDesNoms ()
Ce qui n'aide pas !

Enfin, as-tu tenté un débogage et un contrôle de ce que renvoient :
TableauDesNoms(i)
et
Classeur.Names(TableauDesNoms(i)).RefersTo
(dans la fenêtre des espions, par exemple)
???

[ouskel'n'or]

Hello Froggy,
J'ai fusionné les deux discussions afin que tu n'aies pas à tout ré-expliquer

Ensuite, nulle part je n'ai vu la définition de TableauDesNoms

Question que je t'avais déjà posée le 18/09 et à laquelle tu avais répondu le même jour

Les tableaux sont déclarés dans la partie générale selon :
Dim TableauDesNoms(), TableauDesNouveauxNoms() As Variant

Pour répondre à ta question, les noms de cellules ou de plages de cellules sont des propriétés de l'objet Workbook.
Jète un oeil dans l'aide en ligne à "Names, propriété", tout ça est bien expliqué.
Mais tu peux repérer ou utiliser les noms de la feuille :
- Pour placer un nom dans une feuille précise

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveWorkbook.Names.Add Name:="UnNom", RefersTo:="feuil1!A1:B1"

- Pour connaître le nom de la feuille :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Adres = ActiveWorkbook.Names("UnNom").RefersTo

qui te donne le range avec le nom de la feuille dans laquelle se trouve le nom => "feuil1!A1:B1"

- Enfin, pour avoir le nom de la feuille :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

LaFeuille = Split(Adres,"!")(0)

Ceci dit, pour l'emploi que tu en fais, tu n'as en principe pas besoin de connaître le nom de la feuille si tu n'utilises pas de "Select" "Selection" ou "Activate", autant de propriétés dont tu n'as pas besoin.

Bonne journée

[Froggy007]

Merci des messages précédents.
Mais bon, je chercher encore... grrrrrr

En reprenant la logique des programmes précédents, est-ce que j'ai le droit d'écrire les instructions suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
If InStr(1, TableauDesNoms(i), "!") > 0 Then
   FeuilleOuNomCellule = Left(TableauDesNoms(i), InStr(1, TableauDesNoms(i), "!") - 1)
   AncienNom = Left(InStr(1, TableauDesNoms(i), "!") + 1, Len(TableauDesNoms(i)))
   NouveauNom = Left(InStr(1, TableauDesNouveauxNoms(i), "!") + 1, Len(TableauDesNouveauxNoms(i)))
   MsgBox FeuilleOuNomCellule & " // " & AncienNom & " // " & NouveauNom
   Cible = Workbooks(FileName).Sheets(FeuilleOuNomCellule).Names(AncienNom).RefersTo
   Sheets(FeuilleOuNomCellule).Cells.Replace What:=AncienNom, Replacement:=NouveauNom, LookAt:=xlPart
   Sheets(FeuilleOuNomCellule).Names(AncienNom).Delete
   Sheets(FeuilleOuNomCellule).Names.Add Name:=NouveauNom, RefersTo:=Cible
Else
   Cible = Classeur.Names(TableauDesNoms(i)).RefersTo
' Pour chacune des feuilles du workbook actif, remplace l'ancien nom par le nouveau
   For j = 1 To Sheets.Count
      Sheets(j).Cells.Replace What:=TableauDesNoms(i),   replacement:=TableauDesNouveauxNoms(i), LookAt:=xlPart
   Next j
' Supprime ensuite l'ancien nom
   Classeur.Names(TableauDesNoms(i)).Delete
' et renomme la plage initiale en lui affectant la même adresse
   Classeur.Names.Add Name:=TableauDesNouveauxNoms(i), RefersTo:=Cible
End If

Sachant que Classeur est défini au début du programme par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Dim Classeur As Workbook

Puis par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Workbooks(FileName).Activate
Set Classeur = ActiveWorkbook

Parce que dans l'aide sur Names, il est indiqué également que les propriétés valent au niveau worksheet aussi (extrait de l'aide) :

Pour un objet Worksheet, cette propriété renvoie une collection Names qui représente tous les noms spécifiques aux feuilles de calcul (noms définis avec le préfixe "WorksheetName!").

Ça devient vraiment critique ce souci.
Alors un grand merci d'avance pour l'aide.

[Froggy007]

Bon enfin !!!

Alors voilà, la procédure suivante fonctionne parfaitement (j'ai retiré les qqs bugs qui trainaient encore dans le dernier code placé en ligne) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
Sub RemplacerEtSupprimerNomsOEKN()
 
    Dim i, j, X As Integer
    Dim Cible, PathName, FileName, FileToWorkWith, FeuilleOuNomCellule As String
    Dim AncienNom, NouveauNom As String
    Dim Classeur As Workbook
    Dim Feuille As Worksheet
 
' S'assurer que le nom à traiter est bien dans le workbook
' Voir pour les noms de cellules spécifiques à une feuille ???
 
  ' Chemin d'accès et Nom du fichier à traiter
    PathName = "C:\Data\"
    FileName = "FichierATraiter.xls"
    FileToWorkWith = PathName & FileName
 
  ' S'assurer que le workbook à traiter est ouvert
  ' S'assurer que c'est lui qui est le workbook actif
 
    MsgBox IsFileOpen(FileToWorkWith)
    If IsFileOpen(FileToWorkWith) _
    Then
      ' Afficher un message indiquant que le fichier est ouvert et actif.
        MsgBox FileName & " : " & "déjà ouvert et actif !"
    Else
      ' Afficher un message indiquant que le fichier n'était pas ouvert
      ' qu'il va être ouvert et activé
        MsgBox FileName & " : " & "va être ouvert !"
        Workbooks.Open FileToWorkWith
    End If
 
  ' Comme le fichier est ouvert et en mémoire, plus besoin du pathway, donc
  ' on désigne le fichier sur lequel on va travailler, uniquement par son nom
  ' sans intégrer le pathway !!!
    Workbooks(FileName).Activate
    Set Classeur = ActiveWorkbook
 
    For i = 1 To UBound(TableauDesNoms)
 
      ' On met en correspondance l'ancien et le nouveau nom qui est destiné à le remplacer
        MsgBox TableauDesNoms(i)
 
      ' Vérifier si le nom comprend le nom d'une feuille du classeur
      ' en vérifiant la présence du caractère "!" dans la chaine de caractère
      ' du nom traité
      '
        If InStr(1, TableauDesNoms(i), "!") > 0 Then
            X = InStr(1, TableauDesNoms(i), "!")
            FeuilleOuNomCellule = Left(TableauDesNoms(i), X - 1)
            AncienNom = Right(TableauDesNoms(i), Len(TableauDesNoms(i)) - X)
            NouveauNom = Right(TableauDesNouveauxNoms(i), Len(TableauDesNouveauxNoms(i)) - X)
            MsgBox FeuilleOuNomCellule & " // " & AncienNom & " // " & NouveauNom
            Cible = Sheets(FeuilleOuNomCellule).Names(AncienNom).RefersTo
            Sheets(FeuilleOuNomCellule).Cells.Replace What:=AncienNom, Replacement:=NouveauNom, LookAt:=xlPart
            Sheets(FeuilleOuNomCellule).Names(AncienNom).Delete
            Sheets(FeuilleOuNomCellule).Names.Add Name:=NouveauNom, RefersTo:=Cible
        Else
            Cible = Classeur.Names(TableauDesNoms(i)).RefersTo
          ' Pour chacune des feuilles du workbook actif, remplace l'ancien nom par le nouveau
            For j = 1 To Sheets.Count
                Sheets(j).Cells.Replace What:=TableauDesNoms(i), Replacement:=TableauDesNouveauxNoms(i), LookAt:=xlPart
            Next j
          ' Supprime ensuite l'ancien nom
            Classeur.Names(TableauDesNoms(i)).Delete
          ' et renomme la plage initiale en lui affectant la même adresse
            Classeur.Names.Add Name:=TableauDesNouveauxNoms(i), RefersTo:=Cible
        End If
 
    Next i
 
End Sub

Un grand merci à vous tous et en particulier à OUSKEL'NOR