|
楼主 |
发表于 2017-1-11 12:10
|
显示全部楼层
本帖最后由 aman1516 于 2017-1-11 22:53 编辑
取数部分参照网上的一些例子写的代码,不知道问题出在哪里:
- <p>
- Sub shfe()
- Dim Htm0 As Object, XmlHttp As Object, url$, tb, m%, n%, COOKIE
- Set Htm0 = CreateObject("htmlfile")
- Set XmlHttp = CreateObject("MSXML2.XmlHttp")
- On Error Resume Next
- Application.ScreenUpdating = False
- Cells.ClearContents
- r = 1
- 'r = [a65536].End(xlUp).Row + 2
- url = "http://www.shfe.com.cn/statements/dataview.html?paramid=delaymarket_all"
- Dim Html As Object, oHttp As Object, list As Object
- Dim arr, i%, j%
- Set Html = CreateObject("htmlfile")
- Set oHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
- oHttp.Option(0) = "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"
- With oHttp
- .Option(6) = 0
- .Open "GET", "http://www.shfe.com.cn/statements/delaymarket_all.html", False
- .setRequestHeader "Content-Type", "application/xhtml+xml"
- .SEND
- 'COOKIE = Split(.getResponseHeader("Set-Cookie"), ";")(0) '第一次调用获取Cookie
- strText1 = .responsetext
-
- .Open "POST", "http://www.shfe.com.cn/statements/delaymarket_all.html", False
- .Option(6) = 1
- .setRequestHeader "Referer", "http://www.shfe.com.cn/statements/dataview.html?paramid=delaymarket_all" '设置正确的Referer
- '.setRequestHeader "Cookie", COOKIE '设置有效Cookie
- .setRequestHeader "Content-Type", "application/xhtml+xml"
- .setRequestHeader "Connection", "keep-alive"
- .SEND
- strText2 = .responsetext
- End With
-
- Cells(r, 1) = Format(Date, "yyyy年m月d日") & "上海期货价格"</p><p>ReDim arr(1 To 200, 1 To 13)
- Html.body.innerHTML = strText2
- Set list = Html.getElementsByTagName("table")(0)
- For i = 0 To list.Rows.Length - 1
- m = m + 1
- For j = 0 To list.Rows(1).Cells.Length
- arr(m, j + 1) = list.Rows(i).Cells(j).innerText
- Next
- Next
- Range("A2").Resize(m, 13) = arr
- Erase arr
- Application.ScreenUpdating = True
- End Sub
- </p>
复制代码
请大家帮忙改下……(已修正,谢谢!) |
|