VBA_IE&HTTP

 1 Sub IEYahoo() ‘台湾网址较慢
 2     Dim T
 3     T = Timer
 4     Cells.ClearContents
 5     Application.StatusBar = "网络等待... ..."
 6     Const url As String = "https://tw.stock.yahoo.com/q/q?s=2330" ‘台积电
 7     
 8     Dim i As Integer, j As Integer ‘rowcount,colcount
 9     Dim IE As Object, Table As Object
10     
11     Set IE = CreateObject("internetexplorer.application")
12     
13     With IE
14         .Visible = True
15         .Navigate url
16         
17         Do While .Busy Or .readyState <> 4
18             DoEvents
19         Loop
20         Do While .document.readyState <> "complete"
21             DoEvents
22         Loop
23         
24         Set Table = .document.getElementsByTagName("table")(2)
25         ‘        Stop
26         With ActiveSheet
27             For i = 0 To Table.Rows.Length - 1 ‘ rows.count
28                 For j = 0 To Table.Rows(i).Cells.Length - 1 ‘cols.count
29                     .Cells(i + 1, j + 1) = Table.Rows(i).Cells(j).innerText
30                 Next j
31             Next i
32         End With
33         .Quit
34     End With
35     
36     Set Table = Nothing
37     Set IE = Nothing
38     MsgBox Format(Timer - T, "耗时0.00秒")
39     Application.StatusBar = False
40 End Sub

 1 Sub httpYahoo()
 2     Dim T
 3     T = Timer
 4     Cells.ClearContents
 5     Application.StatusBar = "网络等待... ..."
 6     Const url As String = "https://tw.stock.yahoo.com/q/q?s=2330" ‘台积电
 7     Dim i As Integer, j As Integer ‘rowcount,colcount
 8     Dim WinHttp As Object, Dom As Object, Table As Object
 9     
10     Set WinHttp = CreateObject("WinHttp.WinHttpRequest.5.1")
11     Set Dom = CreateObject("htmlfile")
12     
13     With WinHttp
14         .Open "GET", url, False
15         .send
16     
17         If .Status = 200 Then
18             Dom.body.innerHTML = .responsetext
19             Set Table = Dom.getElementsByTagName("table")(2)
20         End If
21     End With
22     
23     With ActiveSheet
24         For i = 0 To Table.Rows.Length - 1 ‘ rows.count
25             For j = 0 To Table.Rows(i).Cells.Length - 1 ‘cols.count
26                 .Cells(i + 1, j + 1) = Table.Rows(i).Cells(j).innerText
27             Next j
28         Next i
29     End With
30     
31     Set WinHttp = Nothing
32     Set Dom = Nothing
33     Set Table = Nothing
34     MsgBox Format(Timer - T, "耗时0.00秒")
35     Application.StatusBar = False
36 End Sub