Requêtes à partir d'une liste d'Excel comme paramètres pour MS query

Le 07/11/2018 à 13:49

Bonjour,

Je souhaiterais à partir d'une liste de n° d'affaire (mise en colonne A d'excel) me donner un délai client pour chaque n° affaire. Pour ce faire j'utilise MS Query.

Le soucis c'est que pour une valeur "brute" (ex un numéro d'affaire = 102240) cela marche mais pas avec une liste > Problème au niveau du code SQL (WHERE AFFAIRE.NAF = 102240)

Je ne peux pas faire une requête avec tous les numéros d'affaire sinon cela serait trop long.

En gros je voudrais que MS Query me retourne :

  • en colonne C : le numéros d'affaire (retrouvé dans la colonne A)
  • en colonne D : Le délai client

Voici mon fichier avec des indications : dans l'onglet feuille 3 vous avez une requete SQL https://www.cjoint.com/c/HKhickTrmhi

Sinon ma database est sécurisé sur le réseau du travail

Je pense qu'il faut que ca passe par une macro

Pourriez vous m'aider ?

Cordialement,

28test-delai-client.xlsm (22.28 Ko)
Le 07/11/2018 à 14:23

Bonjour,

essaie ceci

Sub Macro2()
'
' Macro2 Macro
'
'

    With Sheets("feuil1")
        dl = .Cells(Rows.Count, 1).End(xlUp).Row
        critèresel = "("
        For i = 2 To dl
            critsel = critsel & "'" & .Cells(i, 1) & "'" & ","
        Next i
        critsel = Left(critsel, Len(critsel) - 1) & ")"
    End With
    With ActiveWorkbook.Connections("RequeteClipper").ODBCConnection
        .BackgroundQuery = False
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
        .CommandType = xlCmdSql
        .Connection = Array(Array("ODBC;DSN=CLIPPER 7;ANA=U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd;REP=U:\FICHIERS\AUVER ODBC\;Server Name=;Server Port=;Databa"), Array("se=;UID=;IntegrityCheck=0;PWDXX=;Encryption="))
        .CommandText = Array("SELECT AFFAIRE.NAF, AFFAIRE.DELAI, AFFAIRE.DELAIINIT, AFFAIRE.DATEAF FROM ""U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd""~AFFAIRE AFFAIRE WHERE (AFFAIRE.NAF in " & critsel & ")")
    End With
    If TheVal <> "" Then ActiveWorkbook.Connections("RequeteClipper").Refresh
End Sub
Le 07/11/2018 à 14:54

Bonjour,

Merci de ton soutien.

Alors j'ai lancé la macro que tu m'as faites et cela me met

"erreur d'exécution '9',

L'indice n'appartient pas à la sélection"

quand je met débogage cela me surligne : "With ActiveWorkbook.Connections("RequeteClipper").ODBCConnection"

Le 07/11/2018 à 14:59

J'ai refait une macro pour faire une demande de requete avec une valeur spécifique :

  • Sub Macro1()
    '
    ' Macro1 Macro
    '
    '
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "1024553"
    Range("E2").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
    "ODBC;DSN=CLIPPER 7;ANA=U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd;REP=U:\FICHIERS\AUVER ODBC\;Server Name=;Server Port=;Databa" _
    ), Array("se=;UID=;IntegrityCheck=0;PWDXX=;Encryption=")), Destination:=Range( _
    "$J$2")).QueryTable
    .CommandText = Array( _
    "SELECT AFFAIRE.DELAI, AFFAIRE.NAF" & Chr(13) & "" & Chr(10) & "FROM ""U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd""~AFFAIRE AFFAIRE" & Chr(13) & "" & Chr(10) & "WHERE (AFFAIRE.NAF=1024553)" _
    )
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = _
    "Tableau_Lancer_la_requête_à_partir_de_CLIPPER_7_1"
    .Refresh BackgroundQuery:=False
    End With
    End Sub
[/list]
Le 07/11/2018 à 15:19

Je te met la macro en version code ce sera plus lisible.

En espérant que cela t'aide

Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=CLIPPER 7;ANA=U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd;REP=U:\FICHIERS\AUVER ODBC\;Server Name=;Server Port=;Databa" _
        ), Array("se=;UID=;IntegrityCheck=0;PWDXX=;Encryption=")), Destination:=Range( _
        "$J$3")).QueryTable
        .CommandText = Array( _
        "SELECT AFFAIRE.DELAI, AFFAIRE.NAF" & Chr(13) & "" & Chr(10) & "FROM ""U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd""~AFFAIRE AFFAIRE" & Chr(13) & "" & Chr(10) & "WHERE (AFFAIRE.NAF=1024564)" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_Lancer_la_requête_à_partir_de_CLIPPER_711"
        .Refresh BackgroundQuery:=False
    End With
End Sub
Le 07/11/2018 à 15:47

Je remet le fichier Excel ci dessous avec les macros

28test-delai-client.xlsm (26.27 Ko)
Le 07/11/2018 à 16:16

Bonjour,

une proposition de correction de macro 1 (je n'ai pas la possibilité de tester)

Sub Macro1()
'
' Macro1 Macro
'

'
    With Sheets("feuil1")
        dl = .Cells(Rows.Count, 1).End(xlUp).Row
        critsel = "("
        For i = 1 To dl
            critsel = critsel & "'" & .Cells(i, 1) & "'" & ","
        Next i
        critsel = Left(critsel, Len(critsel) - 1) & ")"
    End With

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=CLIPPER 7;ANA=U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd;REP=U:\FICHIERS\AUVER ODBC\;Server Name=;Server Port=;Databa" _
        ), Array("se=;UID=;IntegrityCheck=0;PWDXX=;Encryption=")), Destination:=Range( _
        "$J$3")).QueryTable
        .CommandText = Array("SELECT AFFAIRE.DELAI, AFFAIRE.NAF FROM ""U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd""~AFFAIRE AFFAIRE WHERE (AFFAIRE.NAF IN " & critsel & ")")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_Lancer_la_requête_à_partir_de_CLIPPER_711"
        .Refresh BackgroundQuery:=False
    End With
End Sub
Le 07/11/2018 à 16:31

Merci beaucoup de ta réactivité

Alors j'ai testé et cela me donne ceci :

"erreur d'exécution '13' :

Incompatibilité de type

Si je met débogage cela me surligne

.CommandText = Array("SELECT AFFAIRE.DELAI, AFFAIRE.NAF FROM ""U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd""~AFFAIRE AFFAIRE WHERE (AFFAIRE.NAF IN " & critsel & ")")

Par contre je remarque qu'il y a ceci en trop : ") ici

(AFFAIRE.NAF IN " & critsel & ")")

si j'enlève ") on a une erreur de syntaxe

Tu peux regarder le nouveau fichier excel que j'ai mis en copie. j'ai remis la macro utilisée pour récupérer les données dans ma base de données

Le 07/11/2018 à 16:38

Bonjour,

une autre essai

Sub Macro1()
'
' Macro1 Macro
'

'
    With Sheets("feuil1")
        dl = .Cells(Rows.Count, 1).End(xlUp).Row
        critsel = "("
        For i = 1 To dl
            critsel = critsel & "'" & .Cells(i, 1) & "'" & ","
        Next i
        critsel = Left(critsel, Len(critsel) - 1) & ")"
        sqlcmd = "SELECT AFFAIRE.DELAI, AFFAIRE.NAF FROM ""U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd""~AFFAIRE AFFAIRE WHERE (AFFAIRE.NAF IN " & critsel & ")"
    End With

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=CLIPPER 7;ANA=U:\FICHIERS\AUVER ODBC\CLIPPER7.WD7\CLIPPER7.wdd;REP=U:\FICHIERS\AUVER ODBC\;Server Name=;Server Port=;Databa" _
        ), Array("se=;UID=;IntegrityCheck=0;PWDXX=;Encryption=")), Destination:=Range( _
        "$J$3")).QueryTable
        .CommandText = sqlcmd
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = _
        "Tableau_Lancer_la_requête_à_partir_de_CLIPPER_711"
        .Refresh BackgroundQuery:=False
    End With
End Sub
Le 07/11/2018 à 16:42

est il possible de :

1) créer un tableau avec pour chaque ligne (nommé"i") un numéros d'affaire récuperer dans la colonne A d'Excel

2) retourner dans un nouveau tableau avec

en colonne 1 : "i"

en colonne 2 : la requête avec "i" comme paramètre

3) incrémenter i +1

4) répéter les opération 1, 2 et 3 avec pour chaque ligne du nouveau tableau une nouvelle valeur de i

Le 07/11/2018 à 16:44

Niquel ton code marche mais il me retourne pas tous les numéros d'affaire

Edit : j'ai rien dit ce sont des doublons

Merci beaucoup c'est génial

petite question : si je veux ajouter d'autres champs en plus du délai, cela marchera ?

Le 07/11/2018 à 17:09

Bonjour,

Oui, cela devrait marcher

Le 07/11/2018 à 17:16

En tout cas merci beaucoup je testerai demain

Tu viens de me sauver

Rechercher des sujets similaires à "requetes partir liste comme parametres query"