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

**rsuinux** · 18/01/2024, 15h05

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 :

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
 
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.

**Tête de chat** · 18/01/2024, 17h25

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 :

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
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

**rsuinux** · 18/01/2024, 18h20

@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 :

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
 
    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

**rsuinux** · 19/01/2024, 10h05

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 :

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
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.

**Tête de chat** · 19/01/2024, 10h44

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.

**rsuinux** · 19/01/2024, 14h28

est ce que le problème pourrait (en partie) venir des < ' > dans SqlQuery?

**Tête de chat** · 19/01/2024, 15h44

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

**Pierre Fauconnier** · 19/01/2024, 18h31

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.

**rsuinux** · 20/01/2024, 12h20

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.

**Pierre Fauconnier** · 20/01/2024, 13h01

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 vba :

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
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/

**Tête de chat** · 22/01/2024, 10h06

Bonjour,

Faut que j'optimise encore.

pourquoi tu ne veux pas (au moins essayer de) virer le array ?

**rsuinux** · 22/01/2024, 10h07

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.