Excel VBA 抓取网页 [英] Excel VBA Scrape Web Page

问题描述

我正在尝试抓取以下网页

您可以像 textbox onkeypress 事件处理程序那样重现这样的 XHR，并从响应中解析脚本名称，这里是示例 VBA 代码:

选项显式子 SmartGetQuoteData()Dim sScripID 作为字符串Dim sResp As StringDim sAllItems 作为字符串Dim sFirstCode 作为字符串Dim oXHR 作为对象Dim oDoc 作为对象Dim oBody 作为对象' 在此处设置您的代码sScripID = "500222"' 使 XHRSet oXHR = CreateObject("Microsoft.XMLHttp")oXHR.Open "GET", "http://www.bseindia.com/SiteCache/90D/SmartGetQuoteData.aspx?Type=EQ&text=" &sScriptID，假oXHR.SendsResp = oXHR.ResponseText' 将 HTML 转换为纯文本设置 oDoc = CreateObject("htmlfile")oDoc.Write sResp设置 oBody = oDoc.GetElementsByTagName("body")(0)sAllItems = oBody.InnerText' 拆分响应并获得第一部分sFirstCode = Split(sAllItems, "|")(0)'结果输出MsgBox sAllItems ' JCT ELECTRONICS LTD|JCTEL|500222MsgBox sFirstCode ' JCT ELECTRONICS LTD结束子

I'm trying to scrape the following webpage

http://www.bseindia.com/markets/equity/EQReports/StockPrcHistori.aspx?flag=0&expandable=7

The search box (which says Enter Scrip Name / Code / ID), is where I am having difficulty, I am able to set the value of the box to the scrip ID 500222 by using the following code,

IE.Document.All.Item("ctl00$ContentPlaceHolder1$GetQuote1_smartSearch").innerText = "500222"

However if you try entering the value 500222 in the search box manually you will get a drop down box which will be the name of the corresponding scrip. I can't however make it work via VBA. Any help would be great.

解决方案

On the webpage .onkeypress event handler function assigned to the textbox sends HTTP request and receives a response each time you press a key. Then it shows suggested items in drop down list. You don't need to figure out how does the handler function work. Just open URL from your question e. g. in Chrome, press F12 to open Developer Tools window, go to Network tab where all page XHRs listed, type some text into search box and you will see that new requests will be displayed. Click one of them, in Headers tab you can find Request URL:

You can reproduce such XHR as textbox onkeypress event handler does, and parse Scrip Name(s) from response, here is example VBA code:

Option Explicit

Sub SmartGetQuoteData()

    Dim sScripID As String
    Dim sResp As String
    Dim sAllItems As String
    Dim sFirstCode As String
    Dim oXHR As Object
    Dim oDoc As Object
    Dim oBody As Object

    ' set your code here
    sScripID = "500222"

    ' make XHR
    Set oXHR = CreateObject("Microsoft.XMLHttp")
    oXHR.Open "GET", "http://www.bseindia.com/SiteCache/90D/SmartGetQuoteData.aspx?Type=EQ&text=" & sScripID, False
    oXHR.Send
    sResp = oXHR.ResponseText
    ' convert HTML to plain text
    Set oDoc = CreateObject("htmlfile")
    oDoc.Write sResp
    Set oBody = oDoc.GetElementsByTagName("body")(0)
    sAllItems = oBody.InnerText
    ' split response and get 1st part
    sFirstCode = Split(sAllItems, "|")(0)

    ' result output
    MsgBox sAllItems ' JCT ELECTRONICS LTD|JCTEL|500222
    MsgBox sFirstCode ' JCT ELECTRONICS LTD

End Sub

这篇关于Excel VBA 抓取网页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！