Discussion :

[Pierre Fauconnier]

Laurent,

Par rapport à ton code d'hier qui montre l'appel à un msgbox => les fonctions d'un module de ce type ne peuvent afficher un msgbox à l'utilisateur. Ce n'est tout simplement pas leur rôle. Soit on lève une erreur, soit la fonction renvoie une valeur qui sera traitée par le code appelant et qui lui, décidera d'afficher ou non un message, mais les fonctions de ton module ne peuvent pas interagir avec l'utilisateur, même optionnellement. Cela enfreint les règles de programmation qui veulent que des "couches" du programme soient dédiées à des actions précises:

• La couche de présentation qui gère les interactions avec l'utilisateur
• La couche Métier qui gère les problématiques métier
• La couche d'accès aux données qui gèrent les interaction avec les systèmes de stockage de données, dont les tables Excel.

Seule la couche de présentation peut interagir avec l'utilisateur. Les couches métier et d'accès aux données ne peuvent JAMAIS le faire. Dans certains développements légers, on accepte que la DAL (couche d'accès aux données) interagisse directement avec la PL, mais jamais avec l'utilisateur.

[Pierre Fauconnier]

Purée, ça m'avait échappé...

Je viens de voir des fonctions du module qui manipulent un combobox. Normalement, le module de gestion TS ne peut, à nouveau, pas communiquer avec l'utilisateur => on ne peut pas avoir une gestion des Combobox dans le module.

Les fonctions doivent recevoir les infos du combo via le code client, sinon, on enfreint les règles de bonne pratique et de séparation des responsabilités. Le module TS fait partie de la couche d'accès aux données et ne peut donc jamais communiquer avec l'utilisateur.

[laurent_ott]

Finalement il n'est pas nécessaire de stocker l'attribut et le nom complet du classeur d'origine.
Il suffit de restaurer l'attribut dès l'ouverture du classeur faite, et le nom complet s'obtient par "Workbook.FullName".

De même pour retrouver le nom de la feuille d'un tableau structuré il suffit de boucler sur les feuilles du classeur et de tester le nom des tableaux (Worksheets.ListObjects.Name).
Ce que fait la fonction "TS_RangeDansClasseur".

Dans l'exemple ci-dessous j'ai repris l'énoncé du message #58 mais sans désactiver les macros du classeur destination car il en contient une pour la mise en forme que je lance par "Application.Run".

Code VBA :

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
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
On Error GoTo Gest_Err
Err.Clear
 
Dim Wk_S As Workbook
Set Wk_S = OuvrirClasseur("C:\Users\ott_l\Downloads\Classeur_Elèves.xlsx", False)
 
Dim Wk_D As Workbook
Set Wk_D = OuvrirClasseur(Fichier:="C:\Users\ott_l\Downloads\Test_TS.xlsm", Visible:=False, DésactiveMacros:=False)
 
Dim TS As Range: Set TS = TS_RangeDansClasseur(Wk_S, "TS_Eleves")
Dim TD As Range: Set TD = TS_RangeDansClasseur(Wk_D, "TS_Eleves")
 
Call TS_Filtres_Poser(TS, "Note", ">10")  ' Sélectionne les notes supérieures à 10.
Call TS_SupprimerLignesMasquées(TS)       ' Supprime les lignes non sélectionnées.
 
Call TS_CopierUnTableau(TS, TD, TS_RemplacerDonnées, TS_Valeurs) ' Remplace le tableau destination.
 
Wk_D.Activate                                   ' Active le tableau destination.
Application.Run "Test_TS.xlsm!'MiseEnForme'"    ' Lance sa macro de mise en forme.
ThisWorkbook.Activate                           ' Revient sur la console
 
Call FermerClasseur(Wk_S, False) ' Ferme la source sans l'enregister.
Call FermerClasseur(Wk_D, True)  ' Ferme la destination en l'enregistrant.
 
MsgBox "fin"
 
Gest_Err:
 
If Err.Number <> 0 Then
    MsgBox Err.Number & " : " & Err.Description, vbExclamation
    Call FermerClasseur(Wk_S, False)
    Call FermerClasseur(Wk_D, True)
End If
Err.Clear
 
End Sub
'------------------------------------------------------------------------------------------------

Les fonctions proposées, que vous adapterez si besoin:

Code VBA :

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
'------------------------------------------------------------------------------------------------------
Public Function OuvrirClasseur(Fichier As String, _
                               Visible As Boolean, _
                               Optional MotDePasseOuverture As String = "", _
                               Optional MotDePasseEcriture As String = "", _
                               Optional MAJ_Liens As Boolean = False, _
                               Optional DésactiveMacros As Boolean = True) As Workbook
'------------------------------------------------------------------------------------------------------
' Ouvre un classeur Excel en le mettant invisible si "Visible" = False pour que l'utilisateur ne soit pas perturbé par les
' manipulations qui y sont faites. Un fichier en lecture seule peut être modifié car la fonction fait sauter
' cet attribut temporairement puis le remet à la fermeture par la fonction "FermerClasseur".
'------------------------------------------------------------------------------------------------------
' Fichier : le classeur Excel qu'il faut ouvrir (chemin complet + nom avec l'extension).
' Visible : False pour masquer le classeur.
' MotDePasseOuverture : éventuellement le mot de passe pour ouvrir le fichier (vide si non nécessaire).
' MotDePasseEcriture : éventuellement le mot de passe pour modifier le fichier (vide si non nécessaire).
' MAJ_Liens : mettre Vrai s'il faut faire une mise à jour des liens à l'ouverture du classeur.
' DésactiveMacros : mettre Vrai pour désactiver les macros et les événements y compris Workbook_Open.
'------------------------------------------------------------------------------------------------------
' La fonction renvoie l'objet Workbook du classeur si tout s'est bien passé.
'------------------------------------------------------------------------------------------------------
' Exemple d'utilisation pour remplacer les données du tableau "TS_Eleves" contenues dans le classeur
' "C:\Users\ott_l\Downloads\Test_TS.xlsm" sur la feuille "Feuil1" par les données du tableau "TS_Eleves"
' du classeur "C:\Users\ott_l\Downloads\Classeur_Elèves.xlsx" en feuille "Feuil1":
'
'On Error GoTo Gest_Err
'Err.Clear
'
'Dim Wk_S As Workbook
'Set Wk_S = OuvrirClasseur("C:\Users\ott_l\Downloads\Classeur_Elèves.xlsx", False)
'
'Dim Wk_D As Workbook
'Set Wk_D = OuvrirClasseur("C:\Users\ott_l\Downloads\Test_TS.xlsm", False)
'
'Dim TS As Range: Set TS = Wk_S.Sheets("Feuil1").Range("TS_Eleves")
'Dim TD As Range: Set TD = Wk_D.Sheets("Feuil1").Range("TS_Eleves")
'
'Call TS_CopierUnTableau(TS, TD, TS_RemplacerDonnées, TS_Valeurs)
'Call TS_FormatColonne(TD, "Note", "0.0", True)
'
'Call FermerClasseur(Wk_S, False)
'Call FermerClasseur(Wk_D, True)
'
'MsgBox "fin"
'
'Gest_Err:
'If Err.Number <> 0 Then
'    MsgBox Err.Number & " : " & Err.Description, vbExclamation
'    Call FermerClasseur(Wk_S, False)
'    Call FermerClasseur(Wk_D, True)
'End If
'Err.Clear
'------------------------------------------------------------------------------------------------------
Dim Wk As Workbook
Dim Filenum As Long
Dim ObjFile As Object
Dim Anc_ScreenUpdating As Boolean
Dim Attributs As Long
Dim Anc_Wk As Workbook
 
' Gestion des erreurs:
On Error GoTo Gest_Err
Err.Clear
 
' Bloque la mise à jour de l'écran:
Anc_ScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
 
' Mémorise le classeur actif:
Set Anc_Wk = ActiveWorkbook
 
' Une erreur est déclenchée si le fichier source n'est pas trouvé:
Set ObjFile = CreateObject("Scripting.FileSystemObject").GetFile(Fichier)
' Supprime l'attribut lecture seule:
Attributs = ObjFile.Attributes
ObjFile.Attributes = 0
' Une erreur est déclenchée s'il est déjà ouvert:
Filenum = FreeFile()
Open Fichier For Binary Lock Read Write As #Filenum
Close Filenum
 
' Désactive les macros pour ouvrir le fichier sans lancer "Workbook_Open":
If UCase(Right(Fichier, 5)) <> ".XLSX" And DésactiveMacros = True Then
    Dim secAutomation As MsoAutomationSecurity
    secAutomation = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    ' Ouvre le fichier (sans mettre à jour les liens si MAJ_Liens=False):
    Set Wk = Workbooks.Open(Fichier, MAJ_Liens, False, , MotDePasseOuverture, MotDePasseEcriture, True)
    ' Réactive les macros:
    Application.AutomationSecurity = secAutomation
Else
    ' Ouvre le fichier (sans mettre à jour les liens si MAJ_Liens=False):
    Set Wk = Workbooks.Open(Fichier, MAJ_Liens, False, , MotDePasseOuverture, MotDePasseEcriture, True)
End If
 
' Masque le classeur que l'on vient d'ouvrir:
Windows(Wk.Name).Visible = Visible
 
' Restaure l'attribut d'origine:
ObjFile.Attributes = Attributs
 
' Restaure le classeur appelant:
Anc_Wk.Activate
 
' Renvoie le classeur:
Set OuvrirClasseur = Wk
 
' Gestion des erreurs:
Gest_Err:
 
Application.ScreenUpdating = Anc_ScreenUpdating
 
If Err.Number <> 0 Then
    If Not ObjFile Is Nothing Then ObjFile.Attributes = Attributs
    Err.Raise Err.Number
End If
 
End Function
 
'------------------------------------------------------------------------------------------------------
Public Function FermerClasseur(Classeur As Workbook, Enregistrer As Boolean) As Boolean
'------------------------------------------------------------------------------------------------------
' Ferme un classeur (normalement préalablement ouvert avec OuvrirClasseur) et l'enregistre ou non.
'------------------------------------------------------------------------------------------------------
' Classeur : l'objet Workbook à fermer.
' Enregistrer : Vrai s'il faut enregistrer le classeur.
'------------------------------------------------------------------------------------------------------
' Renvoie : VRAI si tout s'est bien passé.
'------------------------------------------------------------------------------------------------------
Dim ObjFile As Object
Dim Anc_ScreenUpdating As Boolean
Dim Attributs As Long
 
' Gestion des erreurs:
On Error GoTo Gest_Err
Err.Clear
 
' Bloque la mise à jour de l'écran:
Anc_ScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
 
' Si le classeur est toujours actif:
If TypeName(Classeur) = "Workbook" Then
 
    ' S'il faut l'enregistrer:
    If Enregistrer = True Then
        ' Une erreur est déclenchée si le fichier source n'est pas trouvé:
        Set ObjFile = CreateObject("Scripting.FileSystemObject").GetFile(Classeur.FullName)
        ' Mémorise l'attribut du fichier puis supprime la lecture seule:
        Attributs = ObjFile.Attributes
        ObjFile.Attributes = 0
        Windows(Classeur.Name).Visible = True
        Classeur.Activate
        Application.WindowState = xlMinimized
        Classeur.Save
        ' Restaure l'attribut:
        ObjFile.Attributes = Attributs
    End If
 
    ' Ferme le fichier:
    Classeur.Saved = True
    Classeur.Close
 
    ' Renvoie Vrai:
    FermerClasseur = True
 
End If
 
' Gestion des erreurs:
Gest_Err:
 
Application.ScreenUpdating = Anc_ScreenUpdating
If Err.Number <> 0 Then Err.Raise Err.Number
 
End Function
 
'------------------------------------------------------------------------------------------------
Public Function TS_RangeDansClasseur(Classeur As Workbook, TS_Nom As String) As Range
'------------------------------------------------------------------------------------------------
Dim Ws, Ref
 
For Each Ws In Classeur.Worksheets
    For Each Ref In Ws.ListObjects
        If Ref.Name = TS_Nom Then
        Set TS_RangeDansClasseur = Classeur.Sheets(Ws.Name).Range(TS_Nom): Exit Function
        End If
    Next Ref
Next Ws
Err.Raise vbObjectError, "TS_RangeDansClasseur", "Le tableau [" & TS_Nom & "] n'est pas trouvé dans le classeur " & Classeur.FullName & "."
End Function
'------------------------------------------------------------------------------------------------
'------------------------------------------------------------------------------------------------

Je mettrai à jour la documentation prochainement.

[Pierre Fauconnier]

Salut Laurent,

La feuille d'un tableau structuré s'obtient par la propriété Parent du ListObject. On peut aussi remonter au classeur par le parent du parent.

range("t_Tâches").ListObject.Parent.name renvoie le nom de la feuille qui supporte le tableau.

range("t_Tâches").ListObject.Parent.parent.name renvoie le nom du classeur contenant le tableau.


Et bien sûr, sans le ".Name", on pointe vers la feuille (Parent) ou vers le classeur (Parent.Parent).

[laurent_ott]

Salut Laurent,
La feuille d'un tableau structuré s'obtient par la propriété Parent du ListObject. On peut aussi remonter au classeur par le parent du parent.
range("t_Tâches").ListObject.Parent.name renvoie le nom de la feuille qui supporte le tableau.
range("t_Tâches").ListObject.Parent.parent.name renvoie le nom du classeur contenant le tableau.
Et bien sûr, sans le ".Name", on pointe vers la feuille (Parent) ou vers le classeur (Parent.Parent).

Bonjour,
L'approche est intéressante mais ne correspond pas à mon besoin (ou je n'ai pas tout compris):
- la classeur contenant le tableau doit être activé, ce qui nécessite donc ensuite de restaurer le classeur appelant, ce que je ne souhaite pas.
- une erreur est déclenchée quand le classeur est non visible.

Le code que j'ai utilisé :

Code VBA :

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

1
2
3
4
5
6
7
8
9
Public Function TS_RangeDansClasseur(Classeur As Workbook, TS_Nom As String) As Range
Dim Anc_wk As Workbook
Dim Feuille As String
Set Anc_wk = ActiveWorkbook
Classeur.Activate
Feuille = Range(TS_Nom).ListObject.Parent.Name
Set TS_RangeDansClasseur = Classeur.Sheets(Feuille).Range(TS_Nom)
Anc_wk.Activate
End Function

Cordialement.

[deedolith]

Code :

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

Feuille = Range(TS_Nom).ListObject.Parent.Name

L'erreur est simple (et classique): Référence implicite.

Ne serait-ce pas mieux de donner comme argument à la fonction un tableau structuré (ListObject) plutôt qu'un nom ?
voir même se débarrasser de l'argument Workbook, ce qui au final resume la fonction a quelques lignes:

Code :

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

1
2
3
4
5
Public Function TS_RangeDansClasseur(TS As Excel.ListObject) As Range
    Debug.Assert Not TS Is Nothing
 
    Set TS_RangeDansClasseur = TS.Range
End Function

[Pierre Fauconnier]

Salut Laurent

Je n'aime vraiment pas cette façon de programmer, donc j'ai du mal à m'y retrouver et à comprendre ce que tes fonctions sont censées réaliser.

Je n'avais pas fait attention que tu travaillais avec un classeur invisible, d'où la proposition de passer par les propriétés Parent.

Je ne comprends pas bien à quoi ça sert, dans un module qui gère des tableaux structurés, d'avoir des fonctions qui ne manipulent pas du tout des tableaux structurés, ceci dit.


De plus, les Exit For dans un For each, perso, je déteste (je déteste tous les Exit). VBA propose Do While qui me semble plus adaptée et qui évite le Exit.

[deedolith]

En complément à ma réponse précédente:

Si tu travaille avec des noms, et avec seul objet que le classeur, cela implique de rechercher que l'objet portant le nom donné en argument (donc parcourir toutes les feuilles, bof...).
Sinon, a minima un argument sur la feuille contenant le TS , son nom ou la feuille elle même sera utile.
Ca t'éviteras d'activer un classeur (qui fait coin coin quand il est invisible).

[Pierre Fauconnier]

Salut

[...]cela implique de rechercher que l'objet portant le nom donné en argument (donc parcourir toutes les feuilles, bof...)[...]

Pour moi, il n'y a pas d'autre méthode que le parcours des feuilles et des tables des feuilles si l'on veut passer uniquement le nom de la table et du classeur. Or, c'est logique de ne passer que ces arguments puisque le but, c'est justement de récupérer la table uniquement au départ de son nom.

Donc perso, je ne vois pas en quoi c'est bof, puisque c'est, à ma connaissance en tout cas, la seule façon de faire.

[deedolith]

@Pierre Fauconnier:
Quid du classeur contenant beaucoup de feuilles, ou beaucoup de tableau ou un mix des 2 ?

Ca reste des cas d'utilisation rare, coté fonctionnement, on peut faire plus optimal.

[Pierre Fauconnier]

[...] coté fonctionnement, on peut faire plus optimal.

Ben propose alors 🙄

Mais avec juste le nom de la table et le classeur, je ne vois pas mieux. Or, le but, c'est justement de récupérer la table ou sa plage de données. Du coup, on est bien obligé de parcourir le classeur et ses feuilles. On ne peut pas partir du principe que l'on connait le ListObject puisque le but de la fonction, c'est justement de le récupérer.

Cela dit, parcourir les feuilles d'un classeur et "tous" les tableaux des feuilles, ce n'est quand même jamais très lourd, surtout si on stoppe la boucle dès que l'on a trouvé ce que l'on cherche.