automatisation de requêtes ODBC

**vinzopus** · 13/03/2008, 16h03

Bonjour,

Je cherche à automatiser une requête ODBC depuis Excel, vers une base de données.
L'idée est la suivante:
- 1ère requête : j'extraie les données correspondant à un certain n°
- je traite ces données dans Excel par des calculs très simples, que je recopie dans une autre feuille
- 2ème requête: données correspondant au n° suivant
- traitement de ces données
...

Mon problème concerne la requête. Plusieurs cas de figures se présentent, selon les programmes que j'ai essayés:
- soit la 1ère requête fonctionne, mais à partir de la 2eme le programme bug à l'expression : .Refresh BackgroundQuery:=False (erreur '1004')
- soit je passe cette expression à True et alors le programme tourne, mais en fait les données ne sont jamais inscrites dans la feuille Excel: il se contente de "Lancer la requête à partir de Base de données WCLIP" et enchaine les étapes suivantes sans qu'il y ait de données dans la feuille...

Voici le code (la Sub principale s'appelle Prog et est en dernier, et la requête est la sub requeteAffCreeFeuille... le reste est annexe)

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
 
Option Explicit
Option Base 1
 
 
'je commence par créer deux tableaux contenant
'les noms des CF et les heures correspondantes
Public tabCF(40) As String, tabH(40) As Double
 
'naff est le numéro de l'affaire pour laquelle on va faire la requete
'ligAff est le n° de la ligne correspondante dans la feuille "données"
'ligCoutsMO est le n° de la ligne correspondant au n° d'affaire dans la feuille "couts MO"
Public naff As Integer, ligAff As Integer, ligCoutsMO As Integer
 
Sub remplirCF()
 
    Dim a As Integer
    For a = 1 To UBound(tabCF)
        tabCF(a) = ""
    Next
 
    tabCF(1) = "n° aff"
 
    'je copie les CF de la feuille "couts" dans le tabCF
    Worksheets("couts").Select
    Dim i As Integer
    For i = 4 To 23
        tabCF(i - 2) = Cells(5, i)
    Next
    tabCF(22) = Cells(5, 3)
    tabCF(23) = Cells(5, 24)
    tabCF(24) = Cells(5, 25)
 
    'je recopie ces valeurs dans la feuille "coutsMO"
    Worksheets("couts MO").Select
    Dim j As Integer
    For j = 1 To 24
        Cells(1, j) = tabCF(j)
    Next j
 
End Sub
 
Sub completerCF()
    Worksheets("couts MO").Select
    Dim p As Integer
    For p = 25 To UBound(tabCF)
        Cells(1, p) = tabCF(p)
    Next p
End Sub
 
Sub requeteAffCreeFeuille()
 
    Sheets.Add
    Range("A1").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT POINT.COFRAIS, POINT.TPSPASSE, POINT.NAF, POINT.DAT" & Chr(13) & "" & Chr(10) & "FROM c:\wclipper\WCLIP.wd5\WCLIP.wdd~POINT POINT" & Chr(13) & "" & Chr(10) & "WHERE (POINT.NAF=" & naff & ")" & Chr(13) & "" & Chr(10) & "ORDER BY POINT.NAF, POINT.COFRAIS" _
        )
        .name = "Lancer la requête à partir de Base de données WCLIP_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=True
    End With
 
End Sub
 
'effectue la somme des heures par CF de la feuille "requete",
'et les enregistre dans le tableau tabH
Sub sommeH()
    Dim m As Integer, ligne As Integer, som As Double, cf As String
 
    'on reinitialise le tableau des heures
    For m = 1 To UBound(tabH)
        tabH(m) = 0
    Next m
 
    tabH(1) = naff
 
    cf = Cells(2, 1).Value
    som = 0
    ligne = 2
 
    If cf = "" Then
        MsgBox "Attention: pas de données dans la feuille requete pour l'affaire" & naff
    End If
 
    'on suppose que les CF sont en colonne 1 et les temps passes en colonne 2
    While cf <> ""
        'on somme les h tant qu'on ne change pas de CF
        While cf = Cells(ligne, 1)
            som = som + Cells(ligne, 2)
            ligne = ligne + 1
        Wend
 
        'on enregistre la valeur de som dans tabH, dans la bonne colonne
        tabH(chercheDansTabCF(cf)) = som
 
        'on réinitialise som et on passe au CF suivant
        som = 0
        cf = Cells(ligne, 1)
    Wend
    'ActiveWindow.SelectedSheets.Delete
 
End Sub
 
Function chercheDansTabCF(CentreFrais As String) As Integer
    Dim n As Integer
    n = 1
 
    While n < UBound(tabCF)
        If tabCF(n) = CentreFrais Then
            chercheDansTabCF = n
            Exit Function
        End If
        n = n + 1
    Wend
 
    'si cf n'est pas dans tabCF on l'ajoute
    n = 25
    While n < UBound(tabCF)
        If tabCF(n) = "" Then
            tabCF(n) = CentreFrais
            chercheDansTabCF = n
            Exit Function
        End If
        n = n + 1
    Wend
    MsgBox "Pb dans la fonction chercheDansTabCF"
 
End Function
 
Sub remplircoutsMO()
    Worksheets("couts MO").Select
    Dim o As Integer
    For o = 1 To UBound(tabH)
        Cells(ligCoutsMO, o) = tabH(o)
    Next o
End Sub
 
 
'la sub renvoie le n° d'affaire suivant
'et la dernière ligne qui lui correspond dans la feuille "données"
 
Sub prochaineAff()
    Worksheets("données").Select
 
    'si le tableau ne contient pas de données, un message s'affiche
    If naff = 0 Then
        MsgBox "Il n'y pas d'affaires dans la feuille données"
        Exit Sub
    End If
 
    'on cherche le n° de la dernière ligne contenant le n° d'affaire
    While Cells(ligAff + 1, 1) = naff
        ligAff = ligAff + 1
    Wend
 
    'je sélectionne la 1ère ligne du groupe qui contient le n° d'affaire suivant
    ligAff = ligAff + 1
    naff = Cells(ligAff, 1)
 
End Sub
 
Sub Prog()
 
    'je commence par remplir la feuille "couts MO" avec les bons noms de CF
    remplirCF
 
    'j'initialise mes variables naff et ligAff
    naff = Worksheets("données").Cells(2, 1)
    ligAff = 2
    ligCoutsMO = 2
 
    While naff <> 0
        'j'effectue la requete avec le n° d'affaire
        requeteAffCreeFeuille
        'requeteMultiple
        'reqVinz
        'j'effecute la somme des heures par CF et remplie tabH
        sommeH
        'je recopie tabH dans la feuille "couts MO"
        remplircoutsMO
        'je sélectionne le prochain n° d'affaire
        prochaineAff
        ligCoutsMO = ligCoutsMO + 1
    Wend
 
    'j'ajoute les CF "bizarres"
    completerCF
 
End Sub

Pour le moment j'ai essayé les solutions suivantes:
- créer une nouvelle feuille à chaque fois (cf ci dessus requeteAffCreeFeuille)
- lancer la requête à partir de la même feuille à chaque fois (cf ci-dessous en activant reqMultiple au lieu de requeteAffCreeFeuille)
- créer un nouveau classeur à chaque fois et l'enregistrer sous un nom différent à chaque n° (je vous fais grâce du 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
 
Sub requeteMultiple()
'
' requeteMultiple Macro
' Macro enregistrée le 12/03/2008 par SCAFFHOLDING
'
 
    Worksheets("requete").Select
    Cells.Select
    Selection.ClearContents
    'Selection.QueryTable.Delete
    Range("A1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT POINT.COFRAIS, POINT.TPSPASSE" & Chr(13) & "" & Chr(10) & "FROM c:\wclipper\WCLIP.wd5\WCLIP.wdd~POINT POINT" & Chr(13) & "" & Chr(10) & "WHERE (POINT.NAF=" & naff & ")" & Chr(13) & "" & Chr(10) & "ORDER BY POINT.COFRAIS" _
        )
        .name = "Lancer la requête à partir de Base de données WCLIP_17"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

A chaque fois le pb revient!

Ce n'est pas un pb de temps, puisque même si je fais "patienter" Excel pendant que la requete est lancée, en lui faisant faire des opérations dans le vide, celle-ci ne s'achève jamais ... (dans le cas .Refresh BackgroundQuery:=True)

Je commence à un peu désespérer...
Si quelqu'un a une idée, je suis vraiment preneur!!
merci d'avance

Vincent

**tirex28** · 14/03/2008, 00h50

Bonsoir,

Je pense que tu dois utiliser une requete parametrée et modifier la valeur de ce parametre dans une boucle sans recréer ta requete à chaque fois:

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
Sub EssaiRequeteParametree()
    Dim QT As QueryTable, i As Long, NAFS
 
    Sheets.Add
    NAFS = Array(1, 2, 3)
 
    Set QT = ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
        , Destination:=Range("A1"))
 
    With QT
        .Parameters.Add "NAF"
        'Le parametre NAF est inclus dans la requete (le ? de la clause Where)
        .CommandText = _
            "SELECT POINT.COFRAIS, POINT.TPSPASSE, POINT.NAF, POINT.DAT" & Chr(13) & "" & Chr(10) & _
            "FROM c:\wclipper\WCLIP.wd5\WCLIP.wdd~POINT POINT" & Chr(13) & "" & Chr(10) & _
            "WHERE (POINT.NAF=?)" & Chr(13) & "" & Chr(10) & _
            "ORDER BY POINT.NAF, POINT.COFRAIS"
        For i = 0 To 2
            'Affecte une valeur au parametre avant le rafraichissement
            .Parameters("NAF").SetParam xlConstant, NAFS(i)
            .Refresh False
            'Traitement sur le resultat de la requete
            Debug.Print .ResultRange.Rows.Count
        Next
    End With
End Sub

A adapter bien sur.

Cordialement,

Tirex28/

**vinzopus** · 14/03/2008, 10h28

Bonjour Tirex28,

Tout d'abord merci beaucoup pour ton aide!
Hélas la requête que tu proposes ne fonctionne pas lorsque je l'exécute:
erreur générale ODBC '1004'
et indique que l'endroit où intervient le bug est: .Refresh False

La Sub crée donc la nouvelle feuille mais n'arrive pas à faire ne serait-ce que la première requête...

Je vais tt de meme essayer de reprendre le principe de ne pas créer une requête à chaque fois et de l'adapter à mon cas. Si tu as une idée de pourquoi ta requête ne fonctionne pas chez moi, ce serait top!!

Merci encore

Vincent

**Jean-Pierre49** · 14/03/2008, 11h23

Bonjour a tous,

Je fais pas mal d'outile extraction sur Clipper et je n'ai jamais eu de soucis pour effectuer des requetes multiples.

En tous cas je ne pense pas que cela ne vienne de ta requête regarde plutot ds le reste de ton code

pour obtimiser la requête:

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
Sub requeteMultiple()
'
' requeteMultiple Macro
' Macro enregistrée le 12/03/2008 par SCAFFHOLDING
'
 
    Worksheets("requete").Select
    Cells.ClearContents
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT POINT.COFRAIS, POINT.TPSPASSE FROM  POINT WHERE POINT.NAF=" & naff & " ORDER BY POINT.COFRAIS")
        .Refresh BackgroundQuery:=False
    End With
End Sub

Bon courage
Jean Pierre

**vinzopus** · 14/03/2008, 11h30

Alors effectivement c'est assez bizarre...
Lorsque j'exécute la sub suivante, ca marche, les requetes s'enchainent.
En revanche lorsque je lance tout mon code alors ca se bloque à la deuxième requête... pourtant naff a bien la valeur voulue, donc je ne vois pas trop ce qui change!

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
Sub requeteMultiple()
'
' requeteMultiple Macro
' Macro enregistrée le 12/03/2008 par SCAFFHOLDING
'
    naff = 9000
    While naff < 9010
 
        Worksheets("requete").Select
        Cells.Select
        Selection.ClearContents
        'Selection.QueryTable.Delete
        Range("A1").Select
        With ActiveSheet.QueryTables.Add(Connection:= _
            "ODBC;DSN=Base de données WCLIP;;ANA=c:\wclipper\WCLIP.wd5\WCLIP.wdd;;REP=T:\FICHIERS\GALVA-AFA\;" _
            , Destination:=Range("A1"))
            .CommandText = Array( _
            "SELECT POINT.COFRAIS, POINT.TPSPASSE" & Chr(13) & "" & Chr(10) & "FROM c:\wclipper\WCLIP.wd5\WCLIP.wdd~POINT POINT" & Chr(13) & "" & Chr(10) & "WHERE (POINT.NAF=" & naff & ")" & Chr(13) & "" & Chr(10) & "ORDER BY POINT.COFRAIS" _
            )
            .Name = "Lancer la requête à partir de Base de données WCLIP_17"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            MsgBox "ok" & naff
            .Refresh BackgroundQuery:=False
        End With
    naff = naff + 1
    Wend
End Sub

Une idée?
Merci!

**Jean-Pierre49** · 14/03/2008, 11h42

A tout hasard

Recupere POINT.NAF ds ta requête

Code :

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

1
2
3
 
        "SELECT POINT.NAF, POINT.COFRAIS, POINT.TPSPASSE FROM 
End

Jean Pierre

automatisation de requêtes ODBC

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager