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 60 61 62 63 64 65 66
|
Sub MAJ_BDD_Nomenclature()
Dim maBDD As DAO.Database
Dim monRS_test_exist As DAO.Recordset
Dim monRS_update_BDD As DAO.Recordset
Dim monWS As Workspace
Set monWS = DAO.Workspaces(0)
Set maBDD = monWS.OpenDatabase("gp", False, False, "ODBC")
MsgBox ("Ouvrez le fichier contenant l'extraction SAP brute (LVL 3 ) adéquate")
Application.FindFile
extractSAP = ActiveWorkbook.Name
Range("B14").Select
If ActiveCell.Offset(0, 2) = 1 Then
reference_xls = ActiveCell.Offset(0, 4)
designation_FR_xls = ActiveCell.Offset(0, 5)
designation_GB_xls = ActiveCell.Offset(0, 6)
qty_xls = ActiveCell.Offset(0, 7)
groupe_marchandise_xls = ActiveCell.Offset(0, 16)
indice_xls = ActiveCell.Offset(0, 18)
supplier_xls = ActiveCell.Offset(0, 19)
contrat_xls = Left(Range("F7"), 6)
requete_test = "select * from lvl1_items WHERE reference = " & Chr("34") & reference_xls & Chr("34") & _
" AND designation_GB = " & Chr("34") & designation_GB_xls & Chr("34") & _
" AND qty = " & Chr("34") & qty_xls & Chr("34") & _
" AND contrat = " & Chr("34") & contrat_xls & Chr("34") & ";"
Range("L5") = requete_test
Set monRS_test_exist = maBDD.OpenRecordSet(requete_test)
If Not monRS_test_exist.NoMatch Then 'la ligne n'existe pas
Set monRS_update_BDD = maBDD.OpenRecordSet("lvl1_items", dbOpenDynaset)
With monRS_update_BDD
.AddNew
.Fields(1) = reference_xls
.Fields(2) = designation_FR_xls
.Fields(3) = designation_GB_xls
.Fields(4) = qty_xls
.Fields(5) = groupe_marchandise_xls
.Fields(6) = "A" 'indice_xls
.Fields(7) = supplier_xls
.Fields(8) = contrat_xls
.Update
End With
Else
End If
End If
If ActiveCell.Offset(0, 2) = 2 Then
End If
If ActiveCell.Offset(0, 2) = 3 Then
End If
End Sub |
Partager