Plage de données dans un requête en vba

Question

Bonjour,  

Je voudrais savoir si il est possible d'affecter une plage de données à une variable pour une requête: 

Ma plage DK contient une liste d'utilisateurs sous forme de nombres. 
Code: 

Dim ajout as string 
Dim CORT as string 

CORT=Sheets("user").select &_ 
Range("J4:J16").select 

Citation: 
'REQUETE: 

Code: 
.sql=Select...From...Where... 

And .... 

'Condition pour la sélection dans un combobox: 
'Sélection de l'équipe cortal à laquelle est affectée la liste d'utilisateurs 

If Combobox9.text<>" " then 

       Select case ajout 

Case "' & Cortal & '",ajout=And id_user in DK 
Case .... 

       End select 
End if 

.sql=.sql & ajout 

 Merci pour votre aide:) 
Tahbre

cedb002 · Answer

Met une boucle if pour tester la valeur de ton indice

tahbre · Answer

Je vais essayer de détailler parce que là je suis bloqué complet....:(

Ma plage DK est en fait une équipe avec plusieurs utilisateurs qui 
ont chacun un identifiant:456,564,32,... 

Tous ces identifiants sont sur ma page user. 

Je voudrais au préalable stocker ma plage de données dans ma variable "DK".(je ne sais pas si cela est possible) 

J'ai un combobox avec le nom de différentes équipes (une équipe didier/karim, une équipe Cortal, et une équipe FAL), et chacune a sa propre liste d'utilisateurs donc sa propre plage de données. 

Donc j'ai ma requête qui commence par select...from...where 
qui est stockée dans ma variable ".sql" et je veux rajouter un if pour le filtre par équipe pour compléter ma requête. 

Ma variable "ajout" stockera tout le résultat de mon if, et l'ajoutera à mon .sql: 
->.sql=.sql & ajout 

Voilà si vous avez une idée je vous remercie d'avance!! 

Salut 
Tahbre

cedb002 · Answer

tu as essayer de lié tes 2 tables?

tahbre · Answer

Bon là ça devient chaotique, j'ai modifié mes variables, et mis mes valeurs, mais je veux rajouter une condition à ma requête, mais la façon dont j'ai posé mon Case ne fonctionne pas. Ma requête me donne un nombre à une date données pour un ensemble, cet ensemble est divisé en trois équipes(combo9) ou en orientations(combo15)...Ecoute c'est pas un drame, je vais voir ça ce week end, ya pas de soucis.Salut! Code: Private Sub CommandButton20_Click() Dim da As String Dim datejour As String Dim DK, FAL, CORT, AF, AE, AI, OTE, OTI, MON, MONEU, DIV, OGAP, FCPR, FCIMT As String FAL = 552 & "," & 613 & "," & 790 CORT = 162 & "," & 199 & "," & 279 & "," & 280 & "," & 307 & "," & 395 & "," & 408 & "," & 479 & "," & 492 & "," & 496 & "," & 540 & "," & 553 & "," & 714 & "," & 723 & "," & 727 & "," & 760 & "," & 799 & "," & 816 & "," & 827 & "," & 1815 & "," & 1816 DK = 32 & "," & 181 & "," & 491 & "," & 497 & "," & 500 & "," & 550 & "," & 554 & "," & 715 & "," & 726 & "," & 728 & "," & 765 & "," & 815 & "," & 826 & "," & 1826 & "," & 1832 AF = 540 & "," & 816 & "," & 827 & "," & 408 & "," & 760 & "," & 552 AE = 790 & "," & 540 & "," & 479 & "," & 408 & "," & 496 & "," & 816 & "," & 613 & "," & 181 & "," & 1832 & "," & 554 AI = 280 & "," & 540 & "," & 491 & "," & 500 & "," & 492 & "," & 550 & "," & 496 & "," & 826 & "," & 1816 & "," & 307 & "," & 728 & "," & 727 & "," & 479 & "," & 408 & "," & 816 & "," & 726 & "," & 395 & "," & 760 & "," & 553 OTE = 1815 & "," & 552 & "," & 1826 & "," & 500 & "," & 408 & "," & 496 & "," & 816 & "," & 613 & "," & 181 & "," & 1832 & "," & 554 OTI = 553 & "," & 395 & "," & 307 & "," & 32 & "," & 726 & "," & 799 & "," & 540 MON = 540 MONEU = 491 & "," & 760 & "," & 552 & "," & 613 DIV = 790 & "," & 181 & "," & 816 & "," & 727 & "," & 162 & "," & 497 & "," & 723 & "," & 552 & "," & 715 & "," & 1832 & "," & 826 & "," & 554 & "," & 714 & "," & 726 & "," & 613 & "," & 307 & "," & 553 & "," & 1815 & "," & 1816 & "," & 492 & "," & 827 & "," & 728 & "," & 1826 OGAP = 554 & "," & 815 & "," & 790 & "," & 552 & "," & 765 & "," & 613 FCPR = 554 & "," & 723 & "," & 162 FCIMT = 395 & "," & 760 datejour = Format(Year(Date), "0000") & "-" & Format(Month(Date), "00") & "-" & Format(Day(Date), "00") da = Format(DateAdd("d", -1, datejour), "yyyy-mm-dd") Sheets("test").Select With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=Faccir Recette;SRVR=CHR1_PAR_TST_SQL;DB=IMVT_CHORUS;UID=EHNR;PWD=EHNR00" _ , Destination:=Range("G2")) .Sql = "SELECT count (imvt_fond_controle.contiin_ci) " & _ "FROM IMVT_CHORUS.dbo.imvt_fond_controle imvt_fond_controle " & _ "WHERE (imvt_fond_controle.contiin_ci=25) " & _ "AND (imvt_fond_controle.fond_dt_entree_base = '" & da & "')" If Pilotage.ComboBox9.Text <> "" Then Select Case Pilotage.ComboBox9.Text Case "' & Equipe Didier/Karim & '" .Sql = .Sql & "And imvt_fond_controle.id_user in DK" Case "' & Equipe Françoise/Armando/Luis & '" .Sql = .Sql & "And imvt_fond_controle.id_user in FAL" Case "' & Cortal & '" .Sql = .Sql & "And imvt_fond_controle.id_user in CORT" End Select End If If Pilotage.ComboBox15.Text <> "" Then Select Case Pilotage.ComboBox15 Case " ' ACTIONS FRANCAISES '" .Sql = .Sql & "and imvt_fond_controle.id_user in AF" Case " ' ACTIONS INTERNATIONALES '" .Sql = .Sql & "and imvt_fond_controle.id_user in AI" Case " ' ACTIONS EURO '" .Sql = .Sql & "and imvt_fond_controle.id_user in AE" Case " ' OBLIG ET TCN EURO '" .Sql = .Sql & "and imvt_fond_controle.id_user in OTE" Case " ' OBLIG ET TCN INTERNATIONAUX '" .Sql = .Sql & "and imvt_fond_controle.id_user in OTI" Case " ' MONETAIRES EURO '" .Sql = .Sql & "and imvt_fond_controle.id_user in MONEU" Case " ' MONETAIRES '" .Sql = .Sql & "and imvt_fond_controle.id_user in MON" Case " ' DIVERSIFIES '" .Sql = .Sql & "and imvt_fond_controle.id_user in DIV" Case " ' OPCVM GARANTIS AVEC PROTECTION '" .Sql = .Sql & "and imvt_fond_controle.id_user in OGAP" Case " ' FCPR '" .Sql = .Sql & "and imvt_fond_controle.id_user in FCPR" Case " ' FCIMT '" .Sql = .Sql & "and imvt_fond_controle.id_user in FCIMT" End Select End If MsgBox .Sql .FieldNames = True .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .FillAdjacentFormulas = False .RefreshOnFileOpen = False .HasAutoFormat = True .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SavePassword = False .SaveData = True End With End Sub

Plage de données dans un requête en vba

4 réponses

Discussions similaires