Excel VBA 激活 web 中的选择项并上网获取数据 [英] Excel VBA to activate selectitem in web and go web for data

问题描述

我对擅长 VBA 非常有兴趣.我尝试使用互联网上的所有潜在解决方案来激活 web 中的 selectitem 并获取数据表，但没有奏效.我的 Excel VBA 代码和 Javascript 如下.

Sub GetQuarterFinancials()Dim ie As Object, URL As StringURL = "https://www.marketwatch.com/investing/stock/aapl/financials"Set ie = CreateObject("internetexplorer.application")与即.可见 = 真.navigate 网址忙时做事件环形做 While .readyState <>4事件环形设置代码 = ie.document.getElementById("autocomplete_input")ticker.value = "FB".document.getElementById("investing_ac_button").点击Application.Wait (Now + TimeValue("00:00:05"))Set selectitem = ie.document.getElementsByTagName("select")对于每个 i 在选择项i.value = "/investing/stock/msci/financials/收入/季度"i.FireEvent ("onchange")接下来我结束于结束子<select style="float:right" onchange="window.location = this.options[selectedIndex].value;"><option value="/investing/stock/msci/financials" selected="selected">年度财务报告</option><option value="/investing/stock/msci/financials/income/quarter" >季度财务</option></选择><div style="clear:both"></div>

<div class="block"><h2>MSCI Inc. 的年度财务报告</h2>

解决方案

您可以使用 css 属性 = 值选择器 从下拉列表中定位适当的选项，然后选择它.

选项显式'VBE >工具 >参考:' 微软互联网控制公共子 MakeSelections()Dim ie 作为新的 Internet Explorer与即.可见 = 真.Navigate2https://www.marketwatch.com/investing/stock/aapl/financials"虽然 .Busy 或 .readyState <4:DoEvents:温德使用 .document.querySelector("#autocomplete_input").Value = "FB" '你可以通过在 url 中添加代码来避免搜索.querySelector("#investing_ac_button").点击结束于虽然 .Busy 或 .readyState <4:DoEvents:温德维度作为对象使用 .document做出错时转到 0设置 ele = .querySelector("[value^='/investing/stock/fb/financials/income/quarter']")出错时继续下一步循环而 ele 什么都不是.querySelector("[value^='/investing/stock/fb/financials/income/quarter']").Selected = True.querySelector(".financials select").FireEvent "onchange"停止'稍后删除我结束于.退出结束于结束子

I'm very fresh to excel VBA. I tried use all potential solutions in the internet for activate selectitem in web and get data table but it didn't work. My Excel VBA code and Javascript is below.

Sub GetQuarterFinancials()

Dim ie As Object, URL As String

    URL = "https://www.marketwatch.com/investing/stock/aapl/financials"
    Set ie = CreateObject("internetexplorer.application")

With ie
 .Visible = True
 .navigate URL

 Do While .Busy
   DoEvents
 Loop
 Do While .readyState <> 4
   DoEvents
 Loop

  Set ticker = ie.document.getElementById("autocomplete_input")
  ticker.value = "FB"

  .document.getElementById("investing_ac_button").Click
  Application.Wait (Now + TimeValue("00:00:05"))

  Set selectitem = ie.document.getElementsByTagName("select")
  For Each i In selectitem
           i.value = "/investing/stock/msci/financials/income/quarter"
           i.FireEvent ("onchange")
      Next i
 End With
End Sub





    <select style="float:right" onchange="window.location = this.options[selectedIndex].value;">
        <option value="/investing/stock/msci/financials" selected="selected">Annual Financials</option>
        <option value="/investing/stock/msci/financials/income/quarter" >Quarter Financials</option>
    </select>
    <div style="clear:both"></div>
</div>
    <div class="block">
        <h2>Annual Financials for MSCI Inc.</h2>

解决方案

You can use a css attribute = value selector to target the appropriate option from dropdown then select it.

Option Explicit

'VBE > Tools > References:
' Microsoft Internet Controls

Public Sub MakeSelections()
    Dim ie As New InternetExplorer
    With ie
        .Visible = True
        .Navigate2 "https://www.marketwatch.com/investing/stock/aapl/financials"

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document
            .querySelector("#autocomplete_input").Value = "FB" 'you could avoid search by adding ticker into url
            .querySelector("#investing_ac_button").Click
        End With

        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim ele As Object
        With .document
            Do
            On Error GoTo 0
            Set ele = .querySelector("[value^='/investing/stock/fb/financials/income/quarter']")
            On Error Resume Next
            Loop While ele Is Nothing
            .querySelector("[value^='/investing/stock/fb/financials/income/quarter']").Selected = True
            .querySelector(".financials select").FireEvent "onchange"
                Stop 'delete me later
        End With
        .Quit
    End With
End Sub

这篇关于Excel VBA 激活 web 中的选择项并上网获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！