Erreur Instruction VBA non valide

Bonjour à tous ,

après l’exécution de la requête suivante sous vba j'ai eu Erreur Instruction VBA non valide ?? ,besoin de votre aide .

merci d'avance

requête :

Sql = "SELECT DRI_ACC.DRI_CODE," & vbCrLfSql = Sql & "      DRI_ACC.DRI_LINK," & vbCrLf
Sql = Sql & "      DRI_REF.DRI_SEC_SETTLT_SYST_REF AS REF_GSP," & vbCrLf
Sql = Sql & "      DRI_ACC.DRI_CLIENT_REF AS REF_CLIENT," & vbCrLf
Sql = Sql & "      SCO.SCO_CODE AS ISIN," & vbCrLf
Sql = Sql & "      SCO.SCO_NAME AS LIB_ISIN," & vbCrLf
Sql = Sql & "      DRI_ACC.DEP_CODE," & vbCrLf
Sql = Sql & "      DEP.DEP_NAME," & vbCrLf
Sql = Sql & "      DRI_ACC.SAC_CODE," & vbCrLf
Sql = Sql & "      SAC.SAC_NAME," & vbCrLf
Sql = Sql & "      DRI_ACC.STS_CODE," & vbCrLf
Sql = Sql & "      (SELECT ICO_CODE FROM ITL_CONTEXT WHERE IL_LNG_ID = '001' AND ICO_PSEUDO = DRI_ACC.IL_CTX_ID) AS CONTEXTE," & vbCrLf
Sql = Sql & "      to_char(PFC_54X.DATE_CREATION, 'YYYY/MM/DD HH24:MI:SS')AS DATE_CREATION_PFC_54X," & vbCrLf
Sql = Sql & "      to_char (PFC_54X.DATE_MODIFICATION,'YYYY/MM/DD HH24:MI:SS')AS DATE_MODIF_PFC_54X," & vbCrLf
Sql = Sql & "      to_char (DRI_ACC.DRI_CREATE_DATE,'YYYY/MM/DD HH24:MI:SS' )AS DATE_CREATION_SIB," & vbCrLf
Sql = Sql & "      to_char (DRI_EV.DRI_INPUT_DATE,'YYYY/MM/DD HH24:MI:SS') AS DATE_EV_SIB," & vbCrLf
Sql = Sql & "      to_char (DRI_ACC.DRI_INPUT_DATE,'YYYY/MM/DD HH24:MI:SS' )AS DATE_ACQ_SIB," & vbCrLf
Sql = Sql & "      to_char (PFC_548.DATE_CREATION,'YYYY/MM/DD HH24:MI:SS' )AS DATE_CREATION_PFC_548," & vbCrLf
Sql = Sql & "      to_char (PFC_548.DATE_MODIFICATION,'YYYY/MM/DD HH24:MI:SS') AS DATE_ACQ_PFC_548," & vbCrLf
Sql = Sql & "     to_char (PFC_54X.DATE_MODIFICATION - PFC_54X.DATE_CREATION)* 86400 AS TRAITEMENT_PFC," & vbCrLf
Sql = Sql & "     to_char (DRI_EV.DRI_INPUT_DATE - PFC_54X.DATE_MODIFICATION)* 86400 AS TRAITEMENT_SIB," & vbCrLf
Sql = Sql & "     to_char (DRI_ACC.DRI_INPUT_DATE - DRI_EV.DRI_INPUT_DATE)* 86400 AS DELAI_ALLER_RETOUR_T2S," & vbCrLf
Sql = Sql & "     to_char (PFC_548.DATE_MODIFICATION - DRI_EV.DRI_INPUT_DATE)* 86400 AS DELAI_1ER_548," & vbCrLf
Sql = Sql & "     to_char (PFC_548.DATE_MODIFICATION - PFC_54X.DATE_CREATION)* 86400 AS DELAI_TOTAL_548" & vbCrLf
Sql = Sql & " FROM PFCMSGH PFC_54X, PFCMSGH PFC_548, DRI_HISTO DRI_ACC, DRI_HISTO DRI_EV, DEPOSITARY DEP, SECURITIES_ACCOUNT SAC, DRI_HISTO DRI_REF, SECURITY_CODIF SCO" & vbCrLf
Sql = Sql & " WHERE 1=1" & vbCrLf
Sql = Sql & " AND (DRI_ACC.dri_create_date >= TO_DATE(:date_deb_DDMMYYYY,'DDMMYYYY')" & vbCrLf
Sql = Sql & " AND DRI_ACC.dri_create_date < TO_DATE(:date_fin_DDMMYYYY,'DDMMYYYY'))" & vbCrLf
Sql = Sql & " AND PFC_54X.ID = (SELECT MIN(ID) FROM LIEN_MESSAGE WHERE LME_APP_CODE = DRI_ACC.DRI_CODE||DRI_ACC.DRI_LINK AND IL_APP_ID = '001' AND IC_LME_SENS = '001')" & vbCrLf
Sql = Sql & " AND PFC_54X.IL_PFS_ID = '075'" & vbCrLf
Sql = Sql & " AND SAC.SAC_CODE = DRI_ACC.SAC_CODE AND SAC.SAC_BAK_CODE = DRI_ACC.SAC_BAK_CODE" & vbCrLf
Sql = Sql & " AND DRI_ACC.SEC_CODE = SCO.SEC_CODE AND SCO.COF_CODE = 'ISIN'" & vbCrLf
Sql = Sql & " AND DRI_ACC.DEP_CODE = DEP.DEP_CODE" & vbCrLf
Sql = Sql & " AND PFC_548.ID = (SELECT MIN(ID) FROM LIEN_MESSAGE WHERE LME_APP_CODE = DRI_ACC.DRI_CODE||DRI_ACC.DRI_LINK AND IL_APP_ID = '001' AND IL_REP_ID IN ('193','194','195','198','201') AND LME_CREATION_DATE >= DRI_ACC.DRI_INPUT_DATE)" & vbCrLf
Sql = Sql & " " & vbCrLf
Sql = Sql & " AND PFC_548.IL_PFS_ID = '040'" & vbCrLf
Sql = Sql & " " & vbCrLf
Sql = Sql & " AND DRI_ACC.DRI_INPUT_DATE = (SELECT MIN(DRI_INPUT_DATE) FROM DRI_HISTO DRI_MIN WHERE DRI_MIN.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_MIN.DRI_LINK = DRI_ACC.DRI_LINK AND DRI_MIN.IL_STA_ID = '009')" & vbCrLf
Sql = Sql & " AND DRI_ACC.IL_STA_ID = '009'" & vbCrLf
Sql = Sql & " AND DRI_REF.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_REF.DRI_LINK = DRI_ACC.DRI_LINK" & vbCrLf
Sql = Sql & " AND DRI_REF.DRI_INPUT_DATE = (SELECT MIN(DRI_INPUT_DATE) FROM DRI_HISTO DRI_MIN WHERE DRI_MIN.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_MIN.DRI_LINK = DRI_ACC.DRI_LINK AND DRI_MIN.DRI_SEC_SETTLT_SYST_REF IS NOT NULL)" & vbCrLf
Sql = Sql & " AND DRI_EV.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_EV.DRI_LINK = DRI_ACC.DRI_LINK" & vbCrLf
Sql = Sql & " AND DRI_EV.DRI_INPUT_DATE = (SELECT MIN(DRI_INPUT_DATE) FROM DRI_HISTO DRI_MIN WHERE DRI_MIN.DRI_CODE = DRI_ACC.DRI_CODE AND DRI_MIN.DRI_LINK = DRI_ACC.DRI_LINK AND DRI_MIN.IL_STA_ID = '012')" & vbCrLf
Sql = Sql & " AND DRI_EV.IL_STA_ID = '012'" & vbCrLf
Sql = Sql & " AND NOT EXISTS (SELECT NULL FROM DRI_HISTO WHERE DRI_CODE = DRI_ACC.DRI_CODE AND IL_STA_ID IN ('001','007')" & vbCrLf
Sql = Sql & " UNION SELECT NULL FROM DRI_HISTO_RECENT WHERE DRI_CODE = DRI_ACC.DRI_CODE AND IL_STA_ID IN ('001','007'))" & vbCrLf
Sql = Sql & " AND TRUNC(PFC_54X.DATE_MODIFICATION) = TRUNC(PFC_548.DATE_CREATION) " & vbCrLf
Sql = Sql & " AND DRI_ACC.STS_CODE = 'T2S';" & vbCrLf
Sql = Sql & " select count(*)," & vbCrLf
Sql = Sql & "       substr(to_char(dri_create_date, 'YYYY/MM/DD HH24:MI:SS'), 12, 2) AS HOURS," & vbCrLf
Sql = Sql & "       substr(to_char(dri_create_date, 'YYYY/MM/DD HH24:MI:SS'), 15, 2) AS MINUTES," & vbCrLf
Sql = Sql & "       trunc(dri_create_date), " & vbCrLf
Sql = Sql & "       (SELECT ICO_CODE||' - '||ICO_NAME  FROM ITL_CONTEXT WHERE IL_LNG_ID = '001' AND ICO_PSEUDO = DRI.IL_CTX_ID) AS CONTEXTE," & vbCrLf
Sql = Sql & "       (SELECT IOC_CODE||' - '||IOC_NAME FROM ITL_OPERATION_CODE WHERE IL_LNG_ID = '001' AND IOC_PSEUDO = DRI.IL_OPC_ID) AS CODE_OPE," & vbCrLf
Sql = Sql & "       DRI.DEP_CODE, DEP.DEP_NAME," & vbCrLf
Sql = Sql & "       DRI.STS_CODE" & vbCrLf
Sql = Sql & " FROM DRI_HISTO DRI, DEPOSITARY DEP" & vbCrLf
Sql = Sql & " WHERE 1=1" & vbCrLf
Sql = Sql & " AND DRI.IL_STA_ID IN ('003','008')" & vbCrLf
Sql = Sql & " AND DRI.DEP_CODE = DEP.DEP_CODE" & vbCrLf
Sql = Sql & " AND DRI.dri_create_date >= TO_DATE(:date_deb_DDMMYYYY,'DDMMYYYY')" & vbCrLf
Sql = Sql & " AND DRI.dri_create_date < TO_DATE(:date_fin_DDMMYYYY,'DDMMYYYY')" & vbCrLf
Sql = Sql & " group by" & vbCrLf
Sql = Sql & "       substr(to_char(dri_create_date, 'YYYY/MM/DD HH24:MI:SS'), 12, 2)," & vbCrLf
Sql = Sql & "       substr(to_char(dri_create_date, 'YYYY/MM/DD HH24:MI:SS'), 15, 2)," & vbCrLf
Sql = Sql & "       trunc(dri_create_date), " & vbCrLf
Sql = Sql & "       DRI.IL_CTX_ID," & vbCrLf
Sql = Sql & "       DRI.IL_OPC_ID," & vbCrLf
Sql = Sql & "       DRI.DEP_CODE," & vbCrLf
Sql = Sql & "       DEP.DEP_NAME," & vbCrLf
Sql = Sql & "       DRI.STS_CODE;"

Set rs = con.Execute(Sql)
ActiveCell.CopyFromRecordset rs

Bonjour,

vbCrLfSql peut-être ! (1re ligne). Constante VBA inconnue à ma connaissance...

vous pouvez m'expliquer ? et me donner une solution concrète ?

Visiblement, ta première ligne est constituée par deux lignes de code dont le séparateur a disparu, ce qui amalgame la fin de la première : cbCrLf au début de la seconde : Sql = pour créer cette fausse constante vbCrLfSql !

Sql = "SELECT DRI_ACC.DRI_CODE," & vbCrLf:Sql = Sql & "      DRI_ACC.DRI_LINK," & vbCrLf

j'ai l'erreur suivant :

y a personne qui peut m'aider ??

Tu ne dis pas avec quel code tu as cette erreur, ni sur quelle ligne...