我已经写了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
...

09-25 16:59
查看更多