Discussion :

[Patinber]

Merci beaucoup pour cette solution pratique.

[domserge]

Bonjour,

Lors de la copie d'une colonne avec TS_CopierValeurColonne je constate le décalage d'une ligne vers le haut, écrasant ainsi le titre de la colonne.
Cette erreur intervient en RemplacerDonnées (je n'ai pas testé sur ajouter données)
Cette commande prend les données sans le titre de colonne mais copie à partir de la ligne de titre, écrasant ainsi le titre de colonne et décalant toutes les données d'une ligne vers le haut.
j'ai modifié en mettant 2 au lieu de 1

Code :

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

1
2
3
4
5
6
7
8
9
 
' Place les données dans la destination:
Select Case Méthode
    Case TS_AjouterDonnées:
        TS_Dest.Cells(TS_Dest.Rows.Count + 1, Colonne_Dest).Resize(UBound(Copie), 1) = Copie
    Case TS_RemplacerDonnées:
        TS_Dest.ListObject.ListColumns(Colonne_Dest).DataBodyRange = ""
        TS_Dest.Cells(2, Colonne_Dest).Resize(UBound(Copie), 1) = Copie
End Select

Amicalement

[deedolith]

Salut,

J'ai remarqué que tu appelles souvent Application.ScreenUpdating, Application.EnableEvents, Application.Cursor.
Encore désolé, mais ce n'est pas son job.
La gestion de ces paramètres doit être faite, s'il y a lieux, à un niveau supérieur (en d'autres termes: Le code appelant).

Evolution possibles:
- Copier une plage dans un tableau structuré.
- Ajouter une plage à un tableau structuré.
- Copier un tableau 2D dans un tableau structuré.
- Ajouter un tableau 2D dans un tableau structuré.
- Copier un tableau structuré.
- Ajouter 2 tableaux structurés.

[fouziel]

Bonjour Laurent_Ott,

J'ai eu besoin de travailler avec les tableaux structurés pour développer une application sous Excel me servant de proptoype (opérationnel quand même) à un rédéveloppement ensuite plus stable disons (Excel plante encore trop souvent). J'ai fais l'erreur de ne pas rechercher sur ce site ... et ai développé mes propres fonctions ...

Avant tout : quel beau boulot ! Mille bravos ! C'est propre et complet. Certes nous pouvons imaginer de très nombreuses améliorations et façons d'aborder notamment la gestion d'erreur (je pense aux remarques un peu acerbes de deedolith, qui n'a pas forcément tord, mais qui pêchent juste sur la forme à mon sens, son avis n'étant pas celui du maître suprême en matière de dev VBA malgré sa longue et riche expérience ...). Je pense aussi à une gestion de curseurs à la recordset sans utiliser de recordset ... etc ...

J'ai commencé par développer comme toi Laurent un module disposant de tout un tas de fonctions boite à outil me permettant de manipuler les tableaux structurés et leurs contenus. Et puis je me suis heurté à un besoin de plus en plus prégnant : utiliser l'intellisense de VBA pour identifier les colonnes existantes dans un tableau, et utiliser au maximum le compilateur vba pour détecter les erreurs sémantiques sur l'utilisation des noms de colonne à la compilation plutôt qu'à l'exécution.

Pour cela j'ai imaginé et développé ce qui suit et qui est orienté objet.

Par dessus ma boite à outil de manipulation de tables Excel, j'ai développé un code capable de générer une classe miroir d'une table Excel statique. Plusieurs axes de fonctions sont ainsi générés dans la classe :
- des fonctions avec préfixe F_ pour identifier un setter/getter de valeurs de colonne
- des fonctions avec préfixe FN_ pour retourner le nom d'une colonne existante dans la table.
- des fonctions avec préfixe S_ pour rechercher dans une colonne donnée.
- toutes les fonctions de ma boite à outils appelable à partir de ma classe. J'ai implémenté un principe de dérivation de ma classe boite à outil, avec les limites de vba ...

Exemple d'usage : balayage de toutes les lignes de ma table et affichage des données des cellules de chaque ligne.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
    'Déclarer ma classe sur ma table 
    Dim MaTable As New ClsTblTest
 
    'Balayer la table
    With MaTable
        Do Until .getCursor() Is Nothing
            Debug.Print .F_ID, .F_PRENOM, .F_NOM, .F_GRADE
            .CursorNext
        Loop
    End With

La classe ClsTblTest aura au préalable été générée de la manière suivante :
- créer une classe from scratch et ajouter ces directives de précompilation :

Code :

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

1
2
3
4
Option Explicit
 
'$DERIVED FROM ClsExcelTable
'$STUB FROM EXCELTABLE Thisworkbook.sheets("Tests"), "Test"

Je lance ensuite mon précompilateur qui génère ce code :

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
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
Option Explicit
 
'$DERIVED FROM ClsExcelTable
'$STUB FROM EXCELTABLE Thisworkbook.sheets("Tests"), "Test"
 
'$DERIVED CODE START - NE PAS MODIFIER -----------------------------------
 
Public LaMere As New ClsExcelTable
...
Function getCursorRowInTable() As Long
    getCursorRowInTable = LaMere.getCursorRowInTable
End Function
 
Function isInColumnRangeActiveCell(ColumnName As String) As Boolean
    isInColumnRangeActiveCell = LaMere.isInColumnRangeActiveCell(ColumnName)
End Function
 
Sub FiltrerColonne(NomColonne As String, C1 As Variant, Optional C2 As Variant)
    Call LaMere.FiltrerColonne(NomColonne, C1, C2)
End Sub
 
Function getSheet() As Worksheet
    getSheet = LaMere.getSheet
End Function
 
Function getColdataRangeFromSheetRow(ColName As String, SheetRow As Long) As Range
    Set getColdataRangeFromSheetRow = LaMere.getColdataRangeFromSheetRow(ColName, SheetRow)
End Function
...
'Stub getters et setters --------------------------------
 
Private Sub Class_Initialize()
    LaMere.InitFromSheetAndTableName ThisWorkbook.Sheets("Tests"), "Test"
End Sub
Public Property Get F_ID()
   F_ID = LaMere.getCellFromCursor("Id")
End Property
 
Public Property Let F_ID(v)
   LaMere.getCellFromCursor("Id") = v
End Property
Public Property Get FN_ID() As String
   FN_ID = "Id"
End Property
Public Property Get R_ID() As Range
   Set R_ID = LaMere.getColdataRange("Id")
End Property
Public Function S_ID(WhatToLookFor As String) As Range
   Set S_ID = LaMere.searchInColumn("Id", WhatToLookFor)
End Function
 
Public Property Get F_NOM()
   F_NOM = LaMere.getCellFromCursor("Nom")
End Property
 
Public Property Let F_NOM(v)
   LaMere.getCellFromCursor("Nom") = v
End Property
Public Property Get FN_NOM() As String
   FN_NOM = "Nom"
End Property
Public Property Get R_NOM() As Range
   Set R_NOM = LaMere.getColdataRange("Nom")
End Property
Public Function S_NOM(WhatToLookFor As String) As Range
   Set S_NOM = LaMere.searchInColumn("Nom", WhatToLookFor)
End Function
 
Public Property Get F_PRENOM()
   F_PRENOM = LaMere.getCellFromCursor("Prénom")
End Property
 
Public Property Let F_PRENOM(v)
   LaMere.getCellFromCursor("Prénom") = v
End Property
Public Property Get FN_PRENOM() As String
   FN_PRENOM = "Prénom"
End Property
Public Property Get R_PRENOM() As Range
   Set R_PRENOM = LaMere.getColdataRange("Prénom")
End Property
Public Function S_PRENOM(WhatToLookFor As String) As Range
   Set S_PRENOM = LaMere.searchInColumn("Prénom", WhatToLookFor)
End Function
 
Public Property Get F_GRADE()
   F_GRADE = LaMere.getCellFromCursor("Grade")
End Property
 
Public Property Let F_GRADE(v)
   LaMere.getCellFromCursor("Grade") = v
End Property
Public Property Get FN_GRADE() As String
   FN_GRADE = "Grade"
End Property
Public Property Get R_GRADE() As Range
   Set R_GRADE = LaMere.getColdataRange("Grade")
End Property
Public Function S_GRADE(WhatToLookFor As String) As Range
   Set S_GRADE = LaMere.searchInColumn("Grade", WhatToLookFor)
End Function

Si cela t'intéresse, et intéresse d'autres personnes je peux travailler pour adapter ton code et y ajouter cette couche objet en fournissant le précompilateur VBA que j'ai développé.

[laurent_ott]

Bonjour,
Lors de la copie d'une colonne avec TS_CopierValeurColonne je constate le décalage d'une ligne vers le haut, écrasant ainsi le titre de la colonne.

Bonjour,
Comment indiqué en messagerie privée, le problème vient de la déclaration de vos variables que vous déclarez en ListObjet au lieu de Range.
Le code correct est le suivant :

Code VBA :

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

1
2
3
4
5
6
7
Dim Tab1 As Range, Tab2 As Range
Dim resultat As Boolean
 
Set Tab1 = Range("Tableau1")
Set Tab2 = Range("Tableau2")
 
resultat = TS_CopierValeurColonne(Tab1, "col1", Tab2, "col1", TS_RemplacerDonnées, False)

Notez également que la feuille du tableau structuré n'a pas besoin d'être indiquée (si elle est dans le classeur actif) car Excel la retrouve automatiquement d'après le nom du tableau structuré.
Une autre façon d'appeler la fonction est de passer en argument directement le tableau sans utiliser une variable intermédiaire, ce qui donne :

Code VBA :

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

1
2
Dim resultat As Boolean
resultat = TS_CopierValeurColonne(Range("Tableau1"), "col1", Range("Tableau2"), "col1", TS_RemplacerDonnées, False)

[rossemma]

Bonjour,

j''utilise la fonction TS_Filtres_Poser pour filtrer mon tableau.

"Call TS_Filtres_Poser(Tableau, 3, "<>S48*", xlAnd, "<>S99*")"

cela fonctionne mais dès que je veux ajouter un xland à savoir "Call TS_Filtres_Poser(Tableau, 3, "<>S48*", xlAnd, "<>S20*", xlAnd, "<>S99*")"

cela plante

pourquoi?

Merci pour votre aide

[jurassic pork]

Hello,

j''utilise la fonction TS_Filtres_Poser pour filtrer mon tableau.
"Call TS_Filtres_Poser(Tableau, 3, "<>S48*", xlAnd, "<>S99*")"
cela fonctionne mais dès que je veux ajouter un xland à savoir "Call TS_Filtres_Poser(Tableau, 3, "<>S48*", xlAnd, "<>S20*", xlAnd, "<>S99*")"
cela plante
pourquoi?

Dans la documentation c'est précisé clairement que :

Le filtre peut contenir un ou deux critères.

Tu veux utiliser trois critères.

Ami calmant, J.P

[rossemma]

oui trois sur cette colonne

Hello,

Dans la documentation c'est précisé clairement que :


Tu veux utiliser trois critères.

Ami calmant, J.P

[laurent_ott]

Bonjour,

j''utilise la fonction TS_Filtres_Poser pour filtrer mon tableau.
"Call TS_Filtres_Poser(Tableau, 3, "<>S48*", xlAnd, "<>S99*")"
cela fonctionne mais dès que je veux ajouter un xland à savoir "Call TS_Filtres_Poser(Tableau, 3, "<>S48*", xlAnd, "<>S20*", xlAnd, "<>S99*")"
cela plante

Bonjour,
Je vous propose de boucler sur les lignes de votre tableau pour masquer celles que vous ne souhaitez pas afficher : =S48* ou =S20* ou =S99*
(ou faire l'inverse si vous prenez le problème dans l'autre sens)

Code VBA :

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

1
2
3
4
5
6
7
8
9
For i = 1 To TS_Nombre_Lignes(Tableau)
    If TS_InfoCellule(Tableau, 3, i) Like "S48*" = True _
    Or TS_InfoCellule(Tableau, 3, i) Like "S20*" = True _
    Or TS_InfoCellule(Tableau, 3, i) Like "S99*" = True _
    Then
        y = TS.Row + i - 1
        Rows(y).Hidden = True
    End If
Next i

Et pour afficher toutes les lignes :

Code VBA :

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

Tableau.Rows.Hidden = False

Cordialement.

[Bill73]

Bonjour,

tout d'abord, bravo Laurent pour ce travail.
j'ai un petit soucis, il y a une procédure pour importer un tableau depuis un fichier extérieur fermé, ...mais je n'ai rien trouvé pour exporter dans un tableau extérieur fermé.
J'ai un ensemble de tableaux structurés dans un fichier, et j'aimerai, après différentes modifs, pouvoir mettre à jour ces tableaux sans ouvrir les fichiers sources.
Est-ce possible ?

[laurent_ott]

Bonjour,
Je vais étudier ce cas et vous tiendrai informé.
Bonne continuation.

[laurent_ott]

j'ai un petit soucis, il y a une procédure pour importer un tableau depuis un fichier extérieur fermé, ...mais je n'ai rien trouvé pour exporter dans un tableau extérieur fermé.

Bonjour,
J'imagine que vous souhaitez mettre à jour les tableaux d’un classeur extérieur fermé à partir d’un classeur ouvert, sans que l’utilisateur soit perturbé par l’affichage du classeur.
Je vous propose la solution suivante, qui ouvre le fichier extérieur avec Excel mais le masque immédiatement avec l’instruction Windows().Visible = False.
Cela permet d’utiliser les fonctions du module « TS » sans incidence sur l’affichage.
Pensez à rendre visible le classeur avant de l’enregistrer.
Ce qui pourrait donner ce code simplifié dans le cas où il faut remplacer les données du tableau "TS_Eleves" contenues dans le classeur "C:\Users\ott_l\Downloads\Test_TS.xlsm" par les données du tableau "TS_Eleves" du classeur actif :

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
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Dim Wk As Workbook
Set Wk = Workbooks.Open("C:\Users\ott_l\Downloads\Test_TS.xlsm")
Windows(Wk.Name).Visible = False
 
Dim TD As Range
Set TD = Wk.Sheets("Feuil1").Range("TS_Eleves")
 
Dim TS As Range
Set TS = Range("TS_Eleves")
 
Call TS_CopierUnTableau(TS, TD, TS_RemplacerDonnées, TS_Valeurs)
 
Windows(Wk.Name).Visible = True
Wk.Save
 
End Sub
'------------------------------------------------------------------------------------------------

Attention, avant tout il faut s’assurer que le fichier à modifier existe, qu’il ne soit pas déjà ouvert, qu’il ne soit pas en lecture seule.
L’ouverture du fichier peut déclencher l’événement « Workbook_Open » ou la mise à jour des liaisons, ce qui n’est pas toujours souhaité.

J’ai donc conçu une fonction générique qui prend en charge ces problèmes ainsi que la gestion des erreurs.
Le premier argument est classique, c’est le fichier à ouvrir, le deuxième est plus curieux car il représente le nom de la macro personnelle à exécuter pour la mise à jour du fichier. Car pour un usage générique il n’est pas possible d’avoir ces traitements à l’intérieur de la fonction, il faut les externaliser.
Les autres arguments sont facultatifs, ils définissent les éventuels mots de passes, s’il faut mettre à jour ou non les liaisons et les événements.

En reprenant notre exemple, où les traitements sont réalisés par la fonction « MonTraitementPersonnel » contenue dans le module « Module1 » cela donne ceci :

L’ouverture du classeur à modifier se fait par l’appel à la fonction « TS_OuvrirClasseurInvisible » comme dans cet exemple :

Code VBA :

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

1
2
3
4
5
6
'------------------------------------------------------------------------------------------------
Sub Exemple()
'------------------------------------------------------------------------------------------------
Call TS_OuvrirClasseurInvisible("C:\Users\ott_l\Downloads\Test_TS.xlsm", "Module1.MonTraitementPersonnel")
End Sub
'------------------------------------------------------------------------------------------------

Qui fait référence à cette fonction du « Module1 » pour la copie du tableau :

Code VBA :

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

1
2
3
4
5
6
'------------------------------------------------------------------------------------------------
Sub MonTraitementPersonnel(Wk As Workbook)
'------------------------------------------------------------------------------------------------
Call TS_CopierUnTableau(Range("TS_Eleves"), Wk.Sheets("Feuil1").Range("TS_Eleves"), TS_RemplacerDonnées, TS_Valeurs)
End Sub
'------------------------------------------------------------------------------------------------

Le code de la fonction :

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
'------------------------------------------------------------------------------------------------------
Public Function TS_OuvrirClasseurInvisible(Fichier As String, _
                                           FonctionPersonnelle As String, _
                                           Optional MotDePasseOuverture As String = "", _
                                           Optional MotDePasseEcriture As String = "", _
                                           Optional MAJ_Liens As Boolean = False, _
                                           Optional DésactiveMacros As Boolean = True) As Boolean
'------------------------------------------------------------------------------------------------------
' Ouvre un classeur Excel en le mettant invisible pour que l'utilisateur ne soit pas perturbé par les
' manipulations qui y sont faites.
'------------------------------------------------------------------------------------------------------
' Fichier : le classeur Excel qu'il faut ouvrir (chemin complet + nom avec l'extension).
' FonctionPersonnelle : nom de la fonction personnelle à exécuter, précédé du nom du module où elle se trouve.
'                       La fonction doit avoir en argument un classeur, qui sera le classeur ouvert.
'                       Par exemple : Sub MonTraitementPersonnel(Wk As Workbook)
' 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 Vrai si tout s'est bien passé, Faux dans le cas contraire.
'------------------------------------------------------------------------------------------------------
' Exemple d'utilisation pour remplacer les données du tableau "TS_Eleves" contenues dans le classeur
' "C:\Users\ott_l\Downloads\Test_TS.xlsm" par les données du tableau "TS_Eleves" du classeur actif:
'
'Sub Example()
'Call TS_OuvrirClasseurInvisible("C:\Users\ott_l\Downloads\Test_TS.xlsm", "Module1.MonTraitementPersonnel")
'End Sub
'
'Sub MonTraitementPersonnel(Wk As Workbook)
'Call TS_CopierUnTableau(Range("TS_Eleves"), Wk.Sheets("Feuil1").Range("TS_Eleves"), TS_RemplacerDonnées, TS_Valeurs)
'End Sub
'------------------------------------------------------------------------------------------------------
Dim Wk As Workbook
Dim Filenum As Long, Anc_Attributs As Long
Dim ObjFile As Object
Dim AncienCancelkey As Long
Dim AncienCursor As Long
Dim AncienScreenUpdating As Boolean
Dim AncienEnableEvents As Boolean
 
' Gestion des erreurs:
On Error GoTo Gest_Err
Err.Clear
 
' 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:
Anc_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
 
' Mémorise les anciens états:
AncienCursor = Application.Cursor
AncienScreenUpdating = Application.ScreenUpdating
AncienEnableEvents = Application.EnableEvents
 
' Bloque la mise à jour de l'écran:
Application.Cursor = xlWait
Application.ScreenUpdating = False
 
' Empêche l'utilisateur d'interrompre le traitement avec Echap:
AncienCancelkey = Application.EnableCancelKey
Application.EnableCancelKey = xlDisabled
 
' 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
    ' Désactive les événements:
    Application.EnableEvents = False
    ' 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 = False
ThisWorkbook.Activate
 
' Lance mes traitements:
Call Application.Run(FonctionPersonnelle, Wk)
 
' Réaffiche le classeur et l'enregistre:
Windows(Wk.Name).Visible = True
Wk.Activate
Application.WindowState = xlMinimized
Wk.Save
 
' Renvoie Vrai:
TS_OuvrirClasseurInvisible = True
 
' Fin du traitement:
Gest_Err:
 
' Ferme le classeur s'il est ouvert:
If Not Wk Is Nothing Then Wk.Saved = True: Wk.Close
 
' Restaure les anciens attributs du classeur:
If Not ObjFile Is Nothing Then ObjFile.Attributes = Anc_Attributs
 
' Active l'écran:
ThisWorkbook.Activate
 
' Réactive l'ancienne configuration:
Application.EnableCancelKey = AncienCancelkey
Application.Cursor = AncienCursor
Application.ScreenUpdating = AncienCursor
Application.EnableEvents = AncienEnableEvents
 
If Err.Number <> 0 Then
    Application.Cursor = xlDefault
    MsgBox Err.Number & " : " & Err.Description, vbInformation, Application.Name
    Application.Cursor = AncienCursor
End If
Err.Clear
 
End Function
'------------------------------------------------------------------------------------------------------

A noter que l'ouverture d'un gros fichier avec une connexion lente provoque l'affichage par Windows d'une barre de progression du chargement, que l'utilisateur peut annuler. Idem pour l'enregistrement.
Cordialement.

[deedolith]

Je pense que ces dernières fonctionnalités sont hors sujet.

En effet, tu commences à gérer des classeurs, ce qui n'a rien à voir avec les tableaux structurés.
Par respect du SRP, cela doit être fait au niveau supérieur (en dehors des fonctions).

[Bill73]

Bonjour,

déjà merci pour le code.
je vais décortiquer tout ça et reviens vous dire.

[Bill73]

Bonjour,

merci Laurent, tout marche parfaitement.

A deedolith, je ne vois pas pourquoi hors sujet. Comme je l'ai dit dans mon premier message, il y a une fonction pour importer un tableau, pourquoi pas une fonction pour exporter ce tableau !

[deedolith]

Une fonction nommée TS_OuvrirClasseurInvisible, comme son nom l'indique, tente d'ouvrir un classeur.

Sans regarder son implémentation, cela pose déjà question:
Quel est le rapport direct avec la gestion des tableaux structurés ?
En regardant l'implémentation, on s'aperçoit que cette dernière ne manipule aucun tableau, ce qui pose la même question une second fois.

Utilisée en interne (donc private), cela à du sens, l'utilisateur final ne se soucie pas de l'implémentation (et il n'a pas à s'en soucier).
Sur l'export / import de données, la bibliothèque va acquérir une ressource (ouvrir un fichier / connexion a une BDD ect ...), realiser l'import (ou export), libérer la ressource.
C'est le comportement qu'on attend (gestion des ressources internes transparentes).

Par contre, mettre a disposition les ressources internes à l'utilisateur final .., c'est la porte ouverte à toutes les bêtises possibles et imaginables (et viol du principe d'encapsulation).
Et par expérience, lorsque l'on laisse une porte ouverte, les ennuis s'y engouffrerons tôt ou tard, ce n'est qu'une question de temps.

On peut faire l'analogie avec ta maison que tu désires repeindre.
Tu donnes accès aux ouvriers à l'extérieur de ta maison (tu leur fournit le service: "Accéder à l'extérieur").
Maintenant imagine que tu leurs fournisse le service: "Accéder à l'intérieur".
- S'il sont honnetes, pas de problème.
- S'ils le sont moins .... surprises surprises.

[Pierre Fauconnier]

Je suis d'accord avec Deedolith.

Un module de gestion de tables ne doit pas offrir au code client du module autre chose que des manipulations de tables. Si le module veut offrir un transfert de tables entre classeurs, la gestion de ces derniers doit être invisible pour l'utilisateur qui ne doit pas lui-même ouvrir le classeur.

On pourrait avoir une signature telle que celles-ci:
TransfererVersClasseur(ts As Range, Filename As String) et RecupererDuClasseur(FileName As String, Target As Range)
Ainsi, le code client ne manipule pas les classeurs, et les mécanismes d'ouverture/fermeture de ces derniers lui sont invisibles.

C'est cela, placer une couche d'abstraction => Permettre au code client de faire abstraction des manipulations. Sinon, la couche perd beaucoup de son intérêt.


Je reste toujours sur ma faim quant à la manipulation de Range nommés TS dans un module qui manipule des tables. TS induit un Tableau Structuré (un ListObject) et on ne devrait donc pas nommer un Range TS.

[laurent_ott]

merci Laurent, tout marche parfaitement.

Ce n'est pas votre demande initiale mais l'on pourrait aussi avoir besoin d'ouvrir plusieurs classeurs en même temps, en les masquant pour que les différents traitements réalisés soient transparents pour l'utilisateur.

Par exemple, depuis un fichier "console" qui contient le code VBA, remplacer les données du tableau "TS_Eleves" contenues dans le classeur externe "C:\Users\ott_l\Downloads\Test_TS.xlsm" sur la feuille "Feuil1" par les données du tableau "TS_Eleves" du classeur externe "C:\Users\ott_l\Downloads\Classeur_Elèves.xlsx" en feuille "Feuil1".

Je propose pour faire cela les fonctions ci-dessous.
A noter que pour les traitements je force le mode Lecture/écriture en modifiant les attributs des fichiers. Pour mémoriser les attributs d'origine, je les stocke temporairement dans les propriétés du classeur (afin de ne pas avoir recours à des variables "Private" ou "Public"), puis je les efface en fin de traitement.
Soit deux fonctions :
- "OuvrirClasseur" pour ouvrir un fichier en le forçant en lecture/écriture, en indiquant s'il faut ou non le rendre visible (plus les paramètres déjà vus pour les mots de passe et les liens) ;
- "FermerClasseur" pour le refermer et l'enregistrer si besoin.

Les trois fonctions "PropriétéEcrire", "PropriétéEcrire" et "PropriétéSupprimer" sont utilisées pour la gestion des propriétés personnelles du classeur.

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
'------------------------------------------------------------------------------------------------
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) ' Ouvre le classeur qui contient le tableau source.
 
Dim Wk_D As Workbook
Set Wk_D = OuvrirClasseur("C:\Users\ott_l\Downloads\Test_TS.xlsm", False) ' Ouvre le classeur à modifier. 
 
Dim TS As Range: Set TS = Wk_S.Sheets("Feuil1").Range("TS_Eleves")   ' Définition du tableau source.
Dim TD As Range: Set TD = Wk_D.Sheets("Feuil1").Range("TS_Eleves")   ' Définition du tableau destination à remplacer.
 
Call TS_CopierUnTableau(TS, TD, TS_RemplacerDonnées, TS_Valeurs)  ' Remplace les données.
Call TS_FormatColonne(TD, "Note", "0.0", True)                    ' Force le format numérique de la colonne.
 
Call FermerClasseur(Wk_S, False) ' Ferme la source sans l'enregistrer.
Call FermerClasseur(Wk_D, True)  ' Ferme la destination et l'enregistre.
 
ThisWorkbook.Activate
MsgBox "fin"
 
' Gestion des erreurs:
Gest_Err:
If Err.Number <> 0 Then MsgBox Err.Number & " : " & Err.Description, vbExclamation
Err.Clear
 
End Sub
'------------------------------------------------------------------------------------------------

Les fonctions :

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
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
'------------------------------------------------------------------------------------------------------
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é, Nothing dans le cas contraire.
'------------------------------------------------------------------------------------------------------
' 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)
'
'ThisWorkbook.Activate
'MsgBox "fin"
'
'Gest_Err:
'If Err.Number <> 0 Then MsgBox Err.Number & " : " & Err.Description, vbExclamation
'Err.Clear
'------------------------------------------------------------------------------------------------------
Dim Wk As Workbook
Dim Filenum As Long
Dim ObjFile As Object
Dim Anc_ScreenUpdating As Boolean
Dim Anc_Attributs As Long
Dim Anc_Wk As Workbook
 
' Gestion des erreurs:
On Error GoTo Gest_Err
Err.Clear
 
' 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:
Anc_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
 
' Bloque la mise à jour de l'écran:
Anc_ScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
 
' 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
    ' Désactive les événements:
    Application.EnableEvents = False
    ' 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
 
' Mémorise les informations utilisées pour la fermeture dans les propriétés du classeur:
Call PropriétéEcrire("MémoClasseur_Fichier", Fichier)
Call PropriétéEcrire("MémoClasseur_Attributs", Anc_Attributs)
 
' Masque (ou non) le classeur que l'on vient d'ouvrir:
Windows(Wk.Name).Visible = Visible
 
' Active 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 = PropriétéLire("MémoClasseur_Attributs")
    Err.Raise Err.Number
End If
 
End Function
 
'------------------------------------------------------------------------------------------------------
Public Function FermerClasseur(Classeur As Workbook, Enregistrer As Boolean) As Boolean
'------------------------------------------------------------------------------------------------------
Dim ObjFile As Object
Dim Anc_ScreenUpdating As Boolean
Dim Fichier As String
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
 
' Récupère les propriétés du classeur:
Classeur.Activate
Fichier = PropriétéLire("MémoClasseur_Fichier")
Attributs = PropriétéLire("MémoClasseur_Attributs")
 
' S'il faut l'enregistrer:
If Enregistrer = True Then
    Windows(Classeur.Name).Visible = True
    Application.WindowState = xlMinimized
    Call PropriétéSupprimer("MémoClasseur_Attributs")
    Call PropriétéSupprimer("MémoClasseur_Fichier")
    Classeur.Save
End If
 
' Ferme le fichier:
Classeur.Saved = True
Classeur.Close
 
' Restaure les anciens attributs du classeur:
If Attributs <> 0 Then
    ' Une erreur est déclenchée si le fichier source n'est pas trouvé:
    Set ObjFile = CreateObject("Scripting.FileSystemObject").GetFile(Fichier)
    ObjFile.Attributes = Attributs
End If
 
' Renvoie Vrai:
FermerClasseur = True
 
' Gestion des erreurs:
Gest_Err:
 
Application.ScreenUpdating = Anc_ScreenUpdating
If Err.Number <> 0 Then Err.Raise Err.Number
 
End Function
 
'----------------------------------------------------------------------------------------
Private Function PropriétéEcrire(NomPropriété As String, ValPropriété As Variant, _
                                 Optional TypePropriété As MsoDocProperties = msoPropertyTypeString) As Boolean
'----------------------------------------------------------------------------------------
' Ecrit la propriété NomPropriété avec la valeur ValPropriété au format String par défaut
' dans le classeur actif.
'----------------------------------------------------------------------------------------
On Error GoTo Gest_Err
' Supprime la propriété si elle existe déjà:
Call PropriétéSupprimer(NomPropriété)
' Crée la propriété et retourne True si tout se passe bien:
ActiveWorkbook.CustomDocumentProperties.Add Name:=NomPropriété, value:=ValPropriété, _
                                            Type:=TypePropriété, LinkToContent:=False
PropriétéEcrire = True
' Efface les erreurs:
Gest_Err:
Err.Clear
End Function
 
'----------------------------------------------------------------------------------------
Private Function PropriétéLire(NomPropriété As String) As Variant
'----------------------------------------------------------------------------------------
' Lit la propriété NomPropriété dans le classeur actif et renvoie sa valeur.
'----------------------------------------------------------------------------------------
On Error GoTo Gest_Err
' Lit la propriété:
PropriétéLire = ActiveWorkbook.CustomDocumentProperties.Item(NomPropriété).value
' Efface les erreurs:
Gest_Err:
Err.Clear
End Function
 
'----------------------------------------------------------------------------------------
Private Function PropriétéSupprimer(NomPropriété As String) As Boolean
'----------------------------------------------------------------------------------------
' Supprime la propriété NomPropriété dans le classeur actif,
' ou toutes les propriétés si NomPropriété = ""
'----------------------------------------------------------------------------------------
Dim p As DocumentProperty
On Error GoTo Gest_Err
If NomPropriété <> "" Then
    ActiveWorkbook.CustomDocumentProperties.Item(NomPropriété).Delete
Else
    ' Boucle sur les propriétés du classeur actif:
    For Each p In ActiveWorkbook.CustomDocumentProperties
        ActiveWorkbook.CustomDocumentProperties.Item(p.Name).Delete
    Next
End If
PropriétéSupprimer = True
' Efface les erreurs:
Gest_Err:
Err.Clear
End Function
'----------------------------------------------------------------------------------------

Bonne Continuation.

[deedolith]

Je pense que tu n'as pas compris no remarques:

Dans le cadre de cette librairie dont le but est de gérer les tableaux structurés,
tu peux, en interne, ouvrir / fermer autant de classeurs que tu veux, ce sont des ressources internes.

Par contre, mettre à disposition les ressources internes auprès des utilisateurs (programmeurs) finaux, est une faute, ce n'est pas le but de la librairie.
Les fonctions permettant d'ouvrir / fermer les classeurs doivent par conséquent être privées.

[Pierre Fauconnier]

Deedolith,

Je pense que Laurent a compris, mais c'est son module et donc, il en fait ce qu'il veut. Cela dit, ça déforce clairement l'outil qui va devenir un fourre-tout informe. Dommage.