Création d'un menu déroulant

Version imprimable

Voir 40 message(s) de cette discussion en une page

31/05/2019, 19h10
Pierre Fauconnier

Le code que tu donnes me pose problème et n'est probablement pas complet ('...). Car si je le lis tel que donné, lorsque tu crées ta colonne "Anomalie?" après, tu as un Exit For, donc tu ne saurais pas passer sur la ligne qui pose problème après avoir ajouté la colonne, sauf si le '... contient le Next qui ferme le bloc For...Next...

Ca ne nous aide pas à voir ce que peut valoir Feuille, L et num_colonne_ano lorsque tu arrives sur la ligne .Add. Au passage, dans tes codes précédents, c'était num_colonne et pas num_colonne_ano...

Du coup, on n'y comprend plus rien du tout.

Mets un point d'arrêt sur la ligne qui pose problème et vérifie tes variables à ce moment-là et si tu veux qu'on t'aide, donne-nous le code réel, car sans cela, c'est la purée de pois... ;)
31/05/2019, 19h42
juberrebi

Citation:

Envoyé par Pierre Fauconnier

Le code que tu donnes me pose problème et n'est probablement pas complet ('...). Car si je le lis tel que donné, lorsque tu crées ta colonne "Anomalie?" après, tu as un Exit For, donc tu ne saurais pas passer sur la ligne qui pose problème après avoir ajouté la colonne, sauf si le '... contient le Next qui ferme le bloc For...Next...

Ca ne nous aide pas à voir ce que peut valoir Feuille, L et num_colonne_ano lorsque tu arrives sur la ligne .Add. Au passage, dans tes codes précédents, c'était num_colonne et pas num_colonne_ano...

Du coup, on n'y comprend plus rien du tout.

Mets un point d'arrêt sur la ligne qui pose problème et vérifie tes variables à ce moment-là et si tu veux qu'on t'aide, donne-nous le code réel, car sans cela, c'est la purée de pois... ;)

Le code total est vraiment vraiment long :(

Je ne t’ai effectivement pas tout mis, et pas non plus les fins de boucles. C’était juste pour te donner une idée du type de données que je traitais ...

Idem pour num_colonne_ano que j’avais écourté pour rendre mon post initial plus lisible.
Mais au niveau de mon point d’arrêt,
L vaut 2 puis 3 puis ... puis 8
Num_colonne_ano vaut 13
Et feuille.name me donne “Racines”, nom de mon onglet.
31/05/2019, 20h30
Pierre Fauconnier

Ok. Jusqu'ici, nous sommes donc dans l'impasse. Je vais te concocter un modèle sur base de la structure dont je t'ai parlé pour lundi matin. Bon WE... ;)
31/05/2019, 21h06
juberrebi

Merci beaucoup, à lundi et bon week-end 😊

1 pièce(s) jointe(s)

Salut.

Voici un fichier Excel avec deux feuilles, une pour les factures et une pour les clients. La table des factures utilise une liste déroulante nourrie par la table des clients. C'est un mécanisme habituel d'Excel pour utiliser une liste de validation.

Dans ton Access, tu vas ouvrir un nouveau fichier sur base du modèle pour transférer les données dans une structure préexistante
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Sub TransferCustomers() Dim rs As DAO.Recordset Dim Target As Range Dim wb As Workbook Set wb = Excel.Workbooks.Add(CurrentProject.Path & "\analyse.xltx") Set rs = CurrentDb.OpenRecordset("select customername from customer order by customername") If Not rs.EOF Then Set Target = wb.Worksheets("Clients").Range("a2") Target.CopyFromRecordset rs End If Set rs = Nothing wb.Close True, CurrentProject.Path & "\analyse_" & Format(Date, "yyyymmdd") & ".xlsx" End Sub
Après le transfert, ton nouveau fichier Excel créé sur base du xltx contient les clients, et la liste déroulante dans les factures est fonctionnelle.

PS: Il faut que les fichiers Access et Excel soient dans le même dossier. Normalement, il faut architecturer ton code différemment. Ici, j'ai juste créé le code pour illustrer comment on peut interagir entre Access et Excel sur base d'un xltx (modèle Excel) construit et préparé. On injecte alors uniquement les données voulues dans le fichier et il est prêt à jouer son rôle avec les bonnes données.

Citation:

Envoyé par Pierre Fauconnier

Ok. Jusqu'ici, nous sommes donc dans l'impasse. Je vais te concocter un modèle sur base de la structure dont je t'ai parlé pour lundi matin. Bon WE... ;)

Bonjour et bonne semaine à tous :)

Voici comme promis mon code en entier :)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Private Sub buttonExportResult_Click()
 
 
strNomFichierResultat = "Adresse sur le serveur"
 
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Onglet_1", strNomFichierResultat, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Onglet_2", strNomFichierResultat, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Onglet_3", strNomFichierResultat, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Onglet_4", strNomFichierResultat, True
 
'On va effectuer des modifications sur le fichier créer pour une meilleure lisibilité:
 
Set wb = CreateObject("Excel.Application").Workbooks.Open(strNomFichierResultat & ".XLSB")
 
Call design_fichier(wb)
 
'à l'issue de la manip on revient au premier onglet
wb.worksheets("Onglet_1").Activate
wb.Close True
 
End Sub

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
Sub design_fichier(wb)
 
For Each feuille In wb.worksheets
 
    Select Case (feuille.Name)
 
    Case "Onglet_1"
        Call Comp_1(wb, feuille)
 
    Case "Onglet_2"
        Call Comp_2(wb, feuille)
 
    Case "Onglet_3"
        Call Comp_3(wb, feuille)
 
    Case "Onglet_4"
        Call Comp_4(wb, feuille)
 
    End Select
 
Next feuille
End Sub

Code:

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
 
'Je ne que l'onglet 1 car strictement indentique pour les autres onglets, avec des noms de colonnes différents.
Sub Comp_1(wb, feuille)
 
For Each c In feuille.range("A1:AN1")
 
    Select Case (c)
'Je vais parcourir chaque colonne pour en tirer les numéros qui m'intéressent
'Puis je ne parcourerai q'une seule fois mes lignes en leur appliquant les modifications dans chaque colonne.
'Cela me semble plus économique que de parcourir les lignes pour chaque colonne. 
 
    Case "Colonne_1"
    c.ColumnWidth = 10
 
    Case "Colonne_2"
    c.ColumnWidth = 12
 
    Case "Colonne_3"
    c.ColumnWidth = 12
 
    Case "Colonne_4_1"
    num_colonne_4_1 = c.Column
 
    Case "Colonne 5_1"
    num_colonne_5_1 = c.Column
 
    Case "Colonne 6_1"
    num_colonne_6_1 = c.Column
 
    Case "Colonne 7_1"
    num_colonne_7_1 = c.Column
 
    Case "Colonne 4_2"
    num_colonne_4_2 = c.Column
 
    Case "Colonne 5_2"
    num_colonne_5_2 = c.Column
 
    Case "Colonne 6_2"
    num_colonne_6_2 = c.Column
 
    Case "Colonne 7_2"
    num_colonne_7_2 = c.Column
 
'Les colonnes Modif, contenant Oui ou Non, indiquent si il y a une anomalie (i.e. une différence entrei_1 et i_2). 
'Leur affichage ne nous intéresse pas. On tire donc l'information et on les cache.
    Case "Modif colonne 4"
    num_colonne_modif4 = c.Column
    c.Entirecolumn.Hidden = True
 
    Case "Modif colonne 5"
    num_colonne_modif5 = c.Column
    c.Entirecolumn.Hidden = True
 
    Case "Modif colonne 6"
    num_colonne_modif6 = c.Column
    c.Entirecolumn.Hidden = True
 
    Case "Modif colonne 7"
    num_colonne_modif7 = c.Column
    c.Entirecolumn.Hidden = True
 
    Case ""
        num_colonne_finale = c.Column - 1
        c.ColumnWidth = 15
        feuille.Cells(c.Row, c.Column) = "Traité par :"
        feuille.Cells(c.Row, c.Column + 1) = "Vraie anomalie ?"
        feuille.Cells(c.Row, c.Column + 2) = "Remarques"
        num_colonne_ano = c.Column + 1
        wb.worksheets(feuille.Name).columns(num_colonne_ano).ColumnWidth = 15
        wb.worksheets(feuille.Name).columns(c.Column + 2).ColumnWidth = 40
        Exit For
 
    End Select
 
Next c
 
'Une fois tous nos numéros de colonnes récupérés, on va appliquer les modifications lignes par ligne.
'On  modifie la couleur des colonnes selon qu'elles sont des colonnes i_1 ou i_2
'On ajoute également des couleurs lors d'anomalies i.e. lorsque i_1 différent de i_2
 
'On traite la première ligne de noms de colonnes à part car affectée uniquement par les deux couleurs.
feuille.range(feuille.Cells(1, num_colonne_4_1), feuille.Cells(1, num_colonne_7_1)).Interior.Color = RGB(242, 220, 219)
feuille.range(feuille.Cells(1, num_colonne_4_2), feuille.Cells(1, num_colonne_finale)).Interior.Color = RGB(220, 230, 241)
feuille.range(feuille.Cells(1, 1), feuille.Cells(1, num_colonne_finale + 3)).Font.Bold = True
 
'On s'attaque aux autres lignes
L = 2
    While feuille.Cells(L, 1).Value <> 0
        'Modification de couleur de fond
        feuille.range(feuille.Cells(L, num_colonne_4_1), feuille.Cells(L, num_colonne_7_1)).Interior.Color = RGB(242, 220, 219)
        feuille.range(feuille.Cells(L, num_colonne_4_2), feuille.Cells(L, num_colonne_finale)).Interior.Color = RGB(220, 230, 241)
 
        'Ajout de la case OUI/NON pour la colonne de "Vraie anomalie ?", où on ne peut répondre
        'LIEU DU PROBLEME
        With feuille.Cells(L, num_colonne_ano).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Oui,Non"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
 
        'Modification de colonne 4
        If feuille.Cells(L, num_colonne_modif4).Value = "Oui" Then
            feuille.Cells(L, num_colonne_4_1).Interior.Color = RGB(230, 184, 183)
            feuille.Cells(L, num_colonne_4_2).Interior.Color = RGB(184, 204, 228)
        End If
 
 
        'Modification de colonne 5 
        If feuille.Cells(L, num_colonne_modif5).Value = "Oui" Then
            feuille.Cells(L, num_colonne_5_1).Interior.Color = RGB(230, 184, 183)
            feuille.Cells(L, num_colonne_5_2).Interior.Color = RGB(184, 204, 228)
        End If
 
        'Modification de colonne 6
        If feuille.Cells(L, num_colonne_modif6).Value = "Oui" Then
            feuille.Cells(L, num_colonne_6_1).Interior.Color = RGB(230, 184, 183)
            feuille.Cells(L, num_colonne_6_2).Interior.Color = RGB(184, 204, 228)
        End If
 
        'Modification de colonne 7 
        If feuille.Cells(L, num_colonne_modif7).Value = "Oui" Then
            feuille.Cells(L, num_colonne_7_1).Interior.Color = RGB(230, 184, 183)
            feuille.Cells(L, num_colonne_7_2).Interior.Color = RGB(184, 204, 228)
        End If
    L = L + 1
    Wend
 
'On remonte vers la case A1 pour un plus bel affichage
wb.worksheets(feuille.Name).Activate
wb.worksheets(feuille.Name).range("A1").Select
End Sub

Citation:
Envoyé par Pierre Fauconnier
Salut.

Voici un fichier Excel avec deux feuilles, une pour les factures et une pour les clients. La table des factures utilise une liste déroulante nourrie par la table des clients. C'est un mécanisme habituel d'Excel pour utiliser une liste de validation.

Dans ton Access, tu vas ouvrir un nouveau fichier sur base du modèle pour transférer les données dans une structure préexistante
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Sub TransferCustomers() Dim rs As DAO.Recordset Dim Target As Range Dim wb As Workbook Set wb = Excel.Workbooks.Add(CurrentProject.Path & "\analyse.xltx") Set rs = CurrentDb.OpenRecordset("select customername from customer order by customername") If Not rs.EOF Then Set Target = wb.Worksheets("Clients").Range("a2") Target.CopyFromRecordset rs End If Set rs = Nothing wb.Close True, CurrentProject.Path & "\analyse_" & Format(Date, "yyyymmdd") & ".xlsx" End Sub
Après le transfert, ton nouveau fichier Excel créé sur base du xltx contient les clients, et la liste déroulante dans les factures est fonctionnelle.

PS: Il faut que les fichiers Access et Excel soient dans le même dossier. Normalement, il faut architecturer ton code différemment. Ici, j'ai juste créé le code pour illustrer comment on peut interagir entre Access et Excel sur base d'un xltx (modèle Excel) construit et préparé. On injecte alors uniquement les données voulues dans le fichier et il est prêt à jouer son rôle avec les bonnes données.
Merci beaucoup pour ces docs, je suis en train de reflechir à tout ça pour l'adapter à mon cas :)

Mais si je comprends bien, je ne peux pas utiliser mon : DoCmd.TransferSpreadsheet acExport ?
Parce que je souhaiterais exporter des tables entières plutôt que des colonnes.
Est-ce que si je ne change que le path de mon fichier pour l'enregistrer dans mon modèle plutôt que dans un nouveau excel, cela suffirait ?

Merci et bonne journée :)

03/06/2019, 09h47
Pierre Fauconnier

Si tu peux importer des tables entières. Normalement, tu exportes au travers non pas d'une table, mais d'une requête qui va préparer tes données pour leur intégration dans la table Excel (bon ordre des colonnes, notamment).

Le CopyFromRecordset accepte sans soucis le multicolonnes, il faut simplement que ta table Excel ait la même structure que ce que ta requête Access lui envoie. Tu peux aussi d'abord créer ta ligne d'entête soit en bouclant sur tes noms de champs, soit en créant tes propres colonnes. L'important, c'est l'ordre des colonnes de tes requêtes.

Perso, je trouve bien plus simple de pouvoir créer des XLTX avec une fonction générique d'envoi des données basée sur des paramètres que tu lui passes, que de coder à chaque fois ce qui te convient pour coller au besoin du moment. Tu pourrais ainsi uniquement créer des feuilles de paramétrage utilisées par l'outil générique. Ce serait plus productif.
03/06/2019, 10h25
juberrebi

Citation:

Envoyé par Pierre Fauconnier

Si tu peux importer des tables entières. Normalement, tu exportes au travers non pas d'une table, mais d'une requête qui va préparer tes données pour leur intégration dans la table Excel (bon ordre des colonnes, notamment).

Le CopyFromRecordset accepte sans soucis le multicolonnes, il faut simplement que ta table Excel ait la même structure que ce que ta requête Access lui envoie.

Tu ne vois aucune solution vis à vis du code que je t'ai envoyé?

Car ta solution me plaît, mais j'aimerais garder une certaine liberté vis à vis de l'ordre et du nombre de colonnes dans chaque onglet :)

Update : je me demande si le problème ne vient pas du fait que j'utilise des "xl..." dans Access ? Peut-être dois-je ajouter un prefixe pour lui signifier que je travaille avec une fonction excel ?
03/06/2019, 12h21
Pierre Fauconnier
Déboguer une procédure de 140 lignes, c'est un peu une torture. Il y a moyen de faire beaucoup plus court.

Citation:

Envoyé par juberrebi

[...]
Update : je me demande si le problème ne vient pas du fait que j'utilise des "xl..." dans Access ? Peut-être dois-je ajouter un prefixe pour lui signifier que je travaille avec une fonction excel ?

Bingo (enfin, peut-être)!

Tu n'a probablement pas référencé la bibliothèque Excel => donc tes constantes xl... ne sont pas connues, car ce ne sont pas des constantes VBA, mais des constantes issues de la bibliothèque Excel.

Soit tu les remplaces par leurs valeurs,

Code:

.Add Type:=3, AlertStyle:=1, Operator:=1, Formula1:="Oui,Non"

Soit tu les déclares en début de module
Code:

1 2 3 Public Const xlValidateList As Long = 3 Public Const xlValidAlertStop As Long = 1 Public Const xlBetween = 1
Soit tu références ta bibliothèque Excel...

PS: J'ai édité mon message de 9h47.

PS: Normalement, tu aurais dû buguer bien plus tôt sur ce problème. Chacun de tes modules devrait contenir avant la première proc, la première fonction et la première variable globale la ligne Option Explicit pour te forcer à déclarer tes variables et constantes. Dès lors, le VBE se serait arrêté en surlignant tes xl... pour te faire comprendre qu'il ne les connaissait pas. Déclarer ses variables est pour moi une obligation pour du code pro. Je t'invite donc à ajouter cette ligne dans tous tes modules existants, et à prévoir l'avenir en allant dans Outils/Options/Editeur pour cocher Déclaration des variables obligatoire. Ainsi, chaque nouveau module contiendra automatiquement cette ligne.

Voir 40 message(s) de cette discussion en une page