Transposer une table Access vers Excel avec VBA

**Salo15** · 23/10/2020, 12h38

Envoyé par EricDgn

Bonjour,
Etait-ce pour cela que vous avez ajouté la colonne D: ordre de présentation ? C'est pour savoir si cette colonne est utilisable.
Cordialement.

Bonjour,

Désolée pour le délai de ma réponse. J'ai décidé de réorganiser les colonnes de mon tableau (je ne l'avais pas fait hier de peur de ne pas savoir comment réadapter le code). Maintenant que c'est réglé (voir image et pièce jointe), je réponds à votre question.

La logique d'ordre de présentation des lignes (du général au particulier) pour ce type de tableau est la suivante :

1. les "catégories" (définies par la nouvelle colonne C)
1.1 les "regroupements" au sein d'une "catégorie" (définis par la colonne D)
1.1.1 les taxons au sein d'un "regroupement" (ordre défini par la nouvelle colonne E "ordre de présentation")
1.2 les "regroupements" au sein d'une "catégorie" (tri croissant en fonction de la fréquence)

Dans ce sens, l'ajout de la nouvelle colonne E "ordre de présentation" est, en effet, une étape vers le tri des lignes (étape 1.1.1). Il correspond a l'ordre de présentation au sein du "regroupement", car dans ce cas le tri "A à Z" n'est pas adapté. Par exemple, pour un regroupement je peux avoir plusieurs lignes différentes : Triticum dicoccon, Triticum cf. dicoccon, cf. Triticum dicoccon (c'est l'ordre que je souhaite obtenir au sein du "regroupement", si je lance un simple tri par ordre alphabétique, le résultat ne sera pas adapté.

Jusque là, grâce à votre aide, j'obtiens un tableau bien présenté. Il resterai donc à résoudre le point 1.2

Voici le tout dernier code (avec les colonnes réorganisées pour plus de logique)

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
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
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
Private Sub Exporter_RQT_Click()
 
Dim oRst As Recordset
Dim oDb As Database
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim xlWsTmp As Object
Dim i As Long
Dim j As Long
Dim Rng As Object
Dim sRng As String
Dim sFml As String
Dim sTitre As String
 
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open("C:\Users\Laura\Desktop\Thèse\BDD\BDRAB thèse\Tableurs_Decompte\Export.xlsx")
' rendre visible Excel
    xlApp.Visible = True
 
    Set oDb = CurrentDb()
 
'--- Export table1 dans feuille "PresentationEchant"
 
    Set oRst = oDb.OpenRecordset("select * from RQT_Decompte_PresentationEchant")
    Set xlWs = xlWb.Worksheets("PresentationEchant")
 
' efface les anciennes données table 1
    xlWs.Select
    xlWs.cells.ClearContents
 
' entête dans 1ère ligne
    For i = 0 To oRst.Fields.Count - 1
        xlWs.Range("A1").Offset(0, i) = oRst(i).Name
    Next i
 
' enregistrement des nouvelles données table 1
    If Not oRst.EOF Then xlWs.cells(2, 1).CopyFromRecordset oRst
    xlWs.Range("A1").Select
 
'--- Export table3 dans la feuille "Tmp" puis recopie transposée dans feuille "Decompte"
 
    Set xlWs = xlWb.Worksheets("Decompte")
 
' efface les anciennes données table 2
    xlWs.Select
    With xlWs.cells
        .ClearContents
        .ClearFormats
        .Font.Name = "Arial"   '--- ou Arial Narrow ?
        .Font.Size = 10
        .HorizontalAlignment = -4108                        '--- xlCenter= -4108
        xlWs.Columns("A:H").HorizontalAlignment = -4131     '--- xlLeft= -4131
 
    End With
 
    Set oRst = oDb.OpenRecordset("select * from RQT_Decompte_InfosEchant")
 
' définition feuille Tmp (reçoit données à transposer)
    Set xlWsTmp = xlWb.Worksheets("Tmp")   '<--- avoir aussi une feuille nommée Tmp
    xlWsTmp.Select
 
' entête dans 1ère ligne
    For i = 0 To oRst.Fields.Count - 1
        xlWsTmp.Range("A1").Offset(0, i) = oRst(i).Name
    Next i
 
' enregistrement des nouvelles données table 3
    If Not oRst.EOF Then xlWsTmp.Range("A2").CopyFromRecordset oRst
    xlWsTmp.Range("A1").Select
 
' récupère données
    Set Rng = xlWsTmp.UsedRange
 
' transpose à l'endroit souhaité
    Rng.Copy
    xlWs.Range("H1").PasteSpecial Paste:=-4163, Transpose:=True
    xlWs.Rows("1:30").Font.Bold = True
 
' vide plage temporaire
    Rng.Clear
    xlWs.Select
 
'--- Export table2 dans feuille "Decompte"
 
    Set oRst = oDb.OpenRecordset("select * from RQT_Decompte_AC_EchantColonne_TaxonLigne")
 
' entête dans 1ère ligne en A30
    For i = 0 To oRst.Fields.Count - 1
        xlWs.Range("A30").Offset(0, i) = oRst(i).Name
    Next i
 
' enregistrement des nouvelles données table 2
    If Not oRst.EOF Then xlWs.Range("A31").CopyFromRecordset oRst
    xlWs.Range("A30").Select
 
' Pour chaque ligne de la feuille à partir de la ligne 31
    xlWs.Select
    With xlWs
        '--- mise en italique
        i = 31
        Do While .Range("B" & i).Value <> ""            '--- parcourt la liste jusqu'à tomber sur celule vide
            Set Rng = .Range("B" & i)
            Rng.Font.Bold = False
            Rng.Font.Italic = True
            If InStr(1, Rng.Value, "cf.") > 0 Then Rng.Characters(InStr(1, Rng.Value, "cf."), Len("cf.")).Font.Italic = False
            If InStr(1, Rng.Value, "s.l.") > 0 Then Rng.Characters(InStr(1, Rng.Value, "s.l."), Len("s.l.")).Font.Italic = False
            If InStr(1, Rng.Value, "fo.") > 0 Then Rng.Characters(InStr(1, Rng.Value, "fo."), Len("fo.")).Font.Italic = False
            If InStr(1, Rng.Value, "ssp.") > 0 Then Rng.Characters(InStr(1, Rng.Value, "ssp."), Len("ssp.")).Font.Italic = False
            If InStr(1, Rng.Value, "agg.") > 0 Then Rng.Characters(InStr(1, Rng.Value, "agg."), Len("agg.")).Font.Italic = False
            If InStr(1, Rng.Value, "sp.") > 0 Then Rng.Characters(InStr(1, Rng.Value, "sp."), Len("sp.")).Font.Italic = False
            If InStr(1, Rng.Value, "Indeterminata") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Indeterminata"), Len("Indeterminata")).Font.Italic = False
            If InStr(1, Rng.Value, "Rosaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Rosaceae"), Len("Rosaceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Leguminosae sativae indeterminatae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Leguminosae sativae indeterminatae"), Len("Leguminosae sativae indeterminatae")).Font.Italic = False
            If InStr(1, Rng.Value, "Amaranthaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Amaranthaceae"), Len("Amaranthaceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Apiaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Apiaceae"), Len("Apiaceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Cerealia indeterminata") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Cerealia indeterminata"), Len("Cerealia indeterminata")).Font.Italic = False
            If InStr(1, Rng.Value, "Asteraceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Asteraceae"), Len("Asteraceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Caryophyllaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Caryophyllaceae"), Len("Caryophyllaceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Coleoptera") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Coleoptera"), Len("Coleoptera")).Font.Italic = False
            If InStr(1, Rng.Value, "Coprolithe") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Coprolithe"), Len("Coprolithe")).Font.Italic = False
            If InStr(1, Rng.Value, "Fabaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Fabaceae"), Len("Fabaceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Gasteropoda") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Gasteropoda"), Len("Gasteropoda")).Font.Italic = False
            If InStr(1, Rng.Value, "Lamiaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Lamiaceae"), Len("Lamiaceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Liliaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Liliaceae"), Len("Liliaceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Pain/galette/bouillie") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Pain/galette/bouillie"), Len("Pain/galette/bouillie")).Font.Italic = False
            If InStr(1, Rng.Value, "Panicoideae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Panicoideae"), Len("Panicoideae")).Font.Italic = False
            If InStr(1, Rng.Value, "Poaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Poaceae"), Len("Poaceae")).Font.Italic = False
            If InStr(1, Rng.Value, "Polygonaceae") > 0 Then Rng.Characters(InStr(1, Rng.Value, "Polygonaceae"), Len("Polygonaceae")).Font.Italic = False
            i = i + 1
        Loop
 
        '--- pour avoir 1 décimale ligne Densité
        j = .Range("I27").End(-4161).Column                     '--- n° dernière colonne non vide de la ligne n°27 --- xlToRight = -4161
        sRng = .Range(.cells(27, 9), .cells(27, j)).Address     '--- 9 = colonne I
        .Range(.cells(27, 9), .cells(27, j)).numberFormat = "0.0" '--- ou "0.0%" pour avoir 1 décimale
 
        '--- ajout totaux en dernière ligne                     '--- i = n° ligne vide en bas du tableau
        j = .Range("I30").End(-4161).Column                     '--- n° dernière colonne non vide de la ligne n°30 --- xlToRight = -4161
        sRng = .Range(.cells(31, 9), .cells(i - 1, 9)).Address  '--- 9 = colonne I
        sRng = Replace(sRng, "$", "")                           '--- pour obtenir une adresse relative
        .Range(.cells(i, 9), .cells(i, j + 1)).Formula = "=SUM(" & sRng & ")"
        .cells(i, 2).Value = "Total NMI"
        .Range(.cells(i, 1), .cells(i, j + 2)).Font.Bold = True
        .Range(.cells(i, 1), .cells(i, j + 3)).Borders(8).Weight = 2   '--- xlEdgeTop = 8    --- xlThin = 2 --- xlMedium = -4138
        .Range(.cells(i, 1), .cells(i, j + 3)).Borders(9).Weight = 2   '--- xlEdgeBottom = 9
 
        '--- ajout totaux en dernière colonne
        j = j + 1                                               '--- n° colonne
        i = i - 1                                               '--- n° dernière ligne de données
        .cells(30, j).Value = "Total NMI"
        sRng = .Range(.cells(31, 9), .cells(31, j - 1)).Address '--- 31 = n° première ligne à sommer
        sRng = Replace(sRng, "$", "")                           '--- pour obtenir une adresse relative
        .Range(.cells(31, j), .cells(i, j)).Formula = "=SUM(" & sRng & ")"
        .Range(.cells(31, j), .cells(i, j)).Font.Bold = True
 
        '--- ajout pourcentages en dernière colonne
        j = j + 1                                               '--- n° colonne
        .cells(30, j).Value = .cells(i + 1, j - 1).Value & " = 100%"
        sRng = .cells(31, j - 1).Address
        sRng = Replace(sRng, "$", "")
        sRng = sRng & "/" & .cells(i + 1, j - 1).Address        '--- le rapport
        sFml = "=If(Rng=0,'-',If(Rng<0.005,'r', If(Rng<0.01,'+',Rng)))"  '--- modèle de la formule --- 0.005 = 5% --- 0.01 = 1%
        sFml = Replace(sFml, "Rng", sRng)                       '--- remplace Rng par sRng (rapport)
        sFml = Replace(sFml, "'", Chr(34))                      '--- remplace les " par "
        .Range(.cells(31, j), .cells(i, j)).Formula = sFml
        .Range(.cells(31, j), .cells(i, j)).numberFormat = "0%" '--- ou "0.0%" pour avoir 1 décimale
        .Range(.cells(31, j), .cells(i, j)).Font.Bold = True
 
        '--- ajouts fréquences
        Freq xlWs, j + 1
 
 
        '--- ajouts des sous-titres
        sTitre = ""
        i = 31
        Do While .Range("A" & i).Value <> ""            '--- parcourt la liste jusqu'à tomber sur celule vide
            If .Range("C" & i).Value <> sTitre Then
                sTitre = .Range("C" & i).Value
                .Range("A" & i).EntireRow.Insert shift:=-4121, CopyOrigin:=1    '<-- 1 sans doute préférable à 0
                .Range("B" & i).Value = sTitre
                .Range("B" & i).Font.Bold = True
                .Range("B" & i).Font.Italic = False
                .Range(.cells(i, 2), .cells(i, j)).Merge                        '--- j = n° colonne pourcentages
            End If
            i = i + 1
        Loop
 
        '--- masquer colonnes C, D et E
        .Columns("C:E").EntireColumn.Hidden = True
 
    End With
 
' fermeture des instances ouvertes
    oRst.Close
    xlWb.Close True
    Set oRst = Nothing
    Set oDb = Nothing
    Set Rng = Nothing
    Set xlWsTmp = Nothing
    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
End Sub
 
Private Sub Freq(R As Object, j As Long)
    Dim kR As Long, kC As Long
    Dim k(100) As Variant, n As Integer             '--- 100 = nombre maximal de colonnes dans la feuille
    Dim sGroupe As String, kRGroupe As Long
    kR = 30
    kRGroupe = kR
    sGroupe = ""
    With R
        .cells(kR, j).Value = "Fréquence" & vbLf & (j - 11) & " = 100%"
        .cells(kR, j).HorizontalAlignment = -4108
        kR = kR + 1
        While .cells(kR - 1, 2).Value <> ""
            If .cells(kR, 4).Value <> sGroupe Then  '--- nouveau groupe
                '--- résultat groupe précédent
                n = 0
                For kC = 9 To j - 3                 '--- 9 = première colonne de données
                    If k(kC) <> 0 Then n = n + 1    '--- nb de colonnes non nulles = nb de lieux
                    k(kC) = 0
                Next kC
                If sGroupe <> "" Then               '--- inscrit fréquence
                    .cells(kRGroupe, j).Value = n / (j - 11)
                    .cells(kRGroupe, j).numberFormat = "0%"
                    .cells(kRGroupe, j).Font.Bold = True
                End If
                '--- début nouveau groupe
                kRGroupe = kR
                sGroupe = .cells(kR, 4).Value
            End If
            '--- cumul par colonne/lieu pour le groupe en cours
            For kC = 9 To j - 3                     '--- 9 = première colonne de données, j-3 dernière colonne
                k(kC) = k(kC) + .cells(kR, kC)
            Next kC
            kR = kR + 1
        Wend
    End With
End Sub

Merci par avance

**Salo15** · 23/10/2020, 12h48

Re-bonjour,

Voici le tableau de l'image avec 53 lignes (pièce jointe).

Cordialement.

**EricDgn** · 23/10/2020, 14h59

A tester. Posez un point d'arrêt en dernière ligne pour vérifier ce qui s'est passé en fin d'exécution de cette partie.

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
Private Sub Freq(R As Object, j As Long)
    Dim kR As Long, kC As Long
    Dim k(100) As Variant, n As Integer             '--- 100 = nombre maximal de colonnes dans la feuille
    Dim sGroupe As String, kRGroupe As Long
    kR = 30
    kRGroupe = kR
    sGroupe = ""
    With R
        .Cells(kR, j).Value = "Fréquence" & vbLf & (j - 11) & " = 100%"
        .Cells(kR, j).HorizontalAlignment = -4108
        kR = kR + 1
        While .Cells(kR - 1, 2).Value <> ""
            If .Cells(kR, 4).Value <> sGroupe Then  '--- nouveau groupe --- 4 = colonne Regroupement
                '--- résultat groupe précédent
                n = 0
                For kC = 9 To j - 3                 '--- 9 = première colonne de données
                    If k(kC) <> 0 Then n = n + 1    '--- nb de colonnes non nulles = nb de lieux
                    k(kC) = 0
                Next kC
                If sGroupe <> "" Then               '--- inscrit fréquence
                    .Cells(kRGroupe, j).Value = n / (j - 11)
                    .Cells(kRGroupe, j).numberFormat = "0%"
                    .Cells(kRGroupe, j).Font.Bold = True
                    '--- préparation tri sur colonne 5 - Ordre de présentation
                    '--- présume que les 3 premiers caractères de la colonne 4 Regroupement
                    '--- constituent le début de la clé de tri
                    For kRgr = kRGroupe To kR - 1
                        .Cells(kRgr, 5).Value = Left(sGroupe, 3) & Format(j - n, "00") & Format(kR - kRgr, "00")
                    Next kRgr
                End If
                '--- début nouveau groupe
                kRGroupe = kR
                sGroupe = .Cells(kR, 4).Value
            End If
            '--- cumul par colonne/lieu pour le groupe en cours
            For kC = 9 To j - 3                     '--- 9 = première colonne de données, j-3 dernière colonne
                k(kC) = k(kC) + .Cells(kR, kC)
            Next kC
            kR = kR + 1
        Wend
        kR = kR - 1                                 '--- dernière ligne non vide du tableau - à vérifier
        '--- tri
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("E31:E" & kR)
        .Sort.SetRange Range(Cells(31, 1), Cells(kR, j))
        .Sort.Apply
    End With
End Sub

Cordialement

**Salo15** · 23/10/2020, 16h17

Envoyé par EricDgn

A tester. Posez un point d'arrêt en dernière ligne pour vérifier ce qui s'est passé en fin d'exécution de cette partie.

Cordialement

Voici ce qui s'affiche
Cordialement

**EricDgn** · 23/10/2020, 17h05

Oui, ajouter des points (qui indiquent que cela fait référence à l'objet R du With R):

Code :

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

1
2
        .Sort.SortFields.Add Key:=.Range("E31:E" & kR)
        .Sort.SetRange .Range(.Cells(31, 1), .Cells(kR, j))

Cordialement.

**Salo15** · 23/10/2020, 17h26

Envoyé par EricDgn

Oui, ajouter des points (qui indiquent que cela fait référence à l'objet R du With R):

Code :

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

1
2
        .Sort.SortFields.Add Key:=.Range("E31:E" & kR)
        .Sort.SetRange .Range(.Cells(31, 1), .Cells(kR, j))

Cordialement.

J'ai testé et ça marche pour le tri des fréquences mais du coup, les autres paramètres de tri sont perdus.
Il regroupe des taxons complétement différents (le tri de la colonne D "regroupement" est perdu), et le tri déterminé par la colonne E "ordre de regroupement" est perdu aussi du fait que les données sont écrasées par les nouvelles données collées (pièce jointe)...

Cordialement.

**EricDgn** · 23/10/2020, 17h55

Remplacez la ligne 28 par ceci:

Code :

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

                        .Cells(kRgr, 5).Value = Left(sGroupe, 3) & Format(j - n, "00") & Format(kRgr, "00")

Cordialement.

**Salo15** · 23/10/2020, 18h30

Envoyé par EricDgn

Remplacez la ligne 28 par ceci:

Code :

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

                        .Cells(kRgr, 5).Value = Left(sGroupe, 3) & Format(j - n, "00") & Format(kRgr, "00")

Cordialement.

Trop fort ! ça marche nickel

Encore un grand merci !

Transposer une table Access vers Excel avec VBA

Requêtes et SQL.

Discussions similaires

Partager

Partager