VBA Internet Explorer应用程序为每个函数调用提供不同的结果 [英] VBA Internet Explorer Application gives different results for each function call

问题描述

我正在尝试在excel中自动执行一项任务，该任务需要打开一个网页，导航到该页面上的链接，然后单击第二页上的按钮以下载.xlsx文件.

I'm trying to automate a task in excel that requires opening a webpage, navigating to a link on that page, and then clicking on a button on the second page to download an .xlsx file.

我已经编写了一个脚本来执行此操作.但是，我从网页获得的响应并不总是相同的.特别是，有时会从第一页返回下载，有时会导航到第二页而不下载任何东西，一次或两次都完成.

I've written a script that should do this. However, the response I get from the webpage is not always the same. In particular, sometimes this will return a download from the first page and sometimes it will navigate to the second page and not download anything, once or twice it has done both.

我的感觉是，这与InternetExplorer.application完成请求所需的时间有关.不过，由于我告诉脚本等待IE.application完成其请求，因此我不知道如何解决此问题.

My sense is that this has to do with how long it takes for InternetExplorer.application to complete a request. I can't figure out how to troubleshoot this though, given that I tell the script to wait for IE.application to complete its request.

Sub DoBrowse2()

    'For Each lnk In Sheets("Sheet4").Hyperlinks
        'Range(lnk).Hy.Follow
        'Next

    Dim i As Long
    Dim URL As String
    Dim BaseURL As String
    Dim ToURL As String
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim HWNDSrc As Long
    Dim html As IHTMLDocument

    Set IE = CreateObject("InternetExplorer.Application")

    URL = Range("B2").Hyperlinks(1).Address

    IE.Navigate URL

    IE.Visible = True

    Application.StatusBar = URL & " is loading. Please wait..."

    Do While IE.ReadyState = 4: DoEvents: Loop
    Do Until IE.ReadyState = 4: DoEvents: Loop

    Application.StatusBar = URL & " Loaded"

    'Set html = IE.Document
    'Dim elements As IHTMLElementCollection
    'Set elements = html.all

    For Each itm In IE.Document.all
        If itm.className = "datagrid" Then
            For Each el In itm.Document.all
                Debug.Print "hello"
                If el.className = "ujump" And Right(el.innerText, 12) = "Constituents" Then
                    'Debug.Print el.innerText
                    ToURL = el.getAttribute("data-subset")
                    BaseURL = "http://datastream.thomsonreuters.com/navigator/search.aspx?dsid=ZUCH002&AppGroup=DSAddin&host=Metadata&prev=scmTELCMBR&s=D&subset="
                    ToURL = BaseURL & ToURL
                    'Debug.Print ToURL

                    IE.Navigate ToURL
                    IE.Visible = True

                    Do While IE.Busy
                        Debug.Print "in busy loop"
                        Application.Wait DateAdd("s", 1, Now)
                    Loop

                    GoTo end_of_for
                End If
            Next
        End If
    Next

end_of_for:

    Debug.Print ("STOP STOP STOP STOP STOP")

    Dim Script As String

    For Each itm In IE.Document.all
        If itm.className = "lgc excel" Then
            Debug.Print "hello world"
            Debug.Print itm.getAttribute("onclick")
            itm.Click

            Do While IE.Busy
                Debug.Print "app busy"
                Application.Wait DateAdd("s", 1, Now)
            Loop

            Exit For

        End If
    Next

End Sub

预先感谢您的帮助.

推荐答案

使用此方法确定IE页面是否已完全加载，始终必须同时满足以下两个条件:

Use this to determine whether IE page has been fully loaded, it always must be both of these conditions:

Do Until ie.ReadyState = 4 And ie.Busy = False
    DoEvents
Loop

即使上面的代码包含页面上的脚本，也可能在 ie.ReadyState = 4 And.Busy = False 条件得到满足后装入某些内容，这两种方法都很简单，但效率低下，并且可以使用不可靠的 Application.Wait ，或者您可以尝试在网站上查找有关加载状态的元素，并通过其可见属性等确定状态.

Even with code above if there are scripts on the page, some content may be loaded after ie.ReadyState = 4 And ie.Busy = False condition is met and either easy way, but inefficient and unreliable Application.Wait can be used or you can try finding elements on the website which inform about loading state and determine the state by their visible attributes etc.

您的部分代码是错误的，并导致无限循环:

Part of your code is wrong and causes an endless loop:

Do While IE.ReadyState = 4: DoEvents: Loop
Do Until IE.ReadyState = 4: DoEvents: Loop

它使DoEvents在readystate完成时触发，直到达到完成状态为止.

It makes DoEvents fire while readystate is complete and also until it reaches complete status.

缩小所有元素的集合:

For Each itm In IE.Document.all

在可能的情况下为特定集合提供更好的性能，例如:

to a specific collection for better performance when possible, for example:

For Each itm In IE.Document.GetElementsByTagName("div")

这篇关于VBA Internet Explorer应用程序为每个函数调用提供不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！