1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
| Private Sub Commande0_Click()
Set oApp = CreateObject("excel.application")
Set oWkb = oApp.Workbooks.Open("U:\DR076\DRAPS\TRANSVERSE\EMPLOI\CDD\SUIVI CDD\Tableau de bord commun CDD 2021.xlsb", ReadOnly:=True)
Set oWSht = oWkb.Worksheets("Base")
'premier ligne ou tu commence ton import
i = 5
'pour éviter les messages lors de l'ajout des enregistrements
DoCmd.SetWarnings False
'tant que la cellule n'est pas vide
While oWSht.Range("A" & i).Value <> ""
If oWSht.Range("G" & i) > CDate("31/05/2021") Then
cSQL = "insert into [cdd]([Direction],[Agence/Service],[Matricule GA],[TH],[Nom Prénom],[coef début contrat],[Date embauche],[Date fin prévue Date sortie],[Date fin période essai],[Renouvellement surcroit],[Motif Sortie CDD],[ETP],[Type de contrat],[Motif Remplacement ou Surcroit],[Enveloppe impactée],[Emploi],[Code emploi],[Matricule Personne Remplacée],[Personne Remplacée],[Balma Vauguières]) values (" & Chr(34) & oWSht.Cells(i, 1) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 2) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 3) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 4) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 5) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 6) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 7) & Chr(34) & " , " & Chr(34) & oWSht.Cells(i, 8) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 9) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 10) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 11) & Chr(34) & "," & _
Chr(34) & oWSht.Cells(i, 12) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 13) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 14) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 15) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 16) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 17) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 18) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 19) & Chr(34) & "," & Chr(34) & oWSht.Cells(i, 22) & Chr(34) & ")"
'exécute la requète
DoCmd.RunSQL cSQL
End If
i = i + 1
Wend
CurrentDb.QueryDefs("MAJ GESTIONNAIRE CDD").Execute
DoCmd.SetWarnings True
Set oApp = CreateObject("excel.application")
Set oWkb = oApp.Workbooks.Open("U:\DR076\DRAPS\TRANSVERSE\EMPLOI\SUIVI DES MOUVEMENTS.xlsm", ReadOnly:=True)
Set oWSht = oWkb.Worksheets("MOUVEMENTS ")
'premier ligne ou tu commence ton import
i = 3
'pour éviter les messages lors de l'ajout des enregistrements
DoCmd.SetWarnings False
'tant que la cellule n'est pas vide
While oWSht.Range("G" & i).Value <> ""
If oWSht.Range("T" & i) > CDate("31/05/2021") Then
cSQL = "insert into [cdi]([DT],[MOTIF],[N°offre],[BDE],[DOCUMENTS],[MATRICULE],[NOM Prénom],[STATUT],[ANCIENNE AFFECTATION],[ANCIEN EMPLOI],[NOUVELLE AFFECTATION],[Code service],[NOUVEL EMPLOI],[Code emploi],[OUI/NON],[ANCIEN COEF/ ECHELON],[NOUVEAU COEF],[NOUVEAU ECHELON],[DATE D'EFFET]) values (" & Chr(34) & oWSht.Cells(i, 1) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 2) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 3) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 4) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 5) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 6) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 7) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 8) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 10) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 11) & Chr(34) & "," & _
Chr(34) & oWSht.Cells(i, 12) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 13) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 14) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 15) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 16) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 17) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 18) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 19) & Chr(34) & ", " & Chr(34) & oWSht.Cells(i, 20) & Chr(34) & ")"
'exécute la requète
DoCmd.RunSQL cSQL
End If
i = i + 1
Wend
CurrentDb.QueryDefs("MAJ GESTIONNAIRE CDI").Execute
DoCmd.SetWarnings True
MsgBox "Traitement terminé"
End Sub |
Partager