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

VBA_dev_Anne_Marie · 27 Février 2022

dysorthographie à dit:
NVL

Merсi, je regarde

VBA_dev_Anne_Marie · 28 Février 2022

dysorthographie à dit:
NVL

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 Accro

dysorthographie

XLDnaute Accro

VBA_dev_Anne_Marie

XLDnaute Occasionnel

dysorthographie

XLDnaute Accro

VBA_dev_Anne_Marie

XLDnaute Occasionnel

VBA_dev_Anne_Marie

XLDnaute Occasionnel

dysorthographie

XLDnaute Accro

VBA_dev_Anne_Marie

XLDnaute Occasionnel

VBA_dev_Anne_Marie

XLDnaute Occasionnel

VBA_dev_Anne_Marie

XLDnaute Occasionnel