时间戳

首页 > 解决方案 > 如何使用 VBA 从网页中提取表格数据

html - 如何使用 VBA 从网页中提取表格数据

问题描述

VBA 代码

Sub Test()
    Dim ie As Object
    Dim frm As Variant
    Dim element, submitInput As Variant
    Dim rowCollection, htmlRow As Variant
    Dim rowSubContent, rowSubData As Variant
    Dim anchorRange As Range, cellRng As Range
    Dim start
    Dim A As String
    Set ie = CreateObject("InternetExplorer.Application")
    ie.navigate "https://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=INFY"
    While ie.readyState <> 4: DoEvents: Wend
    ie.Visible = True
    A = ie.Document.getElementByID("tab20Content").innerText   
End Sub

代码

<div id="tab20Content" class="tabContent" style="display: block; width: 230px;"><table border="0" cellspacing="0" cellpadding="0"><tbody><tr><th class="normalText">Quarter Ended</th><th class="date">30 Sep 2018</th><th class="date">30 Jun 2018</th></tr><tr><td class="normalText" title="Total Income">Total Income</td><td class="number">18,29,700.00</td><td class="number">17,05,600.00</td></tr><tr><td class="normalText" title="Profit (+)/Loss (-) before Interest, Dep. &amp; Taxes">PBT</td><td class="number">5,25,100.00</td><td class="number">4,78,200.00</td></tr><tr><td class="normalText" title="Net Profit (+)/Loss (-) for the Period"

有一张桌子

Quarter Ended     30 Sep 2018     30 Jun 2018
Total Income      18,29,700.00    17,05,600.00
PBT               5,25,100.00     4,78,200.00
Net Profit/Loss   3,87,900.00     3,50,300.00

如何使用多个查询选择器或任何其他常用的方法提取表数据（包括季度结束日期）？

标签： htmlexcelvbaweb-scraping

解决方案

似乎您必须单击公司信息选项卡才能显示该表

Option Explicit

Public Sub GetInfo()
    Dim ie As InternetExplorer, clipboard As Object, ws As Worksheet, t As Date, hTable As HTMLTable
    Const MAX_WAIT_SEC As Long = 5
    Set ie = New InternetExplorer
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    With ie
        .Visible = True
        .navigate2 "https://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=INFY"

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

        'code to ensure table displayed.
        ie.document.querySelector("#tab6").Click

        t = Timer
        Do
            On Error Resume Next
            Set hTable = ie.document.querySelector("#tab20Content table")
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
        Loop While hTable Is Nothing
        If Not hTable Is Nothing Then
            clipboard.SetText hTable.outerHTML
            clipboard.PutInClipboard
            ws.Cells(1, 1).PasteSpecial
        End If
        .Quit
    End With
End Sub

推荐阅读