时间戳

首页 > 解决方案 > 如何使用 VBA 代码从 HTTP 响应获取值到 Excel

excel - 如何使用 VBA 代码从 HTTP 响应获取值到 Excel

问题描述

我需要你的一点帮助才能从我得到的 HTTP 响应中获取股票价值（对应于股票名称）。收到的响应是 HTML 格式的，由于代码太长，我放了一部分。向下我提到了我正在使用的 VBA 代码，但它显示错误。请任何人帮助我..!! 注意：股票名称和值出现在“输入类型”中（第 5 行从下）

<!--    <table align="left">
    <tr>
    <td> -->
        <div class="table-responsive" style="width: 110%;">
          <table border="0" align="left" width="688" cellpadding="5" cellspacing="1" class="table_style table_111111" id="data_table">
                <thead>
                    <tr class="table_header"> 
                        <td width="15" class="center_align"><strong></strong></td>
                        <td width="50" class="left_align"><strong>Security </strong></td>
                        <td width="20" class="center_align"><strong>ME</strong></td>
                        <td width="15" class="center_align"><strong>Exch Code</strong></td>
                        <td width="15" class="center_align"><strong>Tradesmart Trends</strong></td>
                        <td width="20" class="center_align"><strong>IT</strong></td>
                        <td width="50" class="center_align"><strong>Expiry Date</strong></td>
                        <td width="50" class="center_align"><strong>Strike Price</strong></td>
                        <td width="10" class="center_align"><strong>Opt Type</strong></td>
                        <td width="32" class="center_align"><strong>Open Price</strong></td>
                        <td width="40" class="center_align"><strong>Buy Price</strong></td>
                        <td width="32" class="center_align"><strong>Buy Vol</strong></td>
                        <td width="41" class="center_align"><strong>Sell Price</strong></td>
                        <td width="32" class="center_align"><strong>Sell Vol</strong></td>
                        <td width="41" class="center_align"><strong>Last Price</strong></td>
                        <td width="28" class="center_align"><strong>High Price</strong></td>
                        <td width="41" class="center_align"><strong>Low Price</strong></td>
                        <td width="25" class="center_align"><strong>O.I.</strong></td>
                        <td width="20" class="center_align"><strong>CAs</strong></td>
                        <td width="50" class="center_align"><strong> % Change</strong></td>
                        <td width="50" class="center_align"><strong>Net Change</strong></td>

                    </tr>
                </thead>

<input type='hidden' id='first_watch' name='first_watch' value='current'/><tr class='tinside'><td class="left_align"><input type=checkbox name="stkcode" value="1157(NN)EQ" onClick="checkCheck(4);"></td><td class="left_align">APOLLOTYRE</td><td class="center_align">NSE</td><td class="center_align">163</td><td class="center_align"><A style='font-family: Arial, Helvetica, sans-serif; font-size: 11px; color: #31B404; font-weight: bold;' HREF="/itrade/user/recognia.exe?action=access"  >Bullish</A></td><td class="center_align">EQ</td><td class="center_align">-</td><td class="center_align">0.00</td><td class="center_align">- </td><td class="center_align">201.00</td><td class="center_align">0.00</td><td class="center_align"></td><td class="center_align">199.50</td><td class="center_align">1,990</td><td class="center_align">199.50</td><td class="center_align">203.85</td><td class="center_align">198.50</td><td class="center_align">    0</td><td class="center_align">XD</td>  <td class="center_align">    0.00</td>  <td class="center_align">    0.00</td>  </tr><tr class='tinside'><td class="left_align"><input type=checkbox name="stkcode" value="2441(NN)EQ" onClick="checkCheck(5);"></td><td class="left_align">SUNPHARMA</td><td class="center_align">NSE</td><td class="center_align">3351</td><td class="center_align"><A style='font-family: Arial, Helvetica, sans-serif; font-size: 11px; color: #31B404; font-weight: bold;' HREF="/itrade/user/recognia.exe?action=access"  >Bullish</A></td><td class="center_align">EQ</td><td class="center_align">-</td><td class="center_align">0.00</td><td class="center_align">- </td><td class="center_align">454.90</td><td class="center_align">0.00</td><td class="center_align"></td><td class="center_align">431.25</td><td class="center_align">66,412</td><td class="center_align">431.25</td><td class="center_align">455.00</td><td class="center_align">426.45</td><td class="center_align">    0</td><td class="center_align">XD</td>  <td class="center_align">    0.00</td>  <td class="center_align">    0.00</td>  </tr></table>
</div>
<!--</td>
</tr>
<tr>

这是VBA代码。我用来发送 POST Req 以获取股票价值作为回应。

Dim cook As String, bodyk As String, xmlhttp As Object, response As String
Dim getcookres As String, html As New HTMLDocument
Dim document As Object

Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
cook = Worksheets("Action").Range("X2").Value
bodyk = Worksheets("Action").Range("X1").Value


URL = "https://www.xxxxxxxx.com/itrade/user/watch.exe?action=C"
xmlhttp.Open "POST", URL, False
xmlhttp.setRequestHeader "Host", "www.xxxxxxxx.com"
xmlhttp.setRequestHeader "Connection", "keep-alive"
xmlhttp.setRequestHeader "Content-Length", "700"
xmlhttp.setRequestHeader "Cache-Control", "max-age=0"
xmlhttp.setRequestHeader "Origin", "https://www.xxxxxxxx.com"
xmlhttp.setRequestHeader "Upgrade-Insecure-Requests", "1"
xmlhttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
xmlhttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36"
xmlhttp.setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8"
xmlhttp.setRequestHeader "Referer", "https://www.xxxxxxxx.com/itrade/user/watch.exe?action=C"
xmlhttp.setRequestHeader "Accept-Encoding", "gzip, deflate, br"
xmlhttp.setRequestHeader "Accept-Language", "en-US,en;q=0.9"
xmlhttp.setRequestHeader "Cookie", cook

xmlhttp.send bodyk

html.body.innerHTML = xmlhttp.responseText

'Set document = html.getElementsByid("header").getElementsByid("container")(0).getElementsByClassName("table-responsive")(0).getElementsByid("first_watch")(1)

Set document = html.getElementsByClassName("tinside")
'Debug.Print html.body.innerHTML
Debug.Print document
Sheets(1).Cells(2, 1).Value = document.getElementsByTagName("td")(0).innerText

标签： excelvbahttpweb-scraping

解决方案

您可以使用 css 选择器来定位 td 元素

Dim nodeList As Object, i As Long
Set nodeList = html.querySelectorAll("#data_table tr.tinside td")
For i = 0 To nodeList.Length -1
    Debug.Print nodeList.item(i).innerText
Next

您可以使用 id 选择器的整个表

Dim ele As Object
Set ele  = html.getElementById("data_table")
' then loop the tr tags and then the td tags

推荐阅读