Voir le flux RSS

Philippe Tulliez

Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName

Noter ce billet
par , 25/01/2020 à 14h24 (116 Affichages)
Préambule
On n'est jamais à l'abri du fait qu'un utilisateur renomme la feuille du classeur qu'il utilise et donc lorsque l'on distribue une application écrite avec VBA pour Excel, il est hautement conseillé d'utiliser la propriété CodeName de la feuille et ce afin de ne pas voir l'application renvoyer une erreur 9 (L'indice n'appartient pas à la sélection)

Pour en savoir plus sur la propriété CodeName d’une feuille Excel

Si l'usage du CodeName de la feuille où se trouve le code VBA est simple, il n'en est pas de même si l'on souhaite utiliser le CodeName de la feuille d'un autre classeur

Voici une fonction nommée GetSheetByCodeName qui parcoure la collection des feuilles présentes dans le classeur définit dans le second argument de cette fonction

Code de la fonction
Cette fonction renvoie un objet Sheet ou Worksheet en fonction des arguments qui lui sont passés, soit le CodeName de la feuille et l'objet Workbook où se trouve cette feuille. Ce dernier argument est facultatif.
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
Function GetSheetByCodeName(CodeName As String, _
                            Optional oWorkbook As Workbook) As Object
  ' Renvoie un objet Sheet ou Worksheet si trouvé ou Nothing dans le cas contraire
  ' Philippe Tulliez www.magicoffice.be
  ' Arguments
  '  CodeName    (String)   - Nom du CodeName de la feuille à trouver
  '  [oWorkbook] (Workbook) - Objet Classeur (ThisWorkbook par défaut)
  Dim Index As Integer
  If oWorkbook Is Nothing Then Set oWorkbook = ActiveWorkbook
  Do
    Index = Index + 1
    With oWorkbook
      If StrComp(.Sheets(Index).CodeName, CodeName, vbTextCompare) = 0 Then
         Set GetSheetByCodeName = .Sheets(Index)
      End If
    End With
  Loop While Index < Worksheets.Count And Not GetSheetByCodeName Is Nothing
End Function
Code d'une procédure invoquant la fonction
La procédure ci-dessous utilise l'objet Worksheet du classeur nommé TestCodeName.xlsx se trouvant dans le sous-répertoire nommé Data en utilisant son CodeName

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
Sub T()
  ' Déclaration
  Const SubFolder As String = "\Data\"
  Const FileName As String = "TestCodeName.xlsx"
  Const shtCodeName As String = "shtHome"
  Dim Wkb As Workbook
  Dim sht As Worksheet
  Dim CurrentFolder As String
  Dim FullName As String
  Dim msg As String
  ' Affectation
  CurrentFolder = ThisWorkbook.Path
  FullName = CurrentFolder & SubFolder & FileName
  Set Wkb = GetWorkbook(FullName)
  Set sht = GetSheetByCodeName(shtCodeName, Wkb)
  ' Ici code de traitement sur la feuille trouvée ou si Nothing
  If Not sht Is Nothing Then
     msg = "CodeName de la feuille : " & sht.Name
   Else
     msg = "Pas trouvé " & shtCodeName & " dans le classeur " & Wkb.Name
  End If
  MsgBox msg
  Wkb.Close
  Set Wkb = Nothing: Set sht = Nothing
End Sub
Autre possibilité d'accéder au CodeName d'une feuille
Il est possible également d'accéder au CodeName de la feuille d'un autre classeur à l'aide de VBProject.VBComponents

Cependant l'utilisation de la collection VBComponents nous oblige à autoriser l'application à accéder au modèle objet voir plus bas dans ce billet
Pour ma part, j'évite d'utiliser ce qui n'est pas standard

Comment autoriser l'accès par VBA au projet
Pour ce faire il y a lieu de cocher l'option Accès approuvé au modèle d'objet du projet VBA que l'on accède entre autres en cliquant sur la commande Sécurité des macros du groupe Code de l'onglet [Développeur]
Miniatures attachées Images attachées  

Envoyer le billet « Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName » dans le blog Viadeo Envoyer le billet « Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName » dans le blog Twitter Envoyer le billet « Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName » dans le blog Google Envoyer le billet « Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName » dans le blog Facebook Envoyer le billet « Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName » dans le blog Digg Envoyer le billet « Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName » dans le blog Delicious Envoyer le billet « Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName » dans le blog MySpace Envoyer le billet « Excel - Vba - Renvoyer un objet feuille d'un autre classeur en fonction de son CodeName » dans le blog Yahoo

Mis à jour 26/01/2020 à 11h28 par Philippe Tulliez

Catégories
VBA Excel

Commentaires