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时候再释放了，内存会不足。