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
| Private Sub cmdPT_Click()
On Error GoTo Err_cmdPT_Click
Dim vS As String
Dim vI As Recordset
Dim vU As Recordset
Dim vRs As Recordset
Dim vTest As String
Dim vT As Integer
Dim vL As String
vS = OuvrirUnFichier(Me.Hwnd, "Parcourir", 1, "Microsoft Excel", "xls")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "PosteTechniqueImport", vS, True
Set vI = CurrentDb.OpenRecordset("PosteTechnique")
Set vRs = CurrentDb.OpenRecordset("PosteTechniqueImport")
While vRs.EOF = False
vTest = "SELECT * FROM PosteTechnique " & _
"WHERE pTNom = '" & [vRs]![pTNom] & "'"
vT = InStr(1, vRs!pTNom, "-")
vL = Mid(vRs!pTNom, vT + 1, 3)
Set vU = CurrentDb.OpenRecordset(vTest)
If vU.EOF Then
vI.AddNew
vI!pTNom = vRs!pTNom
vI!pTDesc = vRs!pTDesc
vI!pTZoneDeTri = vRs!pTZoneDeTri
vI!pTType = vRs!pTType
vI!pTClasse = vRs!pTClasse
vI!pTSecteur = vRs!pTSecteur
vI!pTUnit = vL
vI.Update
Else
vU.Edit
vU!pTDesc = vRs!pTDesc
vU!pTZoneDeTri = vRs!pTZoneDeTri
vU!pTType = vRs!pTType
vU!pTClasse = vRs!pTClasse
vU!pTSecteur = vRs!pTSecteur
vU!pTUnit = vL
vU.Update
End If
vRs.Delete
vRs.MoveFirst
Wend
Exit_cmdPT_Click:
Exit Sub
Err_cmdPT_Click:
MsgBox "Une erreure est survenue, veuillez verifier votre fichier !" & vbCrLf & "L'importation va s'arrêter"
Resume Exit_cmdPT_Click
End Sub |
Partager