[Excel 2016] Array en incompatibilité de typte pour une requête SQL

Version imprimable

Bonjour,
Je viens vous demander de l'aide car je ne m'en sort vraiment pas.
Je reprends derrière un collègue (parti définitivement). Je ne peux pas tout réécrire, et suis donc obligé de repartir de l'existant.
J'ai un classeur excel, qui génère des stat à partir de requête sql (oracle). Son truc, était de passé par des appel sur deux autres classeurs pour les appels à la base.
En ce qui me concerne, je récupère son code, et modifie 'juste' une des requêtes. c'est la ou ça pèche. Je déclare celle ci dans un array() (SqlQuery=Array("...."), mais à chaque utilisation de .commandText = SqlQuery j'ai une erreur 13 incompatibilité de type. Je n'arrive pas à savoir si c'est un problème de formatage dans l'array, ou le code VBA qui n'est pas conforme. A savoir, ma requête fonctionne , cela va de soit.

Ci dessous une partie du module Excel:
Code:

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 Private Declare PtrSafe Function GetEnvironmentVariable Lib "kernel32" _ Alias "GetEnvironmentVariableA" _ (ByVal lpName As String, _ ByVal lpBuffer As String, _ ByVal nSize As Long) As Long Function GetEnvironmentVar(Name As String) As String GetEnvironmentVar = String(255, 0) GetEnvironmentVariable Name, GetEnvironmentVar, Len(GetEnvironmentVar) GetEnvironmentVar = TrimNull(GetEnvironmentVar) End Function Private Function TrimNull(item As String) Dim iPos As Long iPos = InStr(item, vbNullChar) TrimNull = IIf(iPos > 0, Left$(item, iPos - 1), item) End Function Sub majref() SqlQuery = Array("SELECT M.idate , M.nom, M.nomjf, M.prenom, M.sexe, M.datenaiss, M.age, M.datej , M.heure , U.ref , U.result from MULTICOL M, URGRES U where M.idate = U.ippdate and u.ref = 'INJPED' and m.datej >= to_date('04/10/2023 00:00:00', 'dd-mm-yyyy hh24:mi:ss') and m.datej <= trunc(SYSDATE) and trunc(MONTHS_BETWEEN(SYSDATE, TO_DATE(m.datenaiss, 'DD/MM/YYYY'))) <= 18") Sheets("Beyfortus").Select 'supprimer ancien contenu ['Beyfortus'!A1:z1000].ClearContents ' /* ************************************ */ ' ************* Driver ODBC ************* ' /* ************************************ */ Application.Workbooks.Open ("\\srv-apps\scripts\prod\prod_odbc.xls"), ReadOnly:=True Application.Run ("prod_odbc.xls!Module1.urqprododbc") Workbooks("prod_odbc.xls").Close conn = GetEnvironmentVar("URQUAL") Range("A1").Select With Selection.QueryTable .Connection = conn .CommandText = SqlQuery ' <---- l'erreur 13 est ici End With 'Mise à jour des valeurs Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False
J'ai mis la requête en une ligne, pensant que le formatage sur plusieurs lignes posait problème (j'ai du mal avec cette syntaxe). Mais je souhaite modifier les sysdate par des variable date_debut et date_fin. Est-ce que je peux couper sans problème le texte dans l'array pour inclure des variables?
Le mieux étant concaténer dans l'array() le contenu de cellules, type SqlQuery = ( Range(A1).value & Range(A2).value....), mais est-ce que c'est valide aussi dans un array ? ca permettrait de ne pas toucher le vba et modifier la requête sur une feuille à part.

Merci de votre aide.
Rémi.

Bonjour,

Je ne sais pas ce que fait cet array par là, pour moi commandtext attend un string.
Et puis pas de déclaration de variables, des select un peu partout : prend le pouvoir et sans tout réécrire commence là ou tu passes à faire un peu de ménage... :)

ça devrait ressembler à :

Code:

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
Sub majref()
 
    Dim SqlQuery As String
    Dim Conn As String
 
    SqlQuery = "SELECT M.idate , M.nom, M.nomjf, M.prenom, M.sexe, M.datenaiss, M.age, M.datej , M.heure , U.ref , U.result from MULTICOL M, URGRES U where M.idate = U.ippdate and u.ref = 'INJPED' and m.datej >= to_date('04/10/2023 00:00:00', 'dd-mm-yyyy hh24:mi:ss') and m.datej <= trunc(SYSDATE) and trunc(MONTHS_BETWEEN(SYSDATE, TO_DATE(m.datenaiss, 'DD/MM/YYYY'))) <= 18"
 
    ' /* ************************************ */
    ' ************* Driver ODBC *************
    ' /* ************************************ */
    Application.Workbooks.Open ("\\srv-apps\scripts\prod\prod_odbc.xls"), ReadOnly:=True
    Application.Run ("prod_odbc.xls!Module1.urqprododbc")
    Workbooks("prod_odbc.xls").Close
    Conn = GetEnvironmentVar("URQUAL")
 
    With ThisWorkbook.Sheets("Beyfortus")
        .Range("A1:Z1000").ClearContents 'supprimer ancien contenu
 
        With .Range("A1").ListObject.QueryTable
            .Connection = Conn
            .CommandText = SqlQuery
            .Refresh BackgroundQuery:=False
        End With
    End With
 
End Sub

@tete de chat
merci de ton avis.
CommandText attend donc un string et pas un variant? ok.
Cependant, sur " With .Range("A1").ListObject.QueryTable " j'ai une erreur 91: Variable objet ou variable de bloc With non définie

Je fini par me demander si cela ne vient pas des appels "Application.Workbooks.Open" / "Application.Run" / "Workbooks().close"

J'ai mis (afin de vérifier les recopie)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Dim SqlQuery As String Dim Conn As String SqlQuery = "SELECT M.ippdate , M.nom, M.nomjf, M.prenom, M.sexe, M.datenaiss, M.age, M.datej , M.heure , U.ref , U.result from MULTICOL M, URGRES U where M.ippdate = U.ippdate and u.ref = 'INJPED' and m.datej >= to_date('04/10/2023 00:00:00', 'dd-mm-yyyy hh24:mi:ss') and m.datej <= trunc(SYSDATE) and trunc(MONTHS_BETWEEN(SYSDATE, TO_DATE(m.datenaiss, 'DD/MM/YYYY'))) <= 18" Sheets("Beyfortus").Select .../... With ThisWorkbook.Sheets("Beyfortus") .Range("A1:Z1000").ClearContents 'supprimer ancien contenu With .Range("A1").ListObject.QueryTable .Connection = Conn .CommandText = SqlQuery .Refresh BackgroundQuery:=False End With End With

J'ai avancé dans mon problème.
Ma feuille fonctionne avec le code précédent de mon collègue. Avec sa requeête, cela fonctionne, avec la mienne, ça plante. Je me dirige donc vers un problème de formalisme.
J'ai scindé la mienne pour plus de lisibilité.

Code:

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
Sub majref()
    Dim SqlQuery As String
    Dim Conn As String
    'SqlQuery = "SELECT M.ippdate , M.nom, M.nomjf, M.prenom, M.sexe, M.datenaiss, M.age, M.datej , M.heure , U.ref , U.result "
    'SqlQuery = SqlQuery & "from URQUAL.MULTICOL M, URQUAL.URGRES U where"
    'SqlQuery = SqlQuery & "M.ippdate = U.ippdate "
    'SqlQuery = SqlQuery & "and U.ref = 'INJPED' "
    'SqlQuery = SqlQuery & "and M.datej >= to_date('04/10/2023 00:00:00', 'dd-mm-yyyy hh24:mi:ss') "
    'SqlQuery = SqlQuery & "and M.datej <= trunc(SYSDATE) "
    'SqlQuery = SqlQuery & "and trunc(MONTHS_BETWEEN(SYSDATE, TO_DATE(m.datenaiss, 'DD/MM/YYYY'))) <= 18"



    SqlQuery = "SELECT LOGIN.IDENT, LOGIN.PSW, LOGIN.RIGHTS, LOGIN.NOM, LOGIN.DATEHEURE, LOGIN.SEC1, LOGIN.SEC2, LOGIN.SEC3, LOGIN.SEC4, LOGIN.SEC5, LOGIN.RPPS, LOGIN.NOREC "
    SqlQuery = SqlQuery & "FROM URQUAL.LOGIN LOGIN "
        
'Requêtes
    Application.ScreenUpdating = False
   
    ' /* ************************************ */
    ' ************* Driver ODBC *************
    ' /* ************************************ */
    Application.Workbooks.Open ("\\srv-apps\scripts\prod\prod_odbc.xls"), ReadOnly:=True
    Application.Run ("prod_odbc.xls!Module1.urqprododbc")
    Workbooks("prod_odbc.xls").Close     Conn = GetEnvironmentVar("URQUAL")

    Sheets("Beyfortus").Select
    'supprimer ancien contenu


    ['Beyfortus'!A1:z1000].ClearContents
    Range("A1").Select
    With Selection.QueryTable
    .Connection = Conn
    .CommandText = Array( _
        SqlQuery _
         )
    End With


    'Mise à jour et copie des données
    Range("A1").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False


  end sub

Chaque fois que je change le contenu de SqlQuery entre ma requête et celle de test (Select Login.ident...) la mienne plante par une erreur incompatibilité de type , on en revient au problème du début, et c'était donc un problème de formalisme.
La seconde requête fonctionne bien par contre.
Avez vous une donc sur les appels de ce type? C'est une base Oracle qui est appelée derrière.

Merci de vos avis.

19/01/2024, 09h44
Tête de chat

Bonjour

si c'est toujours le commandext= qui plante la (non) validité du SQL n'a rien à voir là dedans. Si tu fais pas exemple un commandtext = "toto" ça passera. Par contre au monent du refresh, le moteur Oracle qui est sollicité ne va pas aimer.

J'insiste pour que tu vires le "array"...
D'après ce que j'ai testé ça plante à partir de 256 caractères.
19/01/2024, 13h28
rsuinux

est ce que le problème pourrait (en partie) venir des < ' > dans SqlQuery?
19/01/2024, 14h44
Tête de chat
Hum...

... je me permets d'insister lourdement.

Le problème vient du array et du fait que ta requête fait plus de 256 caractères.
- soit tu vires le array...
- soit tu te débrouilles pour que ta requête fasse moins de 256 caractères
19/01/2024, 17h31
Pierre Fauconnier

Salut.

Je ne comprends pas trop bien pourquoi on devrait modifier la requête d'une table par code VBA. C'est une requête Power Query? Pourquoi ne pas la paramétrer?

Le VBA n'est à mon avis pas du tout utile ici.

Vous devriez expliquer votre problème "hors vba" pour que l'on puisse mieux vous aiguiller.
20/01/2024, 11h20
rsuinux
Citation:
Envoyé par Tête de chat

Hum...

... je me permets d'insister lourdement.

Le problème vient du array et du fait que ta requête fait plus de 256 caractères.

soit tu vires le array...
soit tu te débrouilles pour que ta requête fasse moins de 256 caractères
Mea-culpa. Je viens de vérifier: j'ai 300 caractères....
En optimisant la requête je peux tirer à 273. Je ferais du post-traitement dans excel après.
Faut que j'optimise encore.

Je vais retourner à mon clavier.

@Pierre Fauconnier : Le problème de powerquery, c’est que je suis obligé de me baser sur l'existant.

Merci de vos avis.
Rémi.

Perso, j'utiliserais une requête paramétrée. Cela

évite la concaténation laborieuse pour recréer les bonnes valeurs
contourne le problème de la longueur du texte de la requête.

Voici un exemple de requête paramétrée. Il faut créer les paramètres dans l'ordre des ? repris dans le texte de la requête.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Sub RequeteParametree() Dim sql As String Dim cn As ADODB.Connection Dim cm As ADODB.Command Dim Rows Set cn = New ADODB.Connection cn.Open Range("Connexion").Value Set cm = New ADODB.Command cm.ActiveConnection = cn cm.CommandType = adCmdText sql = "select * from tests.dbo.Contact where inscriptiondate between ? and ?" cm.CommandText = sql cm.Parameters.Append cm.CreateParameter("p1", adDate, adParamInput, 4, DateSerial(2020, 6, 1)) cm.Parameters.Append cm.CreateParameter("p2", adDate, adParamInput, 4, DateSerial(2020, 6, 30)) Rows = cm.Execute() Set cm = Nothing cn.Close Set cn = Nothing End Sub
Voir mon billet (et les suivants) sur les requêtes sql en VBA: https://www.developpez.net/forums/bl...-generiques-1/

22/01/2024, 09h06
Tête de chat

Bonjour,

Citation:

Faut que j'optimise encore.

pourquoi tu ne veux pas (au moins essayer de) virer le array ? 8-)
22/01/2024, 09h07
rsuinux

Bonjour;
le problème est réglé.
Le plus "gros" a été de comprendre, après vos judicieux conseils, pourquoi cela continuait à ne pas fonctionner.
En fait, c'est tout bête (comme souvent: il faut par exemple, pour les dates, mettre des quottes sous oracles, pareil pour les paramètres de type texte U.ref = 'INJPED' "
Mis comme cela, ça ne passe pas :(

Une fois compris ça, ce fut simple.

Merci à tous pour votre aide.
22/01/2024, 09h08
Pierre Fauconnier

Citation:

Envoyé par rsuinux

[...]il faut par exemple, pour les dates, mettre des quottes sous oracles, pareil pour les paramètres de type texte[...]

D'où l'intérêt d'utiliser les paramètres, qui évitent qu'on se casse la figure en recréant la requête à coups de concaténation.