eviter les redondances, améliorer la requete et boite de dialogue

**yoann_deygas** · 26/12/2007, 18h55

Bonjour à tous

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
Sub Recup_donnees_access()
'
' Recup_donnees_access Macro
' Macro permettant de recuperer les donnes de la table tarification
'

    'Declaration
    Dim Db As DAO.Database
    Dim Rs As DAO.Recordset
    Dim strSQL As String
    Dim Indice_parcours As Integer
    Dim Date_parcours As Variant
    Dim Nb_Lignes As Integer
   
    'Nettoyage de la feuille
    Sheets("Données").Select
    Range("A2:AK5000").Select
    Selection.ClearContents
     
    'Requete permettant de récuperer les champs
    Set Db = DAO.OpenDatabase("J:\cr\PoleChiffres\Technique\TecCollec\COMMUN\access\BDD Actuariat Frontale.mdb")
    strSQL = "SELECT [N° ETUDE ACTUARIAT], [N° ETUDE OUTIL DE SAISIE], [SOCIETE], [Nom], [RISQUE(S) COUVERT(S)], [Effectifs non cadre], [Age moy non cadre], [Effectif Cadre], [Age moy cadre], [Effectif ETAM], [Age moy ETAM], [Effectif retraité], [Reçu le], [TECHNICIEN(NE)], [RT ?], [RT Exploitables ?], [Effet des RT], [Revue de contrat], [Info concurence], [Alignement], [Liste des courtiers], [Portefeuille], [Realisé DI], [Réalisé FM], [sans suite] FROM [base de tarification]WHERE [Reçu le] BETWEEN #01/01/2006# AND #31/12/2007#"
    Set Rs = Db.OpenRecordset(strSQL, DAO.dbOpenSnapshot)
    Range("A2").CopyFromRecordset Rs
    Db.Close
    
    'Calcul de différents champs (mois, année, effectif total,..)
    Indice_parcours = 2
    Nb_Lignes = Range("A65536").End(xlUp).Row
    
    Do
        'Calcul du mois et de l'année
        Date_parcours = Cells(Indice_parcours, 13)
        Cells(Indice_parcours, 26) = DatePart("m", Date_parcours)
        Cells(Indice_parcours, 27) = DatePart("yyyy", Date_parcours)
        
        'Calcul de l'effectif total
        Cells(Indice_parcours, 28) = Cells(Indice_parcours, 6) + Cells(Indice_parcours, 8) + Cells(Indice_parcours, 10)
        
        'Un identifiant est attribué à chaque dossier en fonction de l'effectif total
        If Cells(Indice_parcours, 28) <= 300 Then
            Cells(Indice_parcours, 29) = 1
        ElseIf Cells(Indice_parcours, 28) > 1000 Then
            Cells(Indice_parcours, 29) = 3
        Else
            Cells(Indice_parcours, 29) = 2
        End If
           
        Indice_parcours = Indice_parcours + 1
        
    Loop Until Indice_parcours = Nb_Lignes + 1
    
    'Mise à jour des tableaux
    Sheets("Tableau3").Visible = True
    Sheets("Tableau3").Select
    Range("A4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Annee de reception").CurrentPage = "2006"
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Annee de reception").CurrentPage = "2007"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    ActiveWindow.SelectedSheets.Visible = False
    
    Sheets("Tableau2").Visible = True
    Sheets("Tableau2").Select
    Range("A4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Annee de reception").CurrentPage = "2006"
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Annee de reception").CurrentPage = "2007"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    ActiveWindow.SelectedSheets.Visible = False
    
    Sheets("Tableau").Visible = True
    Sheets("Tableau").Select
    Range("A4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Annee de reception").CurrentPage = "2006"
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Annee de reception").CurrentPage = "2007"
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    ActiveWindow.SelectedSheets.Visible = False      
End Sub

Le probleme est le suivant !
ma fin de code est presque identique, seul le nom du tableau change.
est t'il possible d'éviter cette redondance.

De plus, comme vous pouvez le constater je remets également à chaque fois 2006 et 2007 pour être en accord avec la requete.

Est il faisable de mettre 2 entiers en paramètres (year1 et year2), de leur donner une valeur au début du programme et de les utiliser à la fin de mon code ainsi que dans la requete.(mon problème pour la requete est le suivant : comment faire comprendre au programme que je veux between 01/01/ + la valeur de year1 et 31/12/ et la valeur year2 ?

Dernière question concernant la requete : comment m'y prendre si je veux les lignes pour lequel le champ "reçu le" termine par 05 ou 07, toujours en utilisant mes 2 valeurs declarées au debut(year1 et year2)

Est il possible au lancement de la macro de faire afficher une boite de dialogue dans lequel l'utilisateur choisira year 1 et year 2 qui seront utilisées ensuite dans le programme

Je vous remercie par avance pour votre aide

**Singular** · 26/12/2007, 20h55

Je précise que je n'ai pas testé les modifs à ton code mais ça devrait fonctionner.

J'ai pris la liberté de mettre un peu d'ordre dans tes instructions de rafraichissement de PivotTables, en accord avec le principe de programmation qui veut qu'on regroupe des instructions qui portent sur un même objet (et non des instruction qui font une action similaire sur des objets différents).

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
Sub Recup_donnees_access()
'
' Recup_donnees_access Macro
' Macro permettant de recuperer les donnes de la table tarification
'
    'Declaration
    Dim Db As DAO.Database
    Dim Rs As DAO.Recordset
    Dim strSQL As String
    Dim Indice_parcours As Integer
    Dim Date_parcours As Variant
    Dim Nb_Lignes As Integer
    Dim Year1 As String
    Dim Year2 As String
 
    'Nettoyage de la feuille
    Sheets("Données").Select
    Range("A2:AK5000").Select
    Selection.ClearContents
 
    Year1 = CStr(Application.InputBox(Prompt:="Entrez la première année:", Title:="Spcification d'année", Type:=2))
    ' Validation de la première année
    If Not Len(Year1) = 4 Then
        MsgBox "L'année n'est pas bonne."
        Exit Sub
    End If
 
    Year2 = CStr(Application.InputBox(Prompt:="Entrez la deuxième année:", Title:="Spcification d'année", Type:=2))
    ' Validation de la première année
    If Not Len(Year2) = 4 Then
        MsgBox "L'année n'est pas bonne."
        Exit Sub
    End If
 
    'Requete permettant de récuperer les champs
    Set Db = DAO.OpenDatabase("J:\cr\PoleChiffres\Technique\TecCollec\COMMUN\access\BDD Actuariat Frontale.mdb")
    strSQL = "SELECT [N° ETUDE ACTUARIAT], [N° ETUDE OUTIL DE SAISIE], [SOCIETE], [Nom], [RISQUE(S) COUVERT(S)], " & _
                "[Effectifs non cadre], [Age moy non cadre], [Effectif Cadre], [Age moy cadre], [Effectif ETAM], " & _
                "[Age moy ETAM], [Effectif retraité], [Reçu le], [TECHNICIEN(NE)], [RT ?], [RT Exploitables ?], " & _
                "[Effet des RT], [Revue de contrat], [Info concurence], [Alignement],[Liste des courtiers], " & _
                "[Portefeuille], [Realisé DI], [Réalisé FM], [sans suite] FROM [base de tarification] " & _
                "WHERE [Reçu le] BETWEEN #01/01/" & Right(Year1, 2) & "# AND #31/12/" & Right(Year2, 2) & "#"
    Set Rs = Db.OpenRecordset(strSQL, DAO.dbOpenSnapshot)
    Range("A2").CopyFromRecordset Rs
    Db.Close
 
    'Calcul de différents champs (mois, année, effectif total,..)
    Indice_parcours = 2
    Nb_Lignes = Range("A65536").End(xlUp).Row
 
    Do
        'Calcul du mois et de l'année
        Date_parcours = Cells(Indice_parcours, 13)
        Cells(Indice_parcours, 26) = DatePart("m", Date_parcours)
        Cells(Indice_parcours, 27) = DatePart("yyyy", Date_parcours)
 
        'Calcul de l'effectif total
        Cells(Indice_parcours, 28) = Cells(Indice_parcours, 6) + Cells(Indice_parcours, 8) + Cells(Indice_parcours, 10)
 
        'Un identifiant est attribué à chaque dossier en fonction de l'effectif total
        If Cells(Indice_parcours, 28) <= 300 Then
            Cells(Indice_parcours, 29) = 1
        ElseIf Cells(Indice_parcours, 28) > 1000 Then
            Cells(Indice_parcours, 29) = 3
        Else
            Cells(Indice_parcours, 29) = 2
        End If
 
        Indice_parcours = Indice_parcours + 1
 
    Loop Until Indice_parcours = Nb_Lignes + 1
 
    'Mise à jour des tableaux
    Sheets("Tableau3").Visible = True
    Sheets("Tableau3").Select
    Range("A4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Annee de reception").CurrentPage = Year1
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Annee de reception").CurrentPage = Year2
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    ActiveWindow.SelectedSheets.Visible = False
 
    Sheets("Tableau2").Visible = True
    Sheets("Tableau2").Select
    Range("A4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Annee de reception").CurrentPage = Year1
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Annee de reception").CurrentPage = Year2
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    ActiveWindow.SelectedSheets.Visible = False
 
    Sheets("Tableau").Visible = True
    Sheets("Tableau").Select
    Range("A4").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotFields("Annee de reception").CurrentPage = Year1
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("Annee de reception").CurrentPage = Year2
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    ActiveWindow.SelectedSheets.Visible = False
End Sub

De plus, la sélection Range("A4").Select est superflue, à mon avis...

**Qwazerty** · 26/12/2007, 22h38

SAlut
Pour ton probleme de redondance tu peux utiliser ca

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Test()
Dim StrTest As String
Dim StrTmp As Variant
 
For Each StrTmp In Array("Tableau3", "Tableau2", "Tableau")
    With Sheets(StrTmp)
        .PivotTables("Tableau croisé dynamique1").PivotFields("Annee de reception").CurrentPage = Year1
        .PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
        .PivotTables("Tableau croisé dynamique2").PivotFields("Annee de reception").CurrentPage = Year2
        .PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
    EndWith
Next
 
End Sub

Tu auras juste a le modifier un peu pour le faire coller a ton besoin, je me suis parmis de virer les "accés directes" au onglet qui me semble supperflu, mais n'ayant jamais geré de tableau dynamiqque avec des macros, je ne peux que supposer que ca ne pose pas de probleme particulier.
Si toutefois c'etait le cas, il te suffis de reprendre ton ancien code et d'utiliser la boucle For Each de la meme maniere que dans mon code precedent.
A++
Qwaz

eviter les redondances, améliorer la requete et boite de dialogue

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager