VBA之股票信息
Sub myNZA() '利用IE,抓取深市股票涨跌数据 Sheets("SHEET2").Select Dim IE, IEDOM As Object Dim myTable, myTR As Object Set IE = CreateObject("InternetExplorer.Application") With IE .Visible = False .navigate "http://q.stock.sohu.com/" Do Until .readystate = 4 DoEvents Loop Set IEDOM = .document End With Cells.ClearContents Set myTable = IEDOM.getElementsByTagName("TABLE")(2) For Each myTR In myTable.Rows i = i + 1 For j = 0 To myTR.Cells.Length - 1 Cells(i, j + 1) = myTR.Cells(j).innertext Next Next Set IE = Nothing Set IEDOM = Nothing Set myTable = Nothing Set myTR = Nothing MsgBox "ok!" End Sub
代码的讲解:
1)Set IE = CreateObject("InternetExplorer.Application") 建立IE 的引用。
2).Visible = False
.navigate "http://q.stock.sohu.com/"
Do Until .readystate = 4
DoEvents
Loop
上述代码令浏览器可见,加载网址http://q.stock.sohu.com/,一直到加载完成,其中的DoEvents 是避免软死机的现象出现。
3)Set IEDOM = .document 提出网页文档数据
4)Set myTable = IEDOM.getElementsByTagName("TABLE")(2) 提取网页文档的第二个表格
5)For Each myTR In myTable.Rows
i = i + 1
For j = 0 To myTR.Cells.Length - 1
Cells(i, j + 1) = myTR.Cells(j).innertext
Next
Next
提取表格的数据到工作表。
6)Set IE = Nothing
Set IEDOM = Nothing
Set myTable = Nothing
Set myTR = Nothing
回收内存。对于回收内存的操作,建议大家利用,在大型的程序中,尤其是注意这点,内存占用过多会导致程序运行减缓。如果不释放内存就只能到END SUB时候再释放了,内存会不足。
,Best Wish 不负年华