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
| Sub poletII()
Dim oWbk As Excel.Workbook
Dim v1 As Variant, v2 As Variant, vT As Variant '1 = valeur 1; 2 = valeur2; T = les 2 valeurs
Dim i1 As Long, i2 As Long, iT As Long, j As Integer
Dim oSh As Excel.Worksheet, oRng As Excel.Range
Set oWbk = Application.Workbooks.Open("http://ichart.finance.yahoo.com/table.csv?s=UBSN.VX&d=3&e=28&f=2011&g=d&a=6&b=10&c=2006&ignore=.csv")
v1 = oWbk.Worksheets(1).UsedRange.Value
oWbk.Close False
Set oWbk = Application.Workbooks.Open("http://ichart.finance.yahoo.com/table.csv?s=%5ESSMI&d=3&e=30&f=2011&g=d&a=10&b=9&c=1990&ignore=.csv")
v2 = oWbk.Worksheets(1).UsedRange.Value
oWbk.Close False
'ajoute une feuille en dernier
ThisWorkbook.Worksheets.Add , ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Set oSh = ActiveSheet
oSh.Name = "Tables"
Set oRng = oSh.Range("A1:N" & UBound(v1, 1) + UBound(v2, 1) - 1)
vT = oRng.Value
'renseigner la ligne des titres
vT(1, 1) = v1(1, 1)
For j = 1 To 6
vT(1, j + 1) = v1(1, j + 1)
vT(1, j + 8) = v1(1, j + 1)
Next j
i1 = 2
i2 = 2
iT = 2
While (i1 <= UBound(v1, 1)) Or (i2 <= UBound(v2, 1))
'date : vt = sup(v1,v2)
If i1 <= UBound(v1, 1) Then
vT(iT, 1) = v1(i1, 1)
If i2 <= UBound(v2, 1) Then
If vT(iT, 1) < v2(i2, 1) Then vT(iT, 1) = v2(i2, 1)
End If
Else
vT(iT, 1) = v2(i2, 1)
End If
'écriture v1
If i1 <= UBound(v1, 1) Then
'si date(v1) = date(vT)
If v1(i1, 1) = vT(iT, 1) Then
For j = 1 To 6
vT(iT, j + 1) = v1(i1, j + 1)
Next j
i1 = i1 + 1
End If
End If
'écriture v2
If i2 <= UBound(v2, 1) Then
'si date(v2) = date(vT)
If v2(i2, 1) = vT(iT, 1) Then
For j = 1 To 6
vT(iT, j + 8) = v2(i2, j + 1)
Next j
i2 = i2 + 1
End If
End If
iT = iT + 1
Wend
oRng.Value = vT
Set oRng = Nothing
Set oSh = Nothing
Set oWbk = Nothing
v1 = Empty
v2 = Empty
vT = Empty
End Sub |
Partager