Problème requête sql dans vba

**Alex70** · 20/08/2013, 18h44

Bonsoir,

Je ne trouve pas la réponse à ma question de façon précise... alors je poste ici ^^

Lorsque je fais une requête via le Microsoft Query ma requête fonctionne sans souci ^^

Du coup je voulais faire un peu brute façon :

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
 
Sub Macro3()
 
Range("A2").Select
 
Sheets.Add After:=Sheets(Sheets.Count)
 
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=mantis;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT (select T1.value from mantis_custom_field_string_table T1 where t_bug.id = T1.bug_id and field_id = 7), t_custf.value, (select case when SUBSTRING(T2.value,2,2) = 'P0' then 'P0' when SUBSTRING(T2.value,2,2)='P1' then 'P1' when SUBSTRING(T2.value,2,2) = 'P2' then 'P2'else 'NC' end from  mantis_custom_field_string_table T2 where t_bug.id = T2.bug_id and field_id = 4, count(t_bug.id)" _
, _
"FROM mantis_bug_table t_bug inner join mantis_project_table t_proj on t_bug.project_id=t_proj.id left outer join mantis_custom_field_string_table t_custf on t_bug.id=t_custf.bug_id and t_custf.field_id='6' left outer join mantis_user_table t_user on t_bug.reporter_id=t_user.id inner join mantis_category_table t_cat on t_bug.category_id=t_cat.id      left outer join mantis_bug_history_table t_hist on t_bug.id = t_hist.bug_id and (t_hist.new_value='90' and t_hist.field_name = 'status')" _
, _
"WHERE t_proj.name = 'Mag21_Fly' AND ((t_bug.status <> '90' ANd ((year (date_format(from_unixtime (date_submitted),get_format(date,'iso')))= '2013' AND  week (date_format(from_unixtime (date_submitted),get_format(date,'iso')),3) <= '2013') OR year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) < '2013'))  OR" _
, _
"((t_bug.status = '90' AND year (date_format(from_unixtime (t_hist.date_modified),get_format(date,'iso'))) = '2013' AND week (date_format(from_unixtime (t_hist.date_modified),get_format(date,'iso')),3) > '33')  AND ( (year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) = '33'        AND         week (date_format(from_unixtime (date_submitted),get_format(date,'iso')),3) <= '33'        )       OR        year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) < '2013' )  )) " _
, _
"group by  (select T1.value from mantis_custom_field_string_table T1 where t_bug.id = T1.bug_id and field_id = 7) ,t_custf.value , (select  case when SUBSTRING(T2.value,2,2) = 'P0' then 'P0' when SUBSTRING(T2.value,2,2) = 'P1' then 'P1' when SUBSTRING(T2.value,2,2) = 'P2' then 'P2' else 'NC' end from  mantis_custom_field_string_table T2 where t_bug.id = T2.bug_id and field_id = 4)" _
)
.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

Mais j'ai une erreur
Erreur D'exécution '13': Incompatibilité de type

Erreur dans mon code ou limite de la taille de la requête dans vba ?

Là je veux bien un avis extérieur svp

Edit : oui après visite du web c'est parlant... mais du coup comment faire ? Oô

**Alex70** · 21/08/2013, 11h48

Bon ben en cherchant un peu j'ai résolu une partie de mon problème

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
 
Sub Macro3()
 
Dim AnneeEC As Integer
Dim SemaineEC As Integer
Dim shtoto As Worksheet
Dim SemTester As Integer
Dim SemDeb As Integer
 
 
'récuperation de la semaine en cours
datedujour = Date
SemaineEC = CInt(Format(datedujour, "ww", 2))
AnneeEC = CInt(Format(datedujour, "yyyy", 2))
 
SemDeb = SemaineEC - 2
 
 
For I = SemDeb To SemaineEC
 
 
Set shtoto = Sheets.Add(After:=Sheets(Sheets.Count))
 
shtoto.Name = "S" & I
 
Range("A2").Select
 
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=mantis;", Destination:=Range("$A$1")).QueryTable
.CommandText = "SELECT (select T1.value from mantis_custom_field_string_table T1 where t_bug.id = T1.bug_id and field_id = 7), t_custf.value, (select case when SUBSTRING(T2.value,2,2) = 'P0' then 'P0' when SUBSTRING(T2.value,2,2)='P1' then 'P1' when SUBSTRING(T2.value,2,2) = 'P2' then 'P2'else 'NC' end from  mantis_custom_field_string_table T2 where t_bug.id = T2.bug_id and field_id = 4), count(t_bug.id) " & _
"FROM mantis_bug_table t_bug inner join mantis_project_table t_proj on t_bug.project_id=t_proj.id left outer join mantis_custom_field_string_table t_custf on t_bug.id=t_custf.bug_id and t_custf.field_id='6' left outer join mantis_user_table t_user on t_bug.reporter_id=t_user.id inner join mantis_category_table t_cat on t_bug.category_id=t_cat.id      left outer join mantis_bug_history_table t_hist on t_bug.id = t_hist.bug_id and (t_hist.new_value='90' and t_hist.field_name = 'status') WHERE t_proj.name = 'Mag21_Fly' AND ((t_bug.status <> '90' ANd ((year (date_format(from_unixtime (date_submitted),get_format(date,'iso')))= '" & AnneeEC & "' AND  week (date_format(from_unixtime (date_submitted),get_format(date,'iso')),3) <= '" & AnneeEC & "') OR year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) < '" & AnneeEC & "'))  OR " & _
"((t_bug.status = '90' AND year (date_format(from_unixtime (t_hist.date_modified),get_format(date,'iso'))) = '" & AnneeEC & "' AND week (date_format(from_unixtime (t_hist.date_modified),get_format(date,'iso')),3) > '" & I & "')  AND ( (year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) = '" & I & "'        AND         week (date_format(from_unixtime (date_submitted),get_format(date,'iso')),3) <= '" & I & "'        )       OR        year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) < '" & AnneeEC & "' )  )) " & _
"group by  (select T1.value from mantis_custom_field_string_table T1 where t_bug.id = T1.bug_id and field_id = 7) ,t_custf.value , (select  case when SUBSTRING(T2.value,2,2) = 'P0' then 'P0' when SUBSTRING(T2.value,2,2) = 'P1' then 'P1' when SUBSTRING(T2.value,2,2) = 'P2' then 'P2' else 'NC' end from  mantis_custom_field_string_table T2 where t_bug.id = T2.bug_id and field_id = 4)"
 
        .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
 
Set shtoto = Nothing
 
Next
 
End Sub

Il me fait 1 feuille par requête ok
Par contre toutes mes feuilles ont les mêmes chiffres.... Une idée pour corriger le problème ?

Problème requête sql dans vba

Excel

Discussions similaires

Partager

Partager