本文主要介绍VBA访问网页获取数据。
实现效果:将网页中表的数据输出到excel中。
↓
VBA代码:
- Sub getResource()
- Columns("A:E").ClearContents
- Set HTML = CreateObject("htmlfile")
- With CreateObject("msxml2.xmlhttp")
- URL = "http://data.eastmoney.com/stock/tradedetail.html"
- .Open "get", URL, False
- .send
- HTML.body.innerhtml = StrConv(.responsebody, vbUnicode)
- Cells(1, 2) = HTML.body.document.getElementById("notice_Ddl").DefaultValue
- Set tr = HTML.all.tags("tr")
- j = 1
- For r = 0 To tr.Length - 1
- If Val(tr(r).Cells(0).innertext) + 1 = j Then
- j = j + 1
- Cells(j, 1) = tr(r).Cells(0).innertext
- Cells(j, 2) = tr(r).Cells(1).innertext
- Cells(j, 3) = tr(r).Cells(2).innertext
- Cells(j, 4) = tr(r).Cells(6).innertext
- Cells(j, 5) = tr(r).Cells(8).innertext
- End If
- Next
- End With
- End Sub