Je vais mettre un bout de code. Je veux mettre à jour des données que j'ai extraites de ma base Access vers un tableau Excel dans lequel j'ai apporté des modifications.
Private Sub Correction_J_Click()
Workbooks.Open Filename:="I:\dq\DQ 00615\AQP Tools\Stagiaire\daisy 2013 02 à 07\Amélioration de la base\Jalons.xls"
Worksheets("Attendu").Activate
Cells.Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.Save
Const adOpenStatic = 3
Const adLockOptimistic = 3
Extraction_Jalon (Ref)
Dim N As Integer
Dim car1 As String
Dim car2 As String
Dim I As Integer
Dim J As Integer
Dim Rf As String
Dim Num As String
Dim NumLi As String
Dim Jalon As String
Dim PC As String
Dim Indic As String
Dim AngInd As String
Dim Ass As String
Dim ac1 As String
Dim ac2 As String
Dim ac3 As String
Dim ac4 As String
Dim ac5 As String
Dim Com As String
Dim AngCom As String
Dim Proc As String
Dim IE As String
Dim Sup As String
Dim RPIF As String
Dim Form As String
Dim k As Integer
'Compter le nombre de caractères de "référence" noté N avec la fonction "Len"
car1 = ListReference.Text
N = Len(ListReference.Value)
Set Table = New ADODB.Recordset
Table.Open "Attendu", BaseAccess, adOpenStatic, adLockOptimistic
Table.MoveFirst
If Not Table.Fields("Référence") = "" Then
For J = 1 To Table.RecordCount
With Table.Fields("Référence")
car2 = Left(Table.Fields("Référence"), N)
Rf = Table.Fields("Référence")
Num = Table.Fields("N°")
With Table.Fields("N° de liaison")
If Not Table.Fields("N° de liaison") = "" Then
NumLi = Table.Fields("N° de liaison")
Else
NumLi = ""
End If
End With
With Jalon = Table.Fields("Jalon")
If Not Table.Fields("Jalon") = "" Then
Jalon = Table.Fields("Jalon")
Else
Jalon = ""
End If
End With
With Table.Fields("Point-clé")
If Not Table.Fields("Point-clé") = "" Then
PC = Table.Fields("Point-clé")
Else
PC = ""
End If
End With
With Table.Fields("Indicateur")
If Not Table.Fields("Indicateur") = "" Then
Indic = Table.Fields("Indicateur")
Else
Indic = ""
End If
End With
With Table.Fields("Anglais Indicateur")
If Not Table.Fields("Anglais Indicateur") = "" Then
AngInd = Table.Fields("Anglais Indicateur")
Else
AngInd = ""
End If
End With
With Table.Fields("Assurance")
If Not Table.Fields("Assurance") = "" Then
Ass = Table.Fields("Assurance")
Else
Ass = ""
End If
End With
With Table.Fields("Acteur 1")
If Not Table.Fields("Acteur 1") = "" Then
ac1 = Table.Fields("Acteur 1")
Else
ac1 = ""
End If
End With
With Table.Fields("Acteur 2")
If Not Table.Fields("Acteur 2") = "" Then
ac2 = Table.Fields("Acteur 2")
Else
ac2 = ""
End If
End With
With Table.Fields("Acteur 3")
If Not Table.Fields("Acteur 3") = "" Then
ac3 = Table.Fields("Acteur 3")
Else
ac3 = ""
End If
End With
With Table.Fields("Acteur 4")
If Not Table.Fields("Acteur 4") = "" Then
ac4 = Table.Fields("Acteur 4")
Else
ac4 = ""
End If
End With
With Table.Fields("Acteur 5")
If Not Table.Fields("Acteur 5") = "" Then
ac5 = Table.Fields("Acteur 5")
Else
ac5 = ""
End If
End With
With Table.Fields("Commentaires")
If Not Table.Fields("Commentaires") = "" Then
Com = Table.Fields("Commentaires")
Else
Com = ""
End If
End With
With Table.Fields("Comments")
If Not Table.Fields("Comments") = "" Then
AngCom = Table.Fields("Comments")
Else
AngCom = ""
End If
End With
With Table.Fields("Processus")
If Not Table.Fields("Processus") = "" Then
Proc = Table.Fields("Processus")
Else
Proc = ""
End If
End With
With Table.Fields("Support")
If Not Table.Fields("Support") = "" Then
Sup = Table.Fields("Support")
Else
Sup = ""
End If
End With
With Table.Fields("RPIF")
If Not Table.Fields("RPIF") = "" Then
RPIF = Table.Fields("RPIF")
Else
RPIF = ""
End If
End With
With Table.Fields("Formulaires")
If Not Table.Fields("Formulaires") = "" Then
Form = Table.Fields("Formulaires")
Else
Form = ""
End If
End With
With Table.Fields("Items Exigences")
If Not Table.Fields("Items Exigences") = "" Then
IE = Table.Fields("Items Exigences")
Else
IE = ""
End If
End With
End With
If car1 = car2 Then
With Worksheets("Attendu").Select
For I = 2 To Columns.Count
If Cells(I, 1) = "" Then
Cells(I, 1).Select
Cells(I, 1) = Rf
Cells(I, 2) = Num
Cells(I, 3) = 0
Cells(I, 4) = Jalon
Cells(I, 5) = PC
Cells(I, 6) = Indic
Cells(I, 7) = AngInd
Cells(I, 8) = Ass
Cells(I, 9) = ac1
Cells(I, 10) = ac2
Cells(I, 11) = ac3
Cells(I, 12) = ac4
Cells(I, 13) = ac5
Cells(I, 14) = Com
Cells(I, 15) = AngCom
Cells(I, 16) = Proc
Cells(I, 17) = Sup
Cells(I, 18) = RPIF
Cells(I, 19) = Form
Cells(I, 20) = IE
Exit For
End If
Next I
End With
J = J + 1
Else
J = J + 1
End If
Table.MoveNext
Next J
End If
Unload Me
ActiveWorkbook.Save
If MsgBox("Voulez-vous modifier le contenu de la plage ?", vbYesNo, "Demande de confirmation") = vbYes Then
With Worksheets("Attendu").Select
ActiveSheet.Unprotect
Range("A5:T1000").Select
Selection.Locked = False
Unload Me
'Mise à jour des données dans la base
For I = 2 To Columns.Count
If Cells(I, 1).Saved True Or Cells(I, 2).Saved True Or Cells(I, 3).Saved = True Or Cells(I, 4).Saved = True Or Cells(I, 5).Saved = True Or Cells(I, 6).Saved = True Or Cells(I, 7).Saved = True Or Cells(I, 8).Saved = True Or Cells(I, 9).Saved = True Or Cells(I, 10).Saved = True Or Cells(I, 11).Saved = True Or Cells(I, 12).Saved = True Or Cells(I, 13).Saved = True Or Cells(I, 14).Saved = True Or Cells(I, 15).Saved = True Or Cells(I, 16).Saved = True Or Cells(I, 17).Saved = True Or Cells(I, 18).Saved = True Or Cells(I, 19).Saved = True Or Cells(I, 20).Saved = True Then
If Table.Fields("Référence") "" And Table.Fields("Jalon") "" And Not Cells(I, 1) = "" Then
Table.Edit
Table.Fields("Référence") = Cells(I, 1).Value
Table.Fields("N°") = Cells(I, 2).Value
Table.Fields("N° de liaison") = Cells(I, 3).Value
Table.Fields("Jalon") = Cells(I, 4).Value
Table.Fields("Point-clé") = Cells(I, 5).Value
Table.Fields("Indicateur") = Cells(I, 6).Value
Table.Fields("Anglais Indicateur") = Cells(I, 7).Value
Table.Fields("Assurance") = Cells(I, 8).Value
Table.Fields("Acteur 1") = Cells(I, 9).Value
Table.Fields("Acteur 2") = Cells(I, 10).Value
Table.Fields("Acteur 3") = Cells(I, 11).Value
Table.Fields("Acteur 4") = Cells(I, 12).Value
Table.Fields("Acteur 5") = Cells(I, 13).Value
Table.Fields("Commentaires") = Cells(I, 14).Value
Table.Fields("Comments") = Cells(I, 15).Value
Table.Fields("Processus") = Cells(I, 16).Value
Table.Fields("Support") = Cells(I, 17).Value
Table.Fields("RPIF") = Cells(I, 18).Value
Table.Fields("Formulaires") = Cells(I, 19).Value
Table.Fields("Items Exigences") = Cells(I, 20).Value
Table.Update
End If
End If
Next I
MsgBox "L'attendu a été mis à jour dans la base !"
End With
End If
End Sub