Excel VBA单击由javascript控制的表中的单元格 [英] Excel VBA click a cell in a table controlled by javascript

问题描述

我试图为excel创建一个网络查询，但我遇到了一个阻碍。我想出了如何登录到网页，并单击链接转到正确的portalID。但是，我无法获取具体的报告。

I am trying to create a web query for excel but I ran into a bit of a snag. I figured out how to log into the web page and click a link to go to the right portalID. However I am having trouble getting to specific reports.

要获取具体的报告，我需要点击表格中的链接，但似乎使用javascript。此表的HTML如下。在这个表中有大约10个单元格，但为了保持代码光我只包括前2个单元格。正如你可以看到，每个单元格都有自己的唯一ID，所以我可以轻松地过滤到正确的单元格元素与VBA。但我似乎无法找出如何实际点击单元格。

To get to specific reports, I need to click their link in a table but it appears to use javascript. The HTML for this table is below. There are about 10 cells in this table but to keep the code light I only included the first 2 cells. As you can see, each cell has its own unique ID so I can easily filter to the correct cell element with VBA. However I cannot seem to figure out how to actually click the cell.

<table submenu='1' border='0' cellpadding='6' cellspacing='0'
 id='ctl02n4c73594f1bf740b982340da2a792ff62_MainM' 
 class="ctl02n4c73594f1bf740b982340da2a792ff62Island"  
 style=' background:#141311; border-width:0px; width:100%; 
 cursor:Default;' onselectstart="javascript:igmenu_selectStart();" 
 onmouseover="javascript:igmenu_mouseover(this, event);" 
 onmouseout="javascript:igmenu_mouseout(this, event);" 
 onmousedown="javascript:igmenu_mousedown(this, event);" 
 onmouseup="javascript:igmenu_mouseup(this, event);"igLevel='0'>
  <tr>
    <td align='center' id='ctl02n4c73594f1bf740b982340da2a792ff62_1' 
     igTag='4eddf201-f6ed-4e11-b2ff-6a742128909c'  
     class="n_4c73594f_1bf7_40b9_8234_0da2a792ff62_Class"style=" 
     white-space: nowrap; ;" igHov='n_4c73594f_1bf7_40b9_8234_0da2a792ff62_Hover' 
     igTop='1'>Welcome</td>
    <td align='center' id='ctl02n4c73594f1bf740b982340da2a792ff62_2' 
     igTag='e0d4474e-87f8-42cc-ab47-38751029052d'  
     class="n_4c73594f_1bf7_40b9_8234_0da2a792ff62_Class"style=" 
     white-space: nowrap; ;" igHov='n_4c73594f_1bf7_40b9_8234_0da2a792ff62_Hover' 
     igTop='1'>Dashboard</td>
  </tr>
</table>

这是我的vba要导航到我需要单击的单元格。它看起来像我需要调用表的onmouseup事件。我如何链接我需要的单元格与表的onmouseup事件？

Here is my vba to direct to the cell I need to click. It looks like I need to invoke the "onmouseup" event of the table. How would I "link" the cell I need with the onmouseup event of the table?

Sub clickTable(toFind As String, ie As Object)
'"toFind" is the ID of the cell to find, ie is the IE application
Dim ieDoc As Object 'ieDocDocument
Dim tdCollection As Object 'table that has the javascript events and contains
                            the cells I want to click
Dim cell As Object 'specific "clickable" cell in the table to "click"

    Set ieDoc = ie.document
    Set tdCollection = ieDoc.getelementbyID("ctl02n4c73594f1bf740b982340da2a792ff62_MainM").getElementsByTagName("td")
    For Each cell In tdCollection
        If cell.ID = toFind Then
            cell.Click
            Exit Sub
        End If
    Next

End Sub

推荐答案

好吧，在搜索其他vba / javascript问题时，我真的想出来了我自己！

Well, while searching other vba/javascript questions I actually figured it out on my own!

我浏览了所有的javascript代码，看看HTML事件是如何绑定到javascript和肯定够了，onmouseup要求onmousedown需要onmouseover。这里我试图执行最终的交互，甚至没有想到打破JavaScript代码。

I went through all the javascript code to see how the HTML events tied into the javascript and sure enough, "onmouseup" requires "onmousedown" which requires "onmouseover". Here I was trying to execute the final interaction and didn't even think of breaking down the javascript code.

因此，如果任何人遇到一个问题，试图点击单元格在JavaScript控制表菜单中，您可能需要执行鼠标与页面交互的所有方式，这取决于JavaScript的编写方式。

So, if anyone else runs into an issue trying to click a cell in a javascript controlled table menu, you may need to execute all the ways the mouse interacts with the page depending on how the javascript was written.

这是我的最终代码

Sub clickTable(toFind As String, ie As Object)
'toFind is the ID of the element to find, ie is the IE application
Dim ieDoc As Object 'ieDocDocument
Dim tdCollection As Object 'table that has the javascript attributes and contains the element I want to click
Dim cell As Object 'specific "clickable" cell in the table to test

        Set ieDoc = ie.document
        Set tdCollection = ieDoc.getelementbyID("ctl02n4c73594f1bf740b982340da2a792ff62_MainM").getElementsByTagName("td")
        For Each cell In tdCollection
            If cell.ID = toFind Then
                cell.FireEvent ("onmouseover")
                cell.FireEvent ("onmousedown")
                cell.FireEvent ("onmouseup")
                Exit Sub
            End If
        Next

    End Sub

这篇关于Excel VBA单击由javascript控制的表中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！