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
|
Sub Copie_fich()
mpath = "C:\div\"
mfich1 = "Contrôle benchmarké1.xls"
mfich2 = "Tableau de contrôle.xls"
mfich3 = "Evl_09_05.xls"
Application.DisplayAlerts = False
Workbooks.Open Filename:=mpath & mfich2, updatelinks:=False
Application.DisplayAlerts = True
Sheets("recap v2 - b").Copy Before:=Workbooks(mfich1).Sheets(1)
Windows(mfich2).Activate
Sheets("recap v2 - a").Copy Before:=Workbooks(mfich1).Sheets(1)
Workbooks(mfich2).Close SaveChanges:=False
Windows(mfich1).Activate
Sheets("recap v2 - a").Activate
Columns("W:BL").Select
Selection.Delete Shift:=xlRight
Range("A3").Select
Sheets("recap v2 - b").Activate
Columns("W:BL").Select
Selection.Delete Shift:=xlRight
Range("A3").Select
Workbooks.Open Filename:=mpath & mfich3
Sheets("Evlsav1").Activate
Range("G31:G40").Select
Selection.NumberFormat = "#,##0.00"
'Range("I31:I34").Select
' Selection.NumberFormat = "dd/mm/yyyy"
'Range("L31:L34").Select
' Selection.NumberFormat = "dd/mm/yyyy"
Call Separe
Windows(mfich1).Activate
Sheets("recap v2 - b").Activate
'Nettoyer un plage de cellule
'Range(Cells(21, 8), Cells(23, 9)).Select
'Selection.ClearContents
'Selection.NumberFormat = "#,##0.00"
'Range(Cells(6, 8), Cells(16, 9)).Select
'Selection.ClearContents
'Selection.NumberFormat = "#,##0.00"
Sheets("recap v2 - a").Activate
Call Affect("Recap V2 - a")
Sheets("recap v2 - b").Activate
Call Affect("Recap V2 - b")
End Sub
Sub Separe()
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 2), Array(10, 1), Array(11, 1), Array(12, 2), Array(13, 1 _
), Array(14, 1), Array(15, 1))
End Sub
Function chargement_cours_j_moins1(code_isin) As Double
Dim code_prod As String
Dim cours As Double
Dim ptr As Variant
cours = 0
Set ptr = CreateObject("ADODB.Recordset")
ptr.Open "SELECT DISTINCT _CODEPRODUI FROM omega.com.produit WHERE _ISIN = '" & code_isin & "'", Mybd
Do While Not ptr.EOF
code_prod = ptr("_CODEPRODUI")
ptr.movenext
Loop
ptr.Close
ptr.Open "SELECT _COURSCLOSE FROM omega.com.prixhist WHERE _CODEPRODUI = '" & code_prod & "' ORDER BY _DATE DESC", Mybd
Do While Not ptr.EOF
cours = ptr("_COURSCLOSE")
Exit Do
Loop
chargement_cours_j_moins1 = cours
End Function
Sub Affect(nom_feuille)
Dim i
For i = 6 To 500
If (Sheets(nom_feuille).Range("A" & i)) <> "" Then
mont = chargement_cours_j_moins1(Sheets(nom_feuille).Range("A" & i))
Sheets(nom_feuille).Range("h" & i) = mont
Else
End If
Next i
End Sub |
Partager