MSQUERY et variable

Bonjour à tous!

Je souhaite réaliser une requête en VBA, j'ai utilisé l'enregistreur de macro et tout fonctionne bien!!

Mais il y a un morceau de cette requête ou j'aimerais mettre une variable

Voici le code:

Sub test()

Dim Path_name As String
Dim File_name As String
Dim Complete_File_name As String

nomClasseur = ActiveWorkbook.Name
Workbooks.Open Filename:=Application.GetOpenFilename("Fichiers Excel, *.XLS"), local:=True

File_name = ActiveWorkbook.Name
Path_name = ThisWorkbook.Path
Complete_File_name = Path_name & "\" & File_name
Windows(nomClasseur).Activate

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=Complete_File_name;DefaultDir=./;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination _
        :=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT `DonnéesOK$`.`N° du labo :`, `DonnéesOK$`.`Nom du contrôle :`, `DonnéesOK$`.Analytes, `DonnéesOK$`.Du, `DonnéesOK$`.Au, `DonnéesOK$`.Moyenne, `DonnéesOK$`.CV, `DonnéesOK$`.`Nb de points`" & Chr(13) & "" & Chr(10) & "FROM " _
        , _
        "`C:\Documents and Settings\MPLNET\Bureau\CQ URT Données OK.xls`.`DonnéesOK$` `DonnéesOK$`" & Chr(13) & "" & Chr(10) & "WHERE (`DonnéesOK$`.Analytes='Chlorure Niv 1') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.An" _
        , _
        "alytes='Chlorure Niv 1 U') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.Analytes='Chlorure Niv 2') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.Analytes='Chlorure Niv 2 U" _
        , _
        "') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.Analytes='Potassium Niv 1') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.Analytes='Potassium Niv 1 U') AND (`DonnéesOK$`.`" _
        , _
        "N° du labo :`='530577') OR (`DonnéesOK$`.Analytes='Potassium Niv 2') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.Analytes='Potassium Niv 2 U') AND (`DonnéesOK$`.`N° du labo :`='530577'" _
        , _
        ") OR (`DonnéesOK$`.Analytes='Sodium Niv 1') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.Analytes='Sodium Niv 1 U') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.Analytes=" _
        , _
        "'Sodium Niv 2') AND (`DonnéesOK$`.`N° du labo :`='530577') OR (`DonnéesOK$`.Analytes='Sodium Niv 2 U') AND (`DonnéesOK$`.`N° du labo :`='530577')" & Chr(13) & "" & Chr(10) & "ORDER BY `DonnéesOK$`.Analytes" _
        )
        .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_Excel_Files14"
        .Refresh BackgroundQuery:=False
    End With
End Sub

J'ai bien réussi a mettre Complete_File_name au niveau de DBQmais après FROM impossible: erreur générale ODBC

Si vous avez une solution je suis prenneur!!!

Bonne fin de journée

J'avais testé aussi " & Complete_File_name & " à la place de "`C:\Documents and Settings\MPLNET\Bureau\CQ URT Données OK.xls`

Mais toujours rien

Bonjour

as-tu essayé ?

`" & complete_file_name & "`

il me semble que as omis le caractère `

Bonjour à tous,

Pour pouvoir maintenir la requête, il est peut-être bon de la formater pour qu'elle soit beaucoup plus lisible.

Une fois bien formatée, les modifications seront beaucoup plus simple.

Ci-joint une proposition de reformatage (juste pour le principe et l'exemple), à vérifier et ré-écrire.

A noter que la clause WHERE est assez redondante avec le même critère qui revient (N° labo = 530577), et des mélanges de OR et AND sans parenthèses (non préconisé : on ne sait plus l'ordre de priorité des opérateurs).

Ce sont juste de bonnes pratiques, conseils à conserver ou jeter

Bon courage

Bouben

EDIT : si les données sont issues d'un seul onglet, il est possible de supprimer tous les préfixes de "table" ("`DonnéesOK$`."), ce qui rendra la requête encore plus claire !

Exemple, après modif du WHERE, et suppression des préfixes de table -à compléter un peu avec les Analystes)

 sReq = "SELECT `N° du labo :`, `Nom du contrôle :`, Analytes," & vbCrLf
    sReq = sReq & "Du, Au, Moyenne, CV," & vbCrLf
    sReq = sReq & "`Nb de points`" & Chr(13) & "" & Chr(10) & vbCrLf
    'FROM
    sReq = sReq & "FROM " & vbCrLf
    'exemple de remplacement
    'sReq = sReq & "`C:\Documents and Settings\MPLNET\Bureau\CQ URT Données OK.xls`.`DonnéesOK$` `DonnéesOK$`" & Chr(13) & "" & Chr(10) & vbCrLf
    sReq = sReq & "`" & Complete_File_name & "`.`DonnéesOK$` `DonnéesOK$`" & Chr(13) & "" & Chr(10) & vbCrLf
    'WHERE revu (proposition, en remplacement) :
    sReq = sReq & "WHERE `N° du labo :`='530577' " & vbCrLf
    sReq = sReq & "AND Analytes IN ('Chlorure Niv 1', 'Chlorure Niv 1 U', 'Chlorure Niv 2', etc)  "
    'ORDER BY
     sReq = sReq & Chr(13) & "" & Chr(10) & "ORDER BY Analytes"]

Bonjour h2so4, bouben et à tous!!

Merci de m'avoir répondu

h2so4 a écrit :

Bonjour

as-tu essayé ?

`" & complete_file_name & "`

il me semble que as omis le caractère `

oui j'avais essayé mais cela ne fonctionne pas

Effectivement bouben ton code est beaucoup plus lisible que celui de l’enregistreur de macro! et me permettra de faire d'autre requête plus aisément car je debute dans ce domaine

Par contre j'ai appliquer ton code avec quelques modifs mais ca ne fonctionne pas

Option Explicit

Sub testiii()

Dim Path_name As String
Dim File_name As String
Dim Complete_File_name As String

Dim nomClasseur As String
Dim sReq As String 'requête

nomClasseur = ActiveWorkbook.Name
Workbooks.Open Filename:=Application.GetOpenFilename("Fichiers Excel, *.XLS"), local:=True

File_name = ActiveWorkbook.Name
Path_name = ThisWorkbook.Path
Complete_File_name = Path_name & "\" & File_name

Windows(nomClasseur).Activate

    '**********************
    'Début de la requête
    '**********************
     sReq = "SELECT `N° du labo :`, `Nom du contrôle :`, Analytes," & vbCrLf
    sReq = sReq & "Du, Au, Moyenne, CV," & vbCrLf
    sReq = sReq & "`Nb de points`" & Chr(13) & "" & Chr(10) & vbCrLf
    'FROM
   sReq = sReq & "FROM " & vbCrLf
    'exemple de remplacement
   'sReq = sReq & "`C:\Documents and Settings\MPLNET\Bureau\CQ URT Données OK.xls`.`DonnéesOK$` `DonnéesOK$`" & Chr(13) & "" & Chr(10) & vbCrLf
   sReq = sReq & "`" & Complete_File_name & "`.`DonnéesOK$` `DonnéesOK$`" & Chr(13) & "" & Chr(10) & vbCrLf
    'WHERE revu (proposition, en remplacement) :
   sReq = sReq & "WHERE `N° du labo :`='530577' " & vbCrLf
    sReq = sReq & "AND Analytes IN ('Chlorure Niv 1', 'Chlorure Niv 1 U', 'Chlorure Niv 2','Chlorure Niv 2 U', 'Potassium Niv 1','Potassium Niv 1 U','Potassium Niv 2','Potassium Niv 2 U','Sodium Niv 1','Sodium Niv 1 U','Sodium Niv 2','Sodium Niv 2 U')  "
    'ORDER BY
    sReq = sReq & Chr(13) & "" & Chr(10) & "ORDER BY Analytes"
    'Affichage dans la fenêtre d'exécution, pour visualisation
    Debug.Print sReq

     '**********************
    'fin de la requête
    '**********************

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=Complete_File_name;DefaultDir=./;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination _
        :=Range("$A$1")).QueryTable
       .CommandText = Array(sReq)
        .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_Excel_Files14"
        .Refresh BackgroundQuery:=False
    End With
End Sub

J'ai une erreur au niv de l'array

Je pense que j'ai mal adapté ton code mais je ne vois pas comment faire

Par avance merci!!

Bonjour critof,

L'enregistreur automatique utilisait un tableau, peut-être parce que la requête était trop long ?

Après la modif faite, la requête est en un seul bloc, c'est--à-dire une chaîne de caractères.

A essayer avec la modif suivante :

.CommandText = Array(sReq)

=>

.CommandText = sReq

Et si la requête est trop longue, il faudra peut-être repasser par un tableau, en envoyant par exemple les premières lignes du SELECT dans l'indice 1, le FROM dans l'indice 2, le WHERE dans le 3.

A voir.

Pas pu lancé le code chez moi, évidemment.

Bonne journée

Bouben

Bonjour Bouben et merci pour ta réponse

Je viens de modifier .CommandText= sReq

mais j'ai toujours une erreur, cette fois ci c'est erreur 400

Je mets en PJ les 2 classeurs au cas ou quelqu'un aurais le temps et la gentillesse de se pencher sur le problème

Bonjour,

essaie ceci, correction de la definition du lien odbc + suppression du nom de fichier dans la clause SQL (inutile) + non ouverture du classeur contenant les données (inutile)

Option Explicit
Sub testiii()

Dim Path_name As String
Dim File_name As String
Dim Complete_File_name As String

Dim nomClasseur As String
Dim sReq As String 'requête
Dim s

nomClasseur = ActiveWorkbook.Name
File_name = Application.GetOpenFilename("Fichiers Excel, *.XLS*")
s = InStrRev(File_name, "\")
Path_name = Left(File_name, s - 1)
Complete_File_name = File_name

Windows(nomClasseur).Activate

    sReq = "SELECT * FROM `DonnéesOK$` `DonnéesOK$`" & _
           "WHERE `N° du labo :`='530577' " & _
           "AND Analytes IN ('Chlorure Niv 1', 'Chlorure Niv 1 U', 'Chlorure Niv 2','Chlorure Niv 2 U', 'Potassium Niv 1','Potassium Niv 1 U','Potassium Niv 2','Potassium Niv 2 U','Sodium Niv 1','Sodium Niv 1 U','Sodium Niv 2','Sodium Niv 2 U')  " & _
           "ORDER BY `DonnéesOK$`.Analytes"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=" & File_name & ";DefaultDir=" & Path_name & ";DriverId=1046;MaxBufferSize"), _
        Array("=2048;PageTimeout=5;")), Destination:=Range("$A$1")).QueryTable
        .CommandText = sReq
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Excel_Files"
        .Refresh BackgroundQuery:=False
    End With

End Sub

Merci h2so4!!

Je vais tester ca

Ca fonctionne parfaitement!!!

Merci h2so4 et merci bouben

@ bientot sur le forum!!