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

Créer une discussion
Répondre

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
VBA_dev_Anne_Marie

VBA_dev_Anne_Marie

XLDnaute Occasionnel
Bonjour,

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

Voici les données attendues :
1645717441212.png


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 !
 
Trier par date Trier par votes
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.
 
Vote positif 0 Vote négatif
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)"
 
Vote positif 0 Vote négatif
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)"
Cliquez pour agrandir...
Bonjour,
Merci, maintenant j'ai une autre erreur :
1645958070093.png

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
 
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
dysorthographie à dit:
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
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 !
Cliquez pour agrandir...
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 :

1646237475730.png


Merci pour votre aide ! 🙂
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

VBA_dev_Anne_Marie
  • Question Question
Microsoft 365 VBA ADODB doublons
Réponses
1
Affichages
180
dysorthographie
dysorthographie
Retour