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
| Sub XmlHttpReq_abcbourse()
Const URL = "http://www.abcbourse.com/download/historiques.aspx"
Dim S() As String, T As String
Dim DateDebut As Date, DateFin As Date, sIsin As String, Ti As Single
Dim oXmlHttp As XmlHttp
Ti = Timer
DateDebut = Range("Date_debut").Value
DateFin = Range("Date_fin").Value
sIsin = Range("Isin").Value
Set oXmlHttp = New XmlHttp
'------------------------------------------- CORPS DE LA REQUETE ---------------------------------
'facultatif
T = "ctl00_BodyABC_ToolkitScriptManager1_HiddenField=%3B%3BAjaxControlToolkit%2C+Version%3D3.0.20229.20843%2C+Culture%3Dneutral%2C+"
T = T & "PublicKeyToken%3D28f01b0e84b6d53e%3Afr-FR%3A3b7d1b28-161f-426a-ab77-b345f2c428f5%3A865923e8%3A9b7907bc%3A411fea1c%3Ae7c87f07%3A91bd373d"
T = T & "%3Abbfda34c%3A30a78ec5%3A9349f837%3Ad4245214%3A8e72a662%3Aacd642d2%3A596d588c%3A77c58d20%3A14b56adc%3A269a19ae&"
T = T & "__EVENTTARGET=&" 'facultatif
T = T & "__EVENTARGUMENT=&" 'facultatif
T = T & "__VIEWSTATE=%2FwEPDwUIMzcyNDY5MjIPZBYCZg9kFgICBA9kFgQCCw9kFgJmDxYCHgdWaXNpYmxlZ2QCDQ9kFgICAQ8PFgIeBFRleHQ"
T = T & "FKUJhc2N1bGVyIHN1ciBsYSB2ZXJzaW9uIGNsYXNzaXF1ZSBkdSBzaXRlZGQYAQUeX19Db250cm9sc1JlcXVpcmVQb3N0QmFja0tleV9fFisFFmN0bDAwJEJvZHlBQkMkZXVy"
T = T & "b2xpc3QFHGN0bDAwJEJvZHlBQkMkYWN0aW9uc2luZGljZXMFGmN0bDAwJEJvZHlBQkMkYWN0aW9uc2luZHVzBRVjdGwwMCRCb2R5QUJDJGNvbXBsZXQFG2N0bDAwJEJvZHlBQk"
T = T & "MkY29tcGxldG5vd2FycgUSY3RsMDAkQm9keUFCQyRzcmRwBRhjdGwwMCRCb2R5QUJDJGluZGljZXNta3AFGWN0bDAwJEJvZHlBQkMkaW5kaWNlc3NlY3AFGGN0bDAwJEJvZHlBQk"
T = T & "MkZXVyb2xpc3RhcAUYY3RsMDAkQm9keUFCQyRldXJvbGlzdGJwBRhjdGwwMCRCb2R5QUJDJGV1cm9saXN0Y3AFGWN0bDAwJEJvZHlBQkMkZXVyb2xpc3R6ZXAFFGN0bDAwJEJv"
T = T & "ZHlBQkMkYWx0ZXJwBRFjdGwwMCRCb2R5QUJDJG1scAUUY3RsMDAkQm9keUFCQyR0cmFja3AFEWN0bDAwJEJvZHlBQkMkYnNwBRNjdGwwMCRCb2R5QUJDJG9ibDJwBRJjdGwwMCRCb"
T = T & "2R5QUJDJG9ibHAFFmN0bDAwJEJvZHlBQkMkd2FycmFudHMFF2N0bDAwJEJvZHlBQkMkb3Bjdm0zNjBwBRVjdGwwMCRCb2R5QUJDJHhjYWM0MHAFFmN0bDAwJEJvZHlBQkMkeHNiZj"
T = T & "EyMHAFFWN0bDAwJEJvZHlBQkMkeGNhY2F0cAUWY3RsMDAkQm9keUFCQyR4Y2FjbjIwcAUYY3RsMDAkQm9keUFCQyR4Y2Fjc21hbGxwBRVjdGwwMCRCb2R5QUJDJHhjYWM2MHAFF"
T = T & "mN0bDAwJEJvZHlBQkMkeGNhY2w2MHAFFWN0bDAwJEJvZHlBQkMkeGNhY21zcAUVY3RsMDAkQm9keUFCQyR4YmVsMjBnBRVjdGwwMCRCb2R5QUJDJHhhZXgyNW4FEWN0bDAwJEJvZH"
T = T & "lBQkMkZGp1BRJjdGwwMCRCb2R5QUJDJG5hc3UFFGN0bDAwJEJvZHlBQkMkc3A1MDB1BRZjdGwwMCRCb2R5QUJDJGdlcm1hbnlmBRJjdGwwMCRCb2R5QUJDJHVzYXUFEWN0bDAwJEJ"
T = T & "vZHlBQkMkdWtlBRJjdGwwMCRCb2R5QUJDJGJlbGcFE2N0bDAwJEJvZHlBQkMkaG9sbG4FFWN0bDAwJEJvZHlBQkMkaXRhbGlhaQUVY3RsMDAkQm9keUFCQyRsaXNib2FsBRJjd"
T = T & "GwwMCRCb2R5QUJDJGRldnAFFWN0bDAwJEJvZHlBQkMkb25lU2ljbwUTY3RsMDAkQm9keUFCQyRjYlllc0QdAEFMOzMYfTefgBBJ2ZEUaWDk&"
T = T & "__VIEWSTATEGENERATOR=0EFFD687&" 'facultatif
T = T & "__EVENTVALIDATION=%2FwEdADzeU%2B461dgjKeOy4euPx9%2BA2AGy%2BFRpYOz7XDkkbfjubp9UXI7RwI%2BukRHnd%2BAlDZ4TidDd"
T = T & "lkpo2nU8toG3jbaiO3bQR4gYjmmwS4y4ybsn33KxLZyHhf4Mje%2Fl2WxsZO8oboymX8K%2FawfpAYdrARkboi8y0D1aKa8YRDbuZk9hCVB%2BkE3gZYtipUze3jzNDNCMEBEMWFEO"
T = T & "c%2BNHEbSbw8FvNFYdXyAUuxt6%2B0jbZc5Nwm1GD0QooTm97LUU9GUKpnIOC%2Bu%2F2W%2BuWyGuXN02UbCiJJxmjp5mkW8DeM%2FocI5W8WmvXMDKwmYrvBKC7pnWUAtWcpMo"
T = T & "VsSmOCOdnlXIcoXu%2F%2Baf%2FiQDbqNtvcnRfkH2oiyB7RsbMXUq2lzc8dt95PPQBi3yzHLNr8sNaxE4jXoUvkYLOMZtGPFq8wOue%2BL3aL6QIV9VrUyAORU3dPeNj8oSuxs4f"
T = T & "VNVenj9bBwNMtd92XoZ0fgAFx0VYRpgUubx6aGZq7pW4kAu5m8fb5ci0zgmhY9T9Z7NZO7Gj9tArTr%2B8hOKHkjEHKgE6Cl%2BPlWP6CsBz2dyy933VqldEv71pnrWB5fl7SDH"
T = T & "6%2BLCeR6Cj3hBml1ipBDbFFYwrN937W%2FpOlYevFxpTuQO4S87Jds5qM1RyrZ1RzKjY7kpf1Uy1EsRjq0lzGo3UDCLR8Qzg%2BICOaGQP60Muea7Jt2Mvrk5dP50a3x3ndE82QK"
T = T & "f%2FstnRZsbrDvGsRZUo73a6kgCRfaABEjb6VehtduCyrNNbiEE%2Fszy7cIA2%2BGZ1fAM4FpZyQ0JQYbnRAQISh2SLDGw6kCjm8bengUhKB5UkNIenkLIxtz0C366YLNhLa"
T = T & "4%2Bher91UkHVTURjwLX%2FQYZkMXRVY7ahhNyymD9AkgSjvLPgJHTftqgsr%2FG5iTF4NQnaPIQKG4JEjJG69LLH4QzaePL67dWk8ZNAzb5On4rHEP9HywghYG2IhWARgjMSGRv"
T = T & "0qVhmbJ7tKAZQEwZOXR5bkA%2FrAjwH8gVCsdxCsXo10tHBh3HveAa6n1yqBv3gW0GFevOJ3B1BYZY9YGcYkXPO5BP5Glxc6DgHBR%2FbLiuAOggKUrocUhDbgaIk74hDc4yWeNpVf"
T = T & "1illXfalS4IP%2F8sbnyzuXvAG8jxH5CKE3kDDjxbgu9dzc8bjDscnPAvX10CapGgKKLX%2FbunaysEeSBMg0k95D%2Fr74nIW2lQs5QGOEcyRrWx3np81%2BkE7Z2rpWliSuX9"
T = T & "l6PQze7UQLbQ7Ri0FTVMSOPzduwVGJIAMkEhBJFgSlik%2FmRQFFRqx4FrCPfzOVdZTJAQMVyjQ%3D%3D&"
T = T & "ctl00%24txtAutoComplete=&" 'facultatif
T = T & "ctl00%24BodyABC%24strDateDeb=" & Replace(DateDebut, "/", "%2F") & "&" 'date de début
T = T & "ctl00%24BodyABC%24strDateFin=" & Replace(DateFin, "/", "%2F") & "&" 'date de fin
T = T & "ctl00%24BodyABC%24oneSico=on&" 'coche ISIN activée
T = T & "ctl00%24BodyABC%24txtOneSico=" & sIsin & "&" 'valeur de l'ISIN
T = T & "ctl00%24BodyABC%24Button1=T%C3%A9l%C3%A9charger&" 'activer le téléchargement
T = T & "ctl00%24BodyABC%24dlFormat=x&" 'liste des formats : Excel sélectionné
T = T & "ctl00%24BodyABC%24listFormat=isin" 'liste des codes : ISIN sélectionné
'--------------------------------------------------------------------------------------------------------
With oXmlHttp
.Open "POST", URL, False
'--------------------------------------- Entêtes dans IE ------------------------------------------------
' .setRequestHeader "Accept", "text/html, application/xhtml+xml, image/jxr, */*"
' .setRequestHeader "Accept-Encoding", "gzip, deflate"
' .setRequestHeader "Accept-Language", "fr-FR, fr; q=0.8, en-US; q=0.5, en; q=0.3"
' .setRequestHeader "Cache-Control", "no-cache"
' .setRequestHeader "Connection", "keep-alive"
' .setRequestHeader "Content-Length", "3708"
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
' .setRequestHeader "Cookie", "_ga=GA1.2.338785583.1464694728; __gads=ID=f06fd70b31924236:T=1464694747:S=ALNI_MYUr2IL9dK0mR0lYx403g1V5yJ47g; _gat=1; ASP.NET_SessionId=o032rswcsaxvmrsccqfmx2oo; axd=100057101507360010"
' .setRequestHeader "Host", "www.abcbourse.com"
' .setRequestHeader "Referer", "http://www.abcbourse.com/download/historiques.aspx"
' .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2486.0 Safari/537.36 Edge/13.10586"
On Error Resume Next
.send T
If .readyState = 4 Then
If .Status <> 200 Then MsgBox "Erreur de connexion !": Exit Sub
On Error GoTo 0
'Debug.Print .responseText
'si la requête ne ramène pas de résultat c'est le document html de la page qui est ramené
If InStr(.responseText, "</html>") > 0 Then MsgBox "Vérifiez les paramètres de la requête": Exit Sub
S = Split(.responseText, vbCrLf)
With Sheets("abcbourse")
.[A1].CurrentRegion.Clear
.Cells(1, 1).Resize(UBound(S)) = Application.Transpose(S)
.[A1].CurrentRegion.TextToColumns Semicolon:=True, DecimalSeparator:=";"
.[A1].CurrentRegion.Columns(2).FormulaLocal = .Columns(2).Value
.[A1].CurrentRegion.Columns.AutoFit
End With
Else
MsgBox "Erreur de chargement": Exit Sub
End If
End With
Set oXmlHttp = Nothing
MsgBox Timer - Ti
End Sub |
Partager