Microsoft 365 ADODB : L'affichage des données manquantes

VBA_dev_Anne_Marie · 24 Février 2022

Bonjour,

Je n'arrive pas à afficher correctement les données après une requête adodb sql.
Voici mes données en sortie :

Voici les données attendues :

Je pense que c'est à cause de la condition Do While Not RECSET2.EOF que les lignes affichées sont celles qui sont "alimentées" :

VB:

  Do While Not RECSET2.EOF
      
     ActiveSheet.Cells(xlRow, Range("Colonne_1").Column).Value = RECSET2("NO_POLICE").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_2").Column).Value = RECSET2("CD_SUPPORT").Value
      
      
     Select Case RECSET2.Fields("IS_DEVISE").Value
     Case 46
     ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "EUR"
     Case Else
     ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "UC"
     End Select
    
     ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).Value = RECSET2("TAUX1").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).Value = RECSET2("TAUX2").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).Value = ""
     ActiveSheet.Cells(xlRow, Range("Colonne_7").Column).Value = RECSET2("D_EFFET").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).Value = RECSET2("MT_BRUT").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_9").Column).Value = RECSET2("LP_NATUR_FLUX").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).Value = RECSET2("MT_EA1").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).Value = RECSET2("Brut_fis").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).Value = RECSET2("Fiscalite").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).Value = RECSET2("MT_EA2").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).NumberFormat = "#,##0.00€"

      RECSET2.MoveNext

      xlRow = xlRow + 1
      
     Loop
      RECSET2.Close

Merci pour votre aide !

VBA_dev_Anne_Marie · 26 Février 2022

Merci beaucoup !
Je reçois cette erreur, il y a une parenthèse à droit manquante ? Je suis en train de chercher.

dysorthographie · 26 Février 2022

pour l'instant je n'es pas trouvé de parenthèse manquante!

il y en à 17 ouvrante et 17 fermantes! ado à forcément raison reste à trouvé une parenthèse mal placé!

Notepad++ offre des outils pour mètre en évidence les problème!

dysorthographie · 26 Février 2022

Il semblerait que ce soit un problème récurrent chez Oracle pour ce qui concerne les sous requête !

Ça fait au-moins 15 que je n'ai pas travaillé sous Oracle ! Il faut que je mette à jour mon logiciel.

Refais un test en supprimant as FRM de la requête !

VBA_dev_Anne_Marie · 26 Février 2022

Merci, j'ai toujours la même erreur, peut-être j'ai mal copier-coller ?

VB:

Public Sub Donnees_EA()
     Dim RECSET2 As New ADODB.Recordset
   
     Dim Num_Ligne As Long
     Dim Num_Ligne2 As Long
     Num_Ligne = Range("Chapeau").Row
     Police_donnee = Trim(UCase(Worksheets("DONNES_EA").Range("Police_donnee").Value))
     While Cells(Num_Ligne, Range("Colonne_4").Column) <> ""
     ActiveSheet.Cells(Num_Ligne, Range("Colonne_4").Column).EntireRow.Clear
     Num_Ligne = Num_Ligne + 1
     Wend
   
     Call CONNEXION_PEGASE("xxxx", "xxx", "xxx")

     ActiveSheet.Range("Colonne_1").Value = "Contrat"
     ActiveSheet.Range("Colonne_2").Value = "Support"
     ActiveSheet.Range("Colonne_3").Value = "Type"
     ActiveSheet.Range("Colonne_4").Value = "Frais de gestion"
     ActiveSheet.Range("Colonne_5").Value = "Taux de PAB net"
     ActiveSheet.Range("Colonne_6").Value = "Taux de bonus"
     ActiveSheet.Range("Colonne_7").Value = "Date mouvement"
     ActiveSheet.Range("Colonne_8").Value = "Montant mouvement"
     ActiveSheet.Range("Colonne_9").Value = "Motif mouvement"
     ActiveSheet.Range("Colonne_10").Value = "PM N-1 Pegase nette de fiscalité "
     ActiveSheet.Range("Colonne_11").Value = " PM N Pegase brut de fiscalité"
     ActiveSheet.Range("Colonne_12").Value = "Fiscalité"
     ActiveSheet.Range("Colonne_13").Value = "PM N Pegase nette de fiscalité"

Dim Sql As String
 
Sql = " select NO_POLICE,CD_SUPPORT,iif(IS_DEVISE=46,'EUR','UC') as [Type],TAUX1,TAUX2," & _
    " 0 as [Taux de bonus],D_EFFET,MT_BRUT,LP_NATUR_FLUX,MT_EA1,Brut_fis,Fiscalite,MT_EA2" & _
    " From (" & _
" select ev1.NO_POLICE, abs(sum(ev1.MT_BRUT)) as Fiscalite, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT," & _
    " ev2.TX_TAUX/100 as TAUX1,ev3.TX_TAUX/100 as TAUX2, srva1.MT_EA as MT_EA1, srva2.MT_EA as MT_EA2, (abs(sum(ev1.MT_BRUT))+srva2.MT_EA) as Brut_fis," & _
    " ev4.MT_BRUT,ev4.D_EFFET,ev4.LP_NATUR_FLUX" & _
    " from DB_EVENEMENT ev1 left join DB_SUPPORT sup on ev1.IS_SUPPORT=sup.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev2 on  ev1.NO_POLICE=ev2.NO_POLICE and ev1.IS_SUPPORT=ev2.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev3 on  ev1.NO_POLICE=ev3.NO_POLICE and ev1.IS_SUPPORT=ev3.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva1 on  ev1.NO_POLICE=srva1.NO_POLICE and ev1.IS_SUPPORT=srva1.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva2 on  ev1.NO_POLICE=srva2.NO_POLICE and ev1.IS_SUPPORT=srva2.IS_SUPPORT" & _
    " left join  DB_EVENEMENT ev4 on  ev1.NO_POLICE=ev4.NO_POLICE and ev1.IS_SUPPORT=ev4.IS_SUPPORT " & _
    " where ev1.NO_POLICE='" & Police_donnee & "' and  ev1.IS_CLASSE_EVT=365" & _
    " and ev1.LP_STATUT_EVT='DONE' and extract(year from ev1.D_EFFET)=2021 and" & _
    " ev2.IS_CLASSE_EVT=563 and extract(year from ev2.D_EFFET)>2020" & _
    " and ev3.IS_CLASSE_EVT=121 and ev3.N_EXERCICE_CPTA in (2021) and ev3.LP_STATUT_EVT^='ANNUL'" & _
    " and extract(year from srva1.D_VALO)=2020" & _
    " and extract(month from srva1.D_VALO)=12  and extract(day from srva1.D_VALO)=31" & _
    " and srva1.S_TYPE_SUPPORT='TXGAR'" & _
    " and extract(year from srva2.D_VALO)=2021" & _
    " and extract(month from srva2.D_VALO)=12  and extract(day from srva2.D_VALO)=31" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR'" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR' and extract(year from ev4.d_effet)=2021 and ev4.IS_CLASSE_EVT=39" & _
    " group by ev1.NO_POLICE, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT,ev2.TX_TAUX/100, ev3.TX_TAUX/100,srva1.MT_EA,srva2.MT_EA, ev4.MT_BRUT, ev4.D_EFFET, ev4.LP_NATUR_FLUX"
 
  xlRow = Range("Colonne_1").Row + 1 + xlRow
 
  ActiveSheet.Cells(xlRow, Range("Colonne_1").Column).CopyFromRecordset cnn_Pegase.Execute(Sql)

With ActiveSheet
    .Columns("Colonne_4").NumberFormat = "0.00%"
    .Columns("Colonne_5").NumberFormat = "0.00%"
    .Columns("Colonne_6").NumberFormat = "0.00%"
    .Columns("Colonne_8").NumberFormat = "#,##0.00€"
    .Columns("Colonne_10").NumberFormat = "#,##0.00€"
    .Columns("Colonne_11").NumberFormat = "#,##0.00€"
    .Columns("Colonne_12").NumberFormat = "#,##0.00€"
    .Columns("Colonne_13").NumberFormat = "#,##0.00€"
End With

      Call DECONNEXION_PEGASE


    End Sub

Merci pour votre aide.

dysorthographie · 26 Février 2022

là pour le coup il manque vraiment une parenthèse!

VB:

Sql = " select NO_POLICE,CD_SUPPORT,iif(IS_DEVISE=46,'EUR','UC') as [Type],TAUX1,TAUX2," & _
    " 0 as [Taux de bonus],D_EFFET,MT_BRUT,LP_NATUR_FLUX,MT_EA1,Brut_fis,Fiscalite,MT_EA2" & _
    " From (" & _
" select ev1.NO_POLICE, abs(sum(ev1.MT_BRUT)) as Fiscalite, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT," & _
    " ev2.TX_TAUX/100 as TAUX1,ev3.TX_TAUX/100 as TAUX2, srva1.MT_EA as MT_EA1, srva2.MT_EA as MT_EA2, (abs(sum(ev1.MT_BRUT))+srva2.MT_EA) as Brut_fis," & _
    " ev4.MT_BRUT,ev4.D_EFFET,ev4.LP_NATUR_FLUX" & _
    " from DB_EVENEMENT ev1 left join DB_SUPPORT sup on ev1.IS_SUPPORT=sup.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev2 on  ev1.NO_POLICE=ev2.NO_POLICE and ev1.IS_SUPPORT=ev2.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev3 on  ev1.NO_POLICE=ev3.NO_POLICE and ev1.IS_SUPPORT=ev3.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva1 on  ev1.NO_POLICE=srva1.NO_POLICE and ev1.IS_SUPPORT=srva1.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva2 on  ev1.NO_POLICE=srva2.NO_POLICE and ev1.IS_SUPPORT=srva2.IS_SUPPORT" & _
    " left join  DB_EVENEMENT ev4 on  ev1.NO_POLICE=ev4.NO_POLICE and ev1.IS_SUPPORT=ev4.IS_SUPPORT " & _
    " where ev1.NO_POLICE='" & Police_donnee & "' and  ev1.IS_CLASSE_EVT=365" & _
    " and ev1.LP_STATUT_EVT='DONE' and extract(year from ev1.D_EFFET)=2021 and" & _
    " ev2.IS_CLASSE_EVT=563 and extract(year from ev2.D_EFFET)>2020" & _
    " and ev3.IS_CLASSE_EVT=121 and ev3.N_EXERCICE_CPTA in (2021) and ev3.LP_STATUT_EVT^='ANNUL'" & _
    " and extract(year from srva1.D_VALO)=2020" & _
    " and extract(month from srva1.D_VALO)=12  and extract(day from srva1.D_VALO)=31" & _
    " and srva1.S_TYPE_SUPPORT='TXGAR'" & _
    " and extract(year from srva2.D_VALO)=2021" & _
    " and extract(month from srva2.D_VALO)=12  and extract(day from srva2.D_VALO)=31" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR'" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR' and extract(year from ev4.d_effet)=2021 and ev4.IS_CLASSE_EVT=39" & _
    " group by ev1.NO_POLICE, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT,ev2.TX_TAUX/100, ev3.TX_TAUX/100,srva1.MT_EA,srva2.MT_EA, ev4.MT_BRUT, ev4.D_EFFET, ev4.LP_NATUR_FLUX)"

VBA_dev_Anne_Marie · 27 Février 2022

dysorthographie à dit:

là pour le coup il manque vraiment une parenthèse!

VB:

Sql = " select NO_POLICE,CD_SUPPORT,iif(IS_DEVISE=46,'EUR','UC') as [Type],TAUX1,TAUX2," & _
    " 0 as [Taux de bonus],D_EFFET,MT_BRUT,LP_NATUR_FLUX,MT_EA1,Brut_fis,Fiscalite,MT_EA2" & _
    " From (" & _
" select ev1.NO_POLICE, abs(sum(ev1.MT_BRUT)) as Fiscalite, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT," & _
    " ev2.TX_TAUX/100 as TAUX1,ev3.TX_TAUX/100 as TAUX2, srva1.MT_EA as MT_EA1, srva2.MT_EA as MT_EA2, (abs(sum(ev1.MT_BRUT))+srva2.MT_EA) as Brut_fis," & _
    " ev4.MT_BRUT,ev4.D_EFFET,ev4.LP_NATUR_FLUX" & _
    " from DB_EVENEMENT ev1 left join DB_SUPPORT sup on ev1.IS_SUPPORT=sup.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev2 on  ev1.NO_POLICE=ev2.NO_POLICE and ev1.IS_SUPPORT=ev2.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev3 on  ev1.NO_POLICE=ev3.NO_POLICE and ev1.IS_SUPPORT=ev3.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva1 on  ev1.NO_POLICE=srva1.NO_POLICE and ev1.IS_SUPPORT=srva1.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva2 on  ev1.NO_POLICE=srva2.NO_POLICE and ev1.IS_SUPPORT=srva2.IS_SUPPORT" & _
    " left join  DB_EVENEMENT ev4 on  ev1.NO_POLICE=ev4.NO_POLICE and ev1.IS_SUPPORT=ev4.IS_SUPPORT " & _
    " where ev1.NO_POLICE='" & Police_donnee & "' and  ev1.IS_CLASSE_EVT=365" & _
    " and ev1.LP_STATUT_EVT='DONE' and extract(year from ev1.D_EFFET)=2021 and" & _
    " ev2.IS_CLASSE_EVT=563 and extract(year from ev2.D_EFFET)>2020" & _
    " and ev3.IS_CLASSE_EVT=121 and ev3.N_EXERCICE_CPTA in (2021) and ev3.LP_STATUT_EVT^='ANNUL'" & _
    " and extract(year from srva1.D_VALO)=2020" & _
    " and extract(month from srva1.D_VALO)=12  and extract(day from srva1.D_VALO)=31" & _
    " and srva1.S_TYPE_SUPPORT='TXGAR'" & _
    " and extract(year from srva2.D_VALO)=2021" & _
    " and extract(month from srva2.D_VALO)=12  and extract(day from srva2.D_VALO)=31" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR'" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR' and extract(year from ev4.d_effet)=2021 and ev4.IS_CLASSE_EVT=39" & _
    " group by ev1.NO_POLICE, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT,ev2.TX_TAUX/100, ev3.TX_TAUX/100,srva1.MT_EA,srva2.MT_EA, ev4.MT_BRUT, ev4.D_EFFET, ev4.LP_NATUR_FLUX)"

Bonjour,
Merci, maintenant j'ai une autre erreur :

VB:

Public Sub Donnees_EA()
     Dim RECSET2 As New ADODB.Recordset
  
     Dim Num_Ligne As Long
     Dim Num_Ligne2 As Long
     Num_Ligne = Range("Chapeau").Row
     Police_donnee = Trim(UCase(Worksheets("DONNES_EA").Range("Police_donnee").Value))
     While Cells(Num_Ligne, Range("Colonne_4").Column) <> ""
     ActiveSheet.Cells(Num_Ligne, Range("Colonne_4").Column).EntireRow.Clear
     Num_Ligne = Num_Ligne + 1
     Wend
  
     Call CONNEXION_PEGASE("xxx", "xxx", "xxx")
     ActiveSheet.Range("Colonne_1").Value = "Contrat"
     ActiveSheet.Range("Colonne_2").Value = "Support"
     ActiveSheet.Range("Colonne_3").Value = "Type"
     ActiveSheet.Range("Colonne_4").Value = "Frais de gestion"
     ActiveSheet.Range("Colonne_5").Value = "Taux de PAB net"
     ActiveSheet.Range("Colonne_6").Value = "Taux de bonus"
     ActiveSheet.Range("Colonne_7").Value = "Date mouvement"
     ActiveSheet.Range("Colonne_8").Value = "Montant mouvement"
     ActiveSheet.Range("Colonne_9").Value = "Motif mouvement"
     ActiveSheet.Range("Colonne_10").Value = "PM N-1 Pegase nette de fiscalité "
     ActiveSheet.Range("Colonne_11").Value = " PM N Pegase brut de fiscalité"
     ActiveSheet.Range("Colonne_12").Value = "Fiscalité"
     ActiveSheet.Range("Colonne_13").Value = "PM N Pegase nette de fiscalité"
 
 

      RECSET2.Open " select ev1.NO_POLICE, abs(sum(ev1.MT_BRUT)) as Fiscalite, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT," & _
    " ev2.TX_TAUX/100 as TAUX1,ev3.TX_TAUX/100 as TAUX2, srva1.MT_EA as MT_EA1, srva2.MT_EA as MT_EA2, (abs(sum(ev1.MT_BRUT))+srva2.MT_EA) as Brut_fis," & _
    " ev4.MT_BRUT,ev4.D_EFFET,ev4.LP_NATUR_FLUX" & _
    " from DB_EVENEMENT ev1 left join DB_SUPPORT sup on ev1.IS_SUPPORT=sup.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev2 on  ev1.NO_POLICE=ev2.NO_POLICE and ev1.IS_SUPPORT=ev2.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev3 on  ev1.NO_POLICE=ev3.NO_POLICE and ev1.IS_SUPPORT=ev3.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva1 on  ev1.NO_POLICE=srva1.NO_POLICE and ev1.IS_SUPPORT=srva1.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva2 on  ev1.NO_POLICE=srva2.NO_POLICE and ev1.IS_SUPPORT=srva2.IS_SUPPORT" & _
    " left join  DB_EVENEMENT ev4 on  ev1.NO_POLICE=ev4.NO_POLICE and ev1.IS_SUPPORT=ev4.IS_SUPPORT " & _
    " where ev1.NO_POLICE='" & Police_donnee & "' and  ev1.IS_CLASSE_EVT=365" & _
    " and ev1.LP_STATUT_EVT='DONE' and extract(year from ev1.D_EFFET)=2021 and" & _
    " ev2.IS_CLASSE_EVT=563 and extract(year from ev2.D_EFFET)>2020" & _
    " and ev3.IS_CLASSE_EVT=121 and ev3.N_EXERCICE_CPTA in (2021) and ev3.LP_STATUT_EVT^='ANNUL'" & _
    " and extract(year from srva1.D_VALO)=2020" & _
    " and extract(month from srva1.D_VALO)=12  and extract(day from srva1.D_VALO)=31" & _
    " and srva1.S_TYPE_SUPPORT='TXGAR'" & _
    " and extract(year from srva2.D_VALO)=2021" & _
    " and extract(month from srva2.D_VALO)=12  and extract(day from srva2.D_VALO)=31" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR'" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR' and extract(year from ev4.d_effet)=2021 and ev4.IS_CLASSE_EVT=39" & _
    " group by ev1.NO_POLICE, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT,ev2.TX_TAUX/100, ev3.TX_TAUX/100,srva1.MT_EA,srva2.MT_EA, ev4.MT_BRUT, ev4.D_EFFET, ev4.LP_NATUR_FLUX)", cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

      xlRow = Range("Colonne_1").Row + 1 + xlRow

    
 

     Do While Not RECSET2.EOF

     ActiveSheet.Cells(xlRow, Range("Colonne_1").Column).Value = RECSET2("NO_POLICE").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_2").Column).Value = RECSET2("CD_SUPPORT").Value


     Select Case RECSET2.Fields("IS_DEVISE").Value
     Case 46
     ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "EUR"
     Case Else
     ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "UC"
     End Select

     ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).Value = RECSET2("TAUX1").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).Value = RECSET2("TAUX2").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).Value = 0
     ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_7").Column).Value = RECSET2.Fields("D_EFFET").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).Value = RECSET2("MT_BRUT").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_9").Column).Value = RECSET2("LP_NATUR_FLUX").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).Value = RECSET2("MT_EA1").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).Value = RECSET2("Brut_fis").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).Value = RECSET2("Fiscalite").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).Value = RECSET2("MT_EA2").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).NumberFormat = "#,##0.00€"

      RECSET2.MoveNext
      xlRow = xlRow + 1

     Loop
  
      RECSET2.Close
      Call DECONNEXION_PEGASE


    End Sub

VBA_dev_Anne_Marie · 27 Février 2022

MarieParis à dit:

Bonjour,
Merci, maintenant j'ai une autre erreur :
Regarde la pièce jointe 1132104

VB:

Public Sub Donnees_EA()
     Dim RECSET2 As New ADODB.Recordset
 
     Dim Num_Ligne As Long
     Dim Num_Ligne2 As Long
     Num_Ligne = Range("Chapeau").Row
     Police_donnee = Trim(UCase(Worksheets("DONNES_EA").Range("Police_donnee").Value))
     While Cells(Num_Ligne, Range("Colonne_4").Column) <> ""
     ActiveSheet.Cells(Num_Ligne, Range("Colonne_4").Column).EntireRow.Clear
     Num_Ligne = Num_Ligne + 1
     Wend
 
     Call CONNEXION_PEGASE("xxx", "xxx", "xxx")
     ActiveSheet.Range("Colonne_1").Value = "Contrat"
     ActiveSheet.Range("Colonne_2").Value = "Support"
     ActiveSheet.Range("Colonne_3").Value = "Type"
     ActiveSheet.Range("Colonne_4").Value = "Frais de gestion"
     ActiveSheet.Range("Colonne_5").Value = "Taux de PAB net"
     ActiveSheet.Range("Colonne_6").Value = "Taux de bonus"
     ActiveSheet.Range("Colonne_7").Value = "Date mouvement"
     ActiveSheet.Range("Colonne_8").Value = "Montant mouvement"
     ActiveSheet.Range("Colonne_9").Value = "Motif mouvement"
     ActiveSheet.Range("Colonne_10").Value = "PM N-1 Pegase nette de fiscalité "
     ActiveSheet.Range("Colonne_11").Value = " PM N Pegase brut de fiscalité"
     ActiveSheet.Range("Colonne_12").Value = "Fiscalité"
     ActiveSheet.Range("Colonne_13").Value = "PM N Pegase nette de fiscalité"
 
 

      RECSET2.Open " select ev1.NO_POLICE, abs(sum(ev1.MT_BRUT)) as Fiscalite, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT," & _
    " ev2.TX_TAUX/100 as TAUX1,ev3.TX_TAUX/100 as TAUX2, srva1.MT_EA as MT_EA1, srva2.MT_EA as MT_EA2, (abs(sum(ev1.MT_BRUT))+srva2.MT_EA) as Brut_fis," & _
    " ev4.MT_BRUT,ev4.D_EFFET,ev4.LP_NATUR_FLUX" & _
    " from DB_EVENEMENT ev1 left join DB_SUPPORT sup on ev1.IS_SUPPORT=sup.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev2 on  ev1.NO_POLICE=ev2.NO_POLICE and ev1.IS_SUPPORT=ev2.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev3 on  ev1.NO_POLICE=ev3.NO_POLICE and ev1.IS_SUPPORT=ev3.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva1 on  ev1.NO_POLICE=srva1.NO_POLICE and ev1.IS_SUPPORT=srva1.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva2 on  ev1.NO_POLICE=srva2.NO_POLICE and ev1.IS_SUPPORT=srva2.IS_SUPPORT" & _
    " left join  DB_EVENEMENT ev4 on  ev1.NO_POLICE=ev4.NO_POLICE and ev1.IS_SUPPORT=ev4.IS_SUPPORT " & _
    " where ev1.NO_POLICE='" & Police_donnee & "' and  ev1.IS_CLASSE_EVT=365" & _
    " and ev1.LP_STATUT_EVT='DONE' and extract(year from ev1.D_EFFET)=2021 and" & _
    " ev2.IS_CLASSE_EVT=563 and extract(year from ev2.D_EFFET)>2020" & _
    " and ev3.IS_CLASSE_EVT=121 and ev3.N_EXERCICE_CPTA in (2021) and ev3.LP_STATUT_EVT^='ANNUL'" & _
    " and extract(year from srva1.D_VALO)=2020" & _
    " and extract(month from srva1.D_VALO)=12  and extract(day from srva1.D_VALO)=31" & _
    " and srva1.S_TYPE_SUPPORT='TXGAR'" & _
    " and extract(year from srva2.D_VALO)=2021" & _
    " and extract(month from srva2.D_VALO)=12  and extract(day from srva2.D_VALO)=31" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR'" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR' and extract(year from ev4.d_effet)=2021 and ev4.IS_CLASSE_EVT=39" & _
    " group by ev1.NO_POLICE, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT,ev2.TX_TAUX/100, ev3.TX_TAUX/100,srva1.MT_EA,srva2.MT_EA, ev4.MT_BRUT, ev4.D_EFFET, ev4.LP_NATUR_FLUX)", cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

      xlRow = Range("Colonne_1").Row + 1 + xlRow

   
 

     Do While Not RECSET2.EOF

     ActiveSheet.Cells(xlRow, Range("Colonne_1").Column).Value = RECSET2("NO_POLICE").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_2").Column).Value = RECSET2("CD_SUPPORT").Value


     Select Case RECSET2.Fields("IS_DEVISE").Value
     Case 46
     ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "EUR"
     Case Else
     ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "UC"
     End Select

     ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).Value = RECSET2("TAUX1").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).Value = RECSET2("TAUX2").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).Value = 0
     ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_7").Column).Value = RECSET2.Fields("D_EFFET").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).Value = RECSET2("MT_BRUT").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_9").Column).Value = RECSET2("LP_NATUR_FLUX").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).Value = RECSET2("MT_EA1").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).Value = RECSET2("Brut_fis").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).Value = RECSET2("Fiscalite").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).Value = RECSET2("MT_EA2").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).NumberFormat = "#,##0.00€"

      RECSET2.MoveNext
      xlRow = xlRow + 1

     Loop
 
      RECSET2.Close
      Call DECONNEXION_PEGASE


    End Sub

En fait, avec ma requête précédente, je n'arrivait pas à afficher les valeurs vides dans la colonne, mais la requête marchait bien pour les lignes avec les données non-vide. C'est un problème de l'affichage de RECSET vide avec des données à vide ou bien un problème dans la jointure ? Peut-être, lors de la jointure, je dois remplacer les données vide par "NC" pour pouvoir les afficher mais je ne sais pas comment faire.
Merci beaucoup pour votre aide.

dysorthographie · 27 Février 2022

NVL

docs.oracle.com

VBA_dev_Anne_Marie · 27 Février 2022

dysorthographie à dit:
NVL

docs.oracle.com

Merсi, je regarde 🙂

VBA_dev_Anne_Marie · 28 Février 2022

dysorthographie à dit:
NVL

docs.oracle.com

cela ne marche pas, j'obtiens le même résultat : que des lignes avec les données non manquantes. C'est énigmatique !

VB:

Public Sub Donnees_EA()
     Dim RECSET2 As New ADODB.Recordset
  
     Dim Num_Ligne As Long
     Dim Num_Ligne2 As Long
     Num_Ligne = Range("Chapeau").Row
     Police_donnee = Trim(UCase(Worksheets("DONNES_EA").Range("Police_donnee").Value))
     While Cells(Num_Ligne, Range("Colonne_4").Column) <> ""
     ActiveSheet.Cells(Num_Ligne, Range("Colonne_4").Column).EntireRow.Clear
     Num_Ligne = Num_Ligne + 1
     Wend
  
    Call CONNEXION_PEGASE("xxx", "xxx", "xxx")
     ActiveSheet.Range("Colonne_1").Value = "Contrat"
     ActiveSheet.Range("Colonne_2").Value = "Support"
     ActiveSheet.Range("Colonne_3").Value = "Type"
     ActiveSheet.Range("Colonne_4").Value = "Frais de gestion"
     ActiveSheet.Range("Colonne_5").Value = "Taux de PAB net"
     ActiveSheet.Range("Colonne_6").Value = "Taux de bonus"
     ActiveSheet.Range("Colonne_7").Value = "Date mouvement"
     ActiveSheet.Range("Colonne_8").Value = "Montant mouvement"
     ActiveSheet.Range("Colonne_9").Value = "Motif mouvement"
     ActiveSheet.Range("Colonne_10").Value = "PM N-1 Pegase nette de fiscalité "
     ActiveSheet.Range("Colonne_11").Value = " PM N Pegase brut de fiscalité"
     ActiveSheet.Range("Colonne_12").Value = "Fiscalité"
     ActiveSheet.Range("Colonne_13").Value = "PM N Pegase nette de fiscalité"
 
 

      RECSET2.Open " select ev1.NO_POLICE, abs(sum(ev1.MT_BRUT)) as Fiscalite, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT," & _
    " ev2.TX_TAUX/100 as TAUX1,ev3.TX_TAUX/100 as TAUX2, srva1.MT_EA as MT_EA1, srva2.MT_EA as MT_EA2, (abs(sum(ev1.MT_BRUT))+srva2.MT_EA) as Brut_fis," & _
    " NVL(TO_CHAR(ev4.MT_BRUT),'Not Applicable') as MT_BRUT, NVL(TO_CHAR(ev4.D_EFFET), 'Not Applicable') as D_EFFET,ev4.LP_NATUR_FLUX" & _
    " from DB_EVENEMENT ev1 left join DB_SUPPORT sup on ev1.IS_SUPPORT=sup.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev2 on  ev1.NO_POLICE=ev2.NO_POLICE and ev1.IS_SUPPORT=ev2.IS_SUPPORT" & _
    " left join DB_EVENEMENT ev3 on  ev1.NO_POLICE=ev3.NO_POLICE and ev1.IS_SUPPORT=ev3.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva1 on  ev1.NO_POLICE=srva1.NO_POLICE and ev1.IS_SUPPORT=srva1.IS_SUPPORT" & _
    " left join SRVEA.DB_SEA_SUPPORT_HISTO srva2 on  ev1.NO_POLICE=srva2.NO_POLICE and ev1.IS_SUPPORT=srva2.IS_SUPPORT" & _
    " left join  DB_EVENEMENT ev4 on  ev1.NO_POLICE=ev4.NO_POLICE and ev1.IS_SUPPORT=ev4.IS_SUPPORT " & _
    " where ev1.NO_POLICE='" & Police_donnee & "' and  ev1.IS_CLASSE_EVT=365" & _
    " and ev1.LP_STATUT_EVT='DONE' and extract(year from ev1.D_EFFET)=2021 and" & _
    " ev2.IS_CLASSE_EVT=563 and extract(year from ev2.D_EFFET)>2020" & _
    " and ev3.IS_CLASSE_EVT=121 and ev3.N_EXERCICE_CPTA in (2021) and ev3.LP_STATUT_EVT^='ANNUL'" & _
    " and extract(year from srva1.D_VALO)=2020" & _
    " and extract(month from srva1.D_VALO)=12  and extract(day from srva1.D_VALO)=31" & _
    " and srva1.S_TYPE_SUPPORT='TXGAR'" & _
    " and extract(year from srva2.D_VALO)=2021" & _
    " and extract(month from srva2.D_VALO)=12  and extract(day from srva2.D_VALO)=31" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR'" & _
    " and srva2.S_TYPE_SUPPORT='TXGAR' and extract(year from ev4.d_effet)=2021 and ev4.IS_CLASSE_EVT=39" & _
    " group by ev1.NO_POLICE, ev1.IS_DEVISE, sup.CD_SUPPORT, ev1.IS_SUPPORT,ev2.TX_TAUX/100, ev3.TX_TAUX/100,srva1.MT_EA,srva2.MT_EA, ev4.MT_BRUT, ev4.D_EFFET, ev4.LP_NATUR_FLUX", cnn_Pegase, adOpenDynamic, adLockBatchOptimistic

      xlRow = Range("Colonne_1").Row + 1 + xlRow

    
 

     Do While Not RECSET2.EOF

     ActiveSheet.Cells(xlRow, Range("Colonne_1").Column).Value = RECSET2("NO_POLICE").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_2").Column).Value = RECSET2("CD_SUPPORT").Value


     Select Case RECSET2.Fields("IS_DEVISE").Value
     Case 46
     ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "EUR"
     Case Else
     ActiveSheet.Cells(xlRow, Range("Colonne_3").Column).Value = "UC"
     End Select

     ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).Value = RECSET2("TAUX1").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_4").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).Value = RECSET2("TAUX2").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_5").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).Value = 0
     ActiveSheet.Cells(xlRow, Range("Colonne_6").Column).NumberFormat = "0.00%"
     ActiveSheet.Cells(xlRow, Range("Colonne_7").Column).Value = RECSET2.Fields("D_EFFET").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).Value = RECSET2("MT_BRUT").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_8").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_9").Column).Value = RECSET2("LP_NATUR_FLUX").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).Value = RECSET2("MT_EA1").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_10").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).Value = RECSET2("Brut_fis").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_11").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).Value = RECSET2("Fiscalite").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_12").Column).NumberFormat = "#,##0.00€"
     ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).Value = RECSET2("MT_EA2").Value
     ActiveSheet.Cells(xlRow, Range("Colonne_13").Column).NumberFormat = "#,##0.00€"

      RECSET2.MoveNext
      xlRow = xlRow + 1

     Loop
  
      RECSET2.Close
      Call DECONNEXION_PEGASE


    End Sub

VBA_dev_Anne_Marie · 2 Mars 2022

dysorthographie à dit:
Il semblerait que ce soit un problème récurrent chez Oracle pour ce qui concerne les sous requête !

Ça fait au-moins 15 que je n'ai pas travaillé sous Oracle ! Il faut que je mette à jour mon logiciel.

Refais un test en supprimant as FRM de la requête !

Bonjour,

En fait, je me demande, comment, dans le cas de Do While de ma requête actuelle, afficher les valeurs manquantes à "" comme je faisais dans le cas de IF :

Merci pour votre aide ! 🙂

Microsoft 365 ADODB : L'affichage des données manquantes

VBA_dev_Anne_Marie

XLDnaute Occasionnel

VBA_dev_Anne_Marie

XLDnaute Occasionnel

dysorthographie

XLDnaute Barbatruc

dysorthographie

XLDnaute Barbatruc

VBA_dev_Anne_Marie

XLDnaute Occasionnel

dysorthographie

XLDnaute Barbatruc

VBA_dev_Anne_Marie

XLDnaute Occasionnel

VBA_dev_Anne_Marie

XLDnaute Occasionnel

dysorthographie

XLDnaute Barbatruc

NVL

VBA_dev_Anne_Marie

XLDnaute Occasionnel

NVL

VBA_dev_Anne_Marie

XLDnaute Occasionnel

NVL

VBA_dev_Anne_Marie

XLDnaute Occasionnel

Discussions similaires

Microsoft 365 ADODB : L'affichage des données manquantes

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Occasionnel

Discussions similaires

Privacy & Transparency

Privacy & Transparency