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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
|
Private Sub btnMajVan_Click()
'----------
'- Objet : Met à jour la table des VANs
'----------
strTexte = "Assurez-vous de fermer tous les fichiers excel <Maj des VAN> puis cliquez sur OK."
strTitre = "INFORMATION"
MsgBox strTexte, vbOKOnly, strTitre
Dim db As Database
Dim tmp As Variant 'mémoire tampon pour la màj de la table de van
Dim tmp_2 As Variant 'mémoire tampon pour la màj des seuils APD
Dim tmp_3 As Variant ' mémoire tampon pour le coût état
Dim i As Integer
Dim j As Integer
Dim l As Integer
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim strFileOutilTarif As String
Dim strSQL As String
Dim strSQL_2 As String
Dim strSQL_3 As String
strFileOutilTarif = DLookup("[Valeurtexte]", "tbl_Parametres", "[Id] = 'CalculMajEcheancier'")
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open(strFileOutilTarif)
Set xlSht = xlBook.Sheets("modèleZC")
DoEvents: DoEvents: DoEvents: DoEvents: DoEvents: DoEvents: DoEvents: DoEvents: DoEvents: DoEvents
xlSht.Select
With xlSht
Set db = CurrentDb
.Application.Run ("calcul_des_marges_ZC")
DoCmd.SetWarnings False
l = 24 ' Ligne
For i = 3 To 7
For j = 0 To i - 1
tmp = .Cells(l, 4).Value
tmp = Replace(tmp, ",", ".")
tmp_2 = .Cells(l, 5).Value
tmp_2 = Replace(tmp_2, ",", ".")
tmp_3 = .Cells(l, 6).Value
tmp_3 = Replace(tmp_3, ",", ".")
.Range("J30") = 249 - l
l = l + 1
strSQL = "UPDATE Tbl_echeancier SET [Val] = " & tmp & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
strSQL_2 = "UPDATE Tbl_echeancier SET [Seuil APD] = " & tmp_2 & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
strSQL_3 = "UPDATE Tbl_echeancier SET [Cout Etat] = " & tmp_3 & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL_2
DoCmd.RunSQL strSQL_3
Next j
Next i
For i = 8 To 20
For j = 0 To 6
tmp = .Cells(l, 4).Value
tmp = Replace(tmp, ",", ".")
tmp_2 = .Cells(l, 5).Value
tmp_2 = Replace(tmp_2, ",", ".")
tmp_3 = .Cells(l, 6).Value
tmp_3 = Replace(tmp_3, ",", ".")
.Range("J30") = 249 - l
l = l + 1
strSQL = "UPDATE Tbl_echeancier SET [Val] = " & tmp & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
strSQL_2 = "UPDATE Tbl_echeancier SET [Seuil APD] = " & tmp_2 & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
strSQL_3 = "UPDATE Tbl_echeancier SET [Cout Etat] = " & tmp_3 & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL_2
DoCmd.RunSQL strSQL_3
Next j
Next i
For i = 21 To 30
For j = 0 To 10
tmp = .Cells(l, 4).Value
tmp = Replace(tmp, ",", ".")
tmp_2 = .Cells(l, 5).Value
tmp_2 = Replace(tmp_2, ",", ".")
tmp_3 = .Cells(l, 6).Value
tmp_3 = Replace(tmp_3, ",", ".")
.Range("J30") = 249 - l
l = l + 1
strSQL = "UPDATE Tbl_echeancier SET [Val] = " & tmp & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
strSQL_2 = "UPDATE Tbl_echeancier SET [Seuil APD] = " & tmp_2 & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
strSQL_3 = "UPDATE Tbl_echeancier SET [Cout Etat] = " & tmp_3 & " " & _
" WHERE [Duree] = " & i & " and [Differe] = " & j & " ;"
DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL_2
DoCmd.RunSQL strSQL_3
Next j
Next i
DoCmd.SetWarnings True
End With
xlBook.Close Savechanges:=False
xlApp.Quit
DoCmd.OpenTable "tbl_echeancier"
strTexte = "Table Mise à jour aves succès !"
strTitre = "INFORMATION"
MsgBox strTexte, vbOKOnly, strTitre
End Sub |
Partager