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
| Sub nasdaq()
Columns("A:F").Delete
faire_ceci_ou_cela = colle_moi_ca_dans_le_sheet("http://www.nasdaq.com/symbol/abbv/historical", "10y|false|abbv", Cells(1, 1), True)
End Sub
Function getsource(url, Optional argmts = "")
With CreateObject("microsoft.xmlhttp")
.Open "POST", url, False
'entete de la requette obligatoire pour ce site(ne fonctionne pas sans )
.setRequestHeader "Accept", "*/*"
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "X-Requested-With", "XMLHttpRequest"
.setRequestHeader "Referer", "http://www.nasdaq.com/symbol/f/historical"
.setRequestHeader "Accept-Language", "fr-FR"
.setRequestHeader "Accept-Encoding", "gzip, deflate"
.setRequestHeader "User-Agent", "Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; WOW64; Trident/6.0)"
.setRequestHeader "Host", "www.nasdaq.com"
.setRequestHeader "Content-Length", "11"
.setRequestHeader "DNT", "1"
.setRequestHeader "Connection", "Keep - Alive"
.setRequestHeader "Cache-Control", "no-cache"
.send argmts
getsource = .responsetext
End With
End Function
Function colle_moi_ca_dans_le_sheet(url, argmts, cel, avec_style)
Dim lestyle
Set Fpage = CreateObject("htmlfile")
code = ""
With Fpage
.write getsource(url, argmts)
Set tablelig = .getelementsbytagname("tr")
For i = 2 To tablelig.Length - 1
tablelig(i).Children(0).innertext = Format(tablelig(i).Children(0).innertext, "dd/mm/yyyy")
Next
If avec_style Then
code = codestyle(Fpage)
Else: code = .body.outerhtml
End If
faire = .ParentWindow.clipboardData.SetData("text", code)
With Sheets(1): cel.Select: .Paste: End With
Selection.HorizontalAlignment = xlLeft
faire = .ParentWindow.clipboardData.ClearData("text")
End With
End Function
Function codestyle(pagehtml)
With pagehtml
code = .getelementsbytagname("table")(0).outerhtml
Set tablo = .getelementsbytagname("tr")
tablo(0).classname = "entete"
For i = 2 To tablo.Length - 1 Step 2
tablo(i).classname = "couleur1"
tablo(i - 1).classname = "couleur2"
Next
code = .body.outerhtml
End With
codestyle = codestyle & "<style type=""text/css"">" & vbCrLf
codestyle = codestyle & ".couleur1{background:#81DAF5;color:#585858;}" & vbCrLf
codestyle = codestyle & ".couleur2{background:#A9E2F3;color:#585858;}" & vbCrLf
codestyle = codestyle & ".entete{background:#0B2161;color:red;height:100px; border:solid 1px #0040FF;}"
codestyle = codestyle & "tr{ border:solid 1px #585858;}" & vbCrLf
codestyle = codestyle & "</style>" & vbCrLf
codestyle = codestyle & code
End Function |
Partager