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 67 68 69 70 71 72 73 74 75 76 77 78 79 80
|
'Ouverture de l'application
Set appExcel = CreateObject("Excel.Application")
'Ouverture d'un fichier Excel
Set wbExcel = appExcel.Workbooks.Open(strFichierTmp)
'wsExcel correspond à la première feuille du fichier
Set wsExcel = wbExcel.Worksheets(1)
varsql = "DELETE FROM OPEDEP_TRANSACGPF"
Session.Database.Execute (varsql)
Session.Database.CommitTrans
'Dim djour As Date
djour = FormatDateTime(Now, vbGeneralDate)
i = 2
For Each minti In wsExcel.Range(wsExcel.Cells(2, 1), wsExcel.Cells(6536, 1).End(xlDown))
varsql = "INSERT INTO OPEDEP_TRANSACGPF"
varsql = varsql & "("
varsql = varsql & "OPE_COD, "
varsql = varsql & " LIB_OPE_COD, "
varsql = varsql & " OPE_COD2, "
varsql = varsql & " LIB_OPE_COD2, "
varsql = varsql & " OPE_COD3, "
varsql = varsql & " LIB_OPE_COD3, "
varsql = varsql & " OPE_COD4, "
varsql = varsql & " LIB_OPE_COD4, "
varsql = varsql & " CAT_VAL, "
varsql = varsql & " LIB_CAT_VAL, "
varsql = varsql & " DEP_COD, "
varsql = varsql & " DEP_LIB, "
varsql = varsql & " TYPE_TRANSAC, "
varsql = varsql & " TYPE_TRANSAC_LIB, "
varsql = varsql & " TYP_FLUX_GPF, "
varsql = varsql & " SENS_MVT, "
varsql = varsql & " INDIC_COE, "
varsql = varsql & " INDIC_SINFONI, "
varsql = varsql & " INDIC_PCB, "
varsql = varsql & " INDIC_GPF, "
varsql = varsql & " INDIC_CET, "
varsql = varsql & " NUM_VERSION, "
varsql = varsql & " DT_MAJ, "
varsql = varsql & " DT_DEB_VALID"
varsql = varsql & ")"
varsql = varsql & " VALUES( '" & Trim(wsExcel.Cells(i, 1))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 2))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 3))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 4))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 5))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 6))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 7))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 8))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 9))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 10))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 11))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 12))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 13))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 14))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 15))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 16))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 17))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 18))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 19))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 20))
varsql = varsql & "', '" & Trim(wsExcel.Cells(i, 21))
varsql = varsql & "', " & 1
varsql = varsql & ", " & djour
varsql = varsql & ", " & djour
varsql = varsql & ")"
On Error GoTo ErreurFatale
Session.Database.Execute (varsql)
i = i + 1
Session.Database.CommitTrans
ErreurFatale:
Exit For
Next |
Partager