使用VBA将特定的web数据导入到excel

Question

我非常初学者的VBA编码场景(网页脚本是我的事情更多)，但我有一个基于excel的程序，我需要创建，将数据从内联网上的应用程序导入电子表格。这是我想建立的东西的要点..。在电子表格中，用户将输入以下信息:用户名、密码、客户帐号列表和日期范围。然后，用户将单击一个“命令按钮”，该按钮将实现以下操作：

1. 打开基于web的程序，登录(基于电子表格中输入的登录/密码)并导航到帐户搜索屏幕。
2. 在搜索字段中输入第一个客户帐号，然后单击“搜索”按钮导航到特定的客户帐户。
3. 导航到“搜索活动”屏幕，输入日期范围并单击“搜索活动”按钮。
4. 从活动表的特定列中提取数据，并将数据导入电子表格。
5. 如果有多页的数据，将有一个“下一个结果”按钮，则应该有一个循环来单击下一个结果按钮(如果存在)，并从每个页面中提取相同的数据列，直到该按钮不再存在(不再存在数据)。
6. 一旦没有更多的数据页(或者如果只有一页)，宏将返回并导航到帐户搜索屏幕，并对电子表格中键入的帐户列表中的每个帐户执行相同的操作，直到没有其他帐户为止。
7. 一旦完成(所有数据成功导入电子表格)，它应该关闭IE窗口。

这有点复杂，我意识到excel/vba绝对不是执行这些功能的最佳解决方案，但不幸的是，这正是我在这个实例中必须使用的。我已经能够拼凑出一些VBA来完成上面几乎所有的工作，我遇到的问题是循环遍历活动页面，而拖动数据就是行不通的(得到的错误范围很广，只会使我更加困惑)，有时它会从第一个工作表中提取数据，单击"next results“按钮，进入下一页并抛出一个错误，甚至通过两三页并抛出一个错误。这没有多大意义，但最常见的错误是“拒绝许可”。另外，这段代码目前只从一个帐户中提取数据，我希望一旦我为一个帐户工作，就可以简单地创建一个完整代码的循环，让它从帐号列表中删除，并对每个帐户执行相同的操作，直到完成。我已经被困在这几个星期，我真的准备抛出整件事，从零开始，任何帮助都会非常感谢！

下面是我到目前为止的代码。

Private Sub CommandButton1_Click()

    ' open IE, navigate to the desired page and loop until fully loaded
    Set IE = New InternetExplorerMedium
    my_url = "https://customerinfo/pages/login.jsp"
    my_url2 = "https://customerinfo/pages/searchCustomer.jsp"
    my_url3 = "https://customerinfo/pages/searchAccountActivity.jsp"

    With IE
        .Visible = True
        .navigate my_url
        Do Until Not .Busy And .readyState = 4
            DoEvents
        Loop
    End With

    ' Input the userid and password
    IE.document.getElementById("userId").Value = [B2]
    IE.document.getElementById("password").Value = [B3]

    ' Click the "Login" button
    IE.document.getElementById("action").Click
    Do Until Not IE.Busy And IE.readyState = 4
        DoEvents
    Loop

    ' Navigate to Search screen
    With IE
        .navigate my_url2
        Do Until Not .Busy And .readyState = 4
            DoEvents
        Loop
    End With

    ' Input the account number & click search
    IE.document.getElementById("accountNumber").Value = [B5]
    IE.document.getElementById("action").Click
    Do Until Not IE.Busy And IE.readyState = 4
        DoEvents
    Loop

    With IE
        .navigate my_url3
        Do Until Not .Busy And .readyState = 4
            DoEvents
        Loop
    End With

    'Input search criteria
    IE.document.getElementById("store").Value = [C7]
    IE.document.getElementById("dateFromMonth").Value = [C10]
    IE.document.getElementById("dateFromDay").Value = [B11]
    IE.document.getElementById("dateFromYear").Value = [B12]
    IE.document.getElementById("timeFromHour").Value = [B20]
    IE.document.getElementById("timeFromMinute").Value = [B21]
    IE.document.getElementById("dateToMonth").Value = [C15]
    IE.document.getElementById("dateToDay").Value = [B16]
    IE.document.getElementById("dateToYear").Value = [B17]
    IE.document.getElementById("timeToHour").Value = [B24]
    IE.document.getElementById("timeToMinute").Value = [B25]
    IE.document.getElementById("action").Click
    Do Until Not IE.Busy And IE.readyState = 4
        DoEvents
    Loop

    'Pulls data from activity search
    Dim TDelements As IHTMLElementCollection
    Dim TDelement As HTMLTableCell
    Dim r As Long, i As Long
    Dim e As Object

    Application.Wait Now + TimeValue("00:00:05")
    Set TDelements = IE.document.getElementsByTagName("tr")
    r = 0
    For i = 1 To 1
        Application.Wait Now + TimeValue("00:00:03")
        For Each TDelement In TDelements
            If TDelement.className = "searchActivityResultsOldContent" Then
                Sheet1.Range("E1").Offset(r, 0).Value = TDelement.ChildNodes(8).innerText
                r = r + 1
            ElseIf TDelement.className = "searchActivityResultsNewContent" Then
                Sheet1.Range("E1").Offset(r, 0).Value = TDelement.ChildNodes(8).innerText
                r = r + 1
            End If
        Next
        Application.Wait Now + TimeValue("00:00:02")
        Set elems = IE.document.getElementsByTagName("input")
        For Each e In elems
            If e.Value = "Next Results" Then
                e.Click
                i = 0
                Exit For
            End If
        Next e
    Next i

    Do Until Not IE.Busy And IE.readyState = 4
      DoEvents
    Loop
    IE.Quit

End Sub

Answer 1

所以，在你点击“下一步.”之后发生了什么元素？让我描述一下我遇到的一个问题。假设代码流如下所示：

1. 创建IE实例，并导航到某些URL，例如第一个搜索结果页面。
2. 检查页面是否已加载并准备就绪。等一等。
3. 创建目标元素的DispHTMLElementCollection集合，由.document.getElementsByTagName()检索，等等。
4. 循环通过集合的元素，做一些事情。
5. 点击“下一个.”元素。问题是，在某些情况下，由于某些JS或XHR处理，下一页无法在单击后立即开始下载。
6. 常规检查下一页是否已加载并准备就绪。此检查只允许立即执行进一步的代码，因为下一页的下载尚未在单击后立即启动，并且当前的现有页被错误地确定为下一页已下载并准备就绪。简单的几个秒延迟不能提供获得就绪页面的可靠方法。
7. 同样，错误地从现有页面而不是下一页创建元素的DispHTMLElementCollection集合。
8. 循环遍历创建的集合的元素。当循环正在进行时，下一页开始下载。集合仍然包含对对象的引用，但实际上该对象的页面已被卸载。因此，要么尝试访问卸载页面的元素，要么由于document对象没有响应，该操作会导致“权限拒绝”错误。

我的提示是避免单击“next .”，尝试从“Next.”的.href属性读取下一页URL。锚定<a>元素，并对该URL调用IE.navigate，然后检查页面就绪情况。

看一看example implementing that approach。

最有效的方法是使用XHR，如this、this和this。