Excel VBA和HTML DOM:无法单击选项卡 [英] Excel VBA and HTML DOM: Can't Click Tab

问题描述

在Excel 2003 VBA中，我试图浏览到此网页...

In Excel 2003 VBA, I'm trying to browse to this web page...

https://www.google.com/财务?q = NYSE％3AWSO& fstype = ii& ei = cy30UrCEI8KKiALOPw

...并在其上单击以下两个选项卡:
-资产负债表"
-年度数据"

...and click these two tabs on it:
- "Balance Sheet"
- "Annual Data"

这些标签的HTML如下所示:
< a class = t>< b class = t>< b class = t"平衡表</b>//b</a>
和
< a id = annual class ="id-annual nac">年度数据</a>

Here's what the HTML for those tabs looks like:
<a class=t><b class=t><b class=t>Balance Sheet</b></b></a>
and
<a id=annual class="id-annual nac">Annual Data</a>

使用getElementById查找年度数据，然后单击它，效果很好.

Finding Annual Data with getElementById, and clicking on it, worked fine.

但是资产负债表没有ID.使用getElementByClass可能会导致多个类名称为" t "的元素产生歧义.而且似乎没有getElementByInnerText或getElementByInnerHTML.

But Balance Sheet has no ID. Using getElementByClass would be ambiguous with more than one element that has classname "t". And there doesn't seem to be a getElementByInnerText or getElementByInnerHTML.

因此，我遍历了所有元素，寻找了类名" t "，然后寻找了innerText"Balance Sheet".我能够找到该元素(其中三个，我想应该是它的结构)，但是单击它没有任何作用(我尝试单击所有三个元素).

So I looped through all elements, looking for classname "t", and then looking for innerText "Balance Sheet". I'm able to find the element (three of them, which I guess is expected given its structure), BUT clicking it has no effect (I tried clicking all three).

但是当我在Annual Data中尝试该方法时，效果很好.

But when I tried that method with Annual Data, it worked fine.

我需要做什么才能单击资产负债表"标签?我很确定我能找到它；只是没有成功点击它.

What do I need to do to be able to click the "Balance Sheet" tab? I'm pretty sure I'm finding it; just not successfully clicking on it.

这是我的代码:

Option Explicit

Sub TestMain()

    Dim strURL As String

    strURL = "https://www.google.com/finance?q=NYSE%3AWSO&fstype=ii&ei=cy30UrCEI8KKiALOPw"

    Call Main(strURL)

End Sub


Function Main(url_string As String)

    Dim oIE As Object, oDoc As Object, oElem As Object

    Set oIE = CreateObject("InternetExplorer.Application")

    oIE.Visible = True

    oIE.Navigate url_string
        Do While oIE.Busy
            Application.Wait DateAdd("s", 1, Now)
        Loop

    Set oDoc = oIE.document

    'Annual Data tab:
    Set oElem = GetElementsByClassNameAndInnerText(oDoc, "id-annual", True, "Annual Data", False)
    oElem.Click 'this works.

    'Quarterly Data:
    Set oElem = GetElementsByClassNameAndInnerText(oDoc, "id-interim", True, "Quarterly Data", False)
    oElem.Click 'this works.

    'Balance Sheet:
    Set oElem = GetElementsByClassNameAndInnerText(oDoc, "t", False, "Balance Sheet", True)
    oElem.Click 'does NOT work.

    'Income Statement:
    Set oElem = GetElementsByClassNameAndInnerText(oDoc, "t", False, "Income Statement", True)
    oElem.Click 'does NOT work.

    oIE.Quit
    Set oIE = Nothing

End Function


Public Function GetElementsByClassNameAndInnerText(html_doc As Object, _
                                                   class_name As String, is_classname_partial As Boolean, _
                                                   inner_text As String, is_innertext_partial As Boolean) As Object
    Dim oElem As Object
    Dim bClassNameIsMatch As Boolean, bInnerTextIsMatch As Boolean


    For Each oElem In html_doc.All
'        Debug.Print oElem.GetAttribute("class"), oElem.innertext
        bClassNameIsMatch = False 'init for each oElem.

        If is_classname_partial Then
            If InStr(oElem.GetAttribute("class"), class_name) > 0 Then
                bClassNameIsMatch = True
            End If
        Else
            'classname is exact:
            If oElem.GetAttribute("class") = class_name Then
                bClassNameIsMatch = True
            End If
        End If

        If bClassNameIsMatch Then
            bInnerTextIsMatch = False 'init for each oElem.

            If is_innertext_partial Then
                If InStr(oElem.innertext, inner_text) > 0 Then
                    bInnerTextIsMatch = True
                End If
            Else
                'innertext is exact:
                If oElem.innertext = inner_text Then
                    bInnerTextIsMatch = True
                End If
            End If

            If bInnerTextIsMatch Then
                If oElem.innertext = inner_text Then
                    Set GetElementsByClassNameAndInnerText = oElem
                    Exit For
                End If
            End If

        End If

    Next oElem
End Function

谢谢

格雷格

推荐答案

我找不到以编程方式单击这些选项卡的方法.尽管选项卡仅用于选择可见的分区，但所有数据都在页面上，因此可以下载数据(至少是到目前为止的表).在下面的函数中，将6个分区复制到一个文件中(可以通过编写一个函数来清除它).IE然后被重定向到该文件.

I can't find a way to click those tabs programatically. The data, at least the tables so far, can be downloaded though as the tabs are only used to select which division is visable, all the data is on the page. In the following function the 6 divisions are copied into a file (could be cleaned up by writing a function to do this). IE is then redirected to this file.

如果这是您要执行的操作，则最好查看Microsoft的HTML对象库或XML而不是IE，并使用get ot post来获取数据.

If this is the sort of thing you are looking to do you might be better off looking at Microsofts HTML Object Library or XML in stead of IE and using get ot post to get the data.

为此功能，我使用了对Microsoft HTML对象库，Microsoft Internet控件和Microsoft脚本运行时的引用.

I have used references to microsoft HTML object library, Microsoft internet controls and Microsoft scripting runtime for this function.

Function main(url_string As String)

    Dim oIE As InternetExplorer
    Dim oDivElement As HTMLDivElement
    Dim fsoObject As Scripting.FileSystemObject
    Dim FileHandle As Scripting.TextStream


    Set oIE = CreateObject("InternetExplorer.Application")

    oIE.Visible = True

    oIE.navigate url_string
    Do While oIE.Busy
       Application.Wait DateAdd("s", 1, Now)
    Loop

    Set fsoObject = New FileSystemObject
    Set FileHandle = fsoObject.CreateTextFile((ThisWorkbook.Path & "\Output.html"), True)


    Set oDivElement = oIE.document.getElementById("incinterimdiv")
    FileHandle.WriteLine ("Quarterly income")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine (oDivElement.innerHTML)
    Set oDivElement = oIE.document.getElementById("incannualdiv")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine ("Annual income")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine (oDivElement.innerHTML)
    Set oDivElement = oIE.document.getElementById("balinterimdiv")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine ("Quarterly balance")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine (oDivElement.innerHTML)
    Set oDivElement = oIE.document.getElementById("balannualdiv")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine ("Annual balance")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine (oDivElement.innerHTML)
    Set oDivElement = oIE.document.getElementById("casinterimdiv")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine ("Quarterly cash flow")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine (oDivElement.innerHTML)
    Set oDivElement = oIE.document.getElementById("casannualdiv")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine ("Annual cash flow")
    FileHandle.WriteLine ("<BR>&nbsp;<BR>")
    FileHandle.WriteLine (oDivElement.innerHTML)

    FileHandle.Close
    Set FileHandle = Nothing
    Set fsoObject = Nothing
    Set oDivElement = Nothing
    Set oIE = Nothing

End Function

这篇关于Excel VBA和HTML DOM:无法单击选项卡的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！