我已经写了VBA代码,从雅虎财务提取数据。在很大程度上,代码是在完成它的工作并提取我想要的内容。然而,它经常在不同公司之间循环时陷入困境,整个代码停止工作。我怀疑这可能是由于互联网连接在某个特定公司中循环时瞬间断开造成的。具体来说,我认为这发生在下面的代码块中:
For i = 1 To 14
.navigate "http://finance.yahoo.com/q/hp?s=" & Arrays(i) & "+Historical+Prices"
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop here
当internet试图导航ith公司时(数组(i))很可能中断,并导致代码在
Do While .ReadyState <> 4: DoEvents: Loop.ReadyState <>4
处无限循环。由于某些原因,即使在internet重新打开后,代码也无法工作,只是一直被困在试图执行ith数组的导航中。为什么会这样?有办法纠正吗?注意:如果我结束宏的执行并重新启动它,它将完美地工作。
这是完整的代码块供您参考。
Sub GetYahooFinanceTable()
Dim sURL As String, sResult As String
Dim oResult As Variant, oData As Variant, R As Long, C As Long, Arrays(30) As String, IE As Object
Set IE = CreateObject("InternetExplorer.Application")
For i = 1 To 14
Arrays(i) = Sheets("List of Companies").Cells(i, 1).Value
Next i
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = False
For i = 1 To 14
.navigate "http://finance.yahoo.com/q/hp?s=" & Arrays(i) & "+Historical+Prices"
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .document
Sheets("Data Pull Adj Close Yahoo").Cells(3, 7 * i) = .getElementById("yfs_l84_" & Arrays(i)).innerText
End With
Next i
End With
Set IE = Nothing
End Sub
最佳答案
我不确定这是否可行。但是你试过使用Sleep
函数吗?我试着这样插入:
带.document
Sheets("Data Pull Adj Close Yahoo").Cells(3, 7 * i) =
.getElementById("yfs_l84_" & Arrays(i)).innerText
Sleep 10000
...