如何用VBA从网页中提取表格数据
如何用VBA从网页中提取表格数据
提问于 2018-12-21 12:58:11
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 SubHTML代码
<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. & 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如何使用多个queryselector或任何其他常用方法提取表数据(包括季度结束日期)？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-12-21 15:14:17
似乎您必须单击公司信息选项卡才能显示该表
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53879404
复制相关文章
相似问题
腾讯云开发者
