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

问题描述

我正在尝试删除以下网页

您可以再现文本框onkeypress事件处理程序的XHR，并从响应中解析Scrip Name（s），这里是示例VBA代码：

  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 
 
'设置你的代码
 sScripID =500222
 
'make XHR 
设置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 
'将HTML转换为纯文本
设置oDoc = CreateObject（htmlfile）
 oDoc.Write sResp 
设置oBody = oDoc.GetElementsByTagName（body）（0）
 sAllItems = oBody.InnerText 
'拆分响应并获取第一部分
 sFirstCode =拆分（sAllItems，| ）（0）
 
'结果输出
 MsgBox sAllItems'JCT ELECTRONICS LTD | JCTEL | 500222 
 MsgBox sFirstCode'JCT ELECTRONICS LTD 
 
 End Sub 
  

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 Scrape网页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！