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
|
Sub ImportAllData()
Dim DD As String, DF As String, S1 As String, S2 As String, CsvUrl As String, ActionUrl As String
Dim i As Integer, N As Integer
Dim Req1 As Object, Req2 As Object
Dim Tb
Application.ScreenUpdating = False
N = 3
DD = Format(DateSerial(2015, 1, 1), "yyyy-mm-dd")
DF = Format(Now, "yyyy-mm-dd")
CsvUrl = "http://www.swapsinfo.org/download/index/cdaf3d57f0bc68356ecf58c4235a8311c10f5d91/CDS%20Market%20Risk%20Activity"
Set Req2 = CreateObject("microsoft.xmlhttp")
Set Req1 = CreateObject("microsoft.xmlhttp")
For i = 1 To N
DoEvents
ActionUrl = "http://www.swapsinfo.org/charts/swaps/market-risk-activity?date_start=" & DD
ActionUrl = ActionUrl & "&date_end=" & DF & "&products=snre%2Cindex&suggest=&search=" & i
ActionUrl = ActionUrl & "&type=&submit=Update+Data"
With Req1
.Open "GET", ActionUrl, False 'ActionUrl: URL variable en fonction des dates DD et DF et de l'action i
.Send
S1 = .Responsetext
End With
If InStr(S1, "No data was returned. Please modify your filter parameters.") = 0 Then
With Req2
.Open "GET", CsvUrl, False 'CsvUrl: URL fixe de téléchargement du fichier csv
.Send
S2 = .Responsetext
End With
Tb = Split(S2, vbLf)
With ThisWorkbook
With .Sheets.Add(After:=.Sheets(.Sheets.Count)).Cells(1, 1).Resize(UBound(Tb) + 1)
.Value = Application.Transpose(Tb)
.TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, Comma:=True, FieldInfo:=Array(Array(1, 5), Array(2, 1), Array(3, 1))
End With
End With
End If
Next i
Set Req1 = Nothing
Set Req2 = Nothing
End Sub |