从网页单击 Excel 图标的 VBA 代码 - 第 2 部分 [英] VBA code to click on Excel icon from a webpage - part 2

问题描述

关注帖子 点击Excel图标下载数据Part1

还有另一个 Excel 图标可以在以下链接中下载历史股票价格 Historical我想用 VBA 自动化的股票数据.我能够让点击按钮工作，但它有一个窗口来确认保存数据，我无法通过这一步.这是该图标的 HTML 代码

There is another Excel icon to download historical stock price in the following link Historical Stock Data that I would like to automate with VBA. I am able to get the click button work but it has a window to confirm save data and I could not go pass this step. Here is a HTML code for that Icon

        <a class="btn-view white fri" onclick="changePage(1);">
                    <span class="curp"><i class="fa fa-angle-right" aria-hidden="true"></i>Xem</span>
                    <span onclick="ExelLichsugia();" class="curp"><img src="/images/icon-excel.jpg" alt="#"></span>
                </a>

我的 VBA 代码是这样的

and my VBA code is like this

    Set HTMLDoc = objIE.document
Set oHTML_Elements = HTMLDoc.getElementsByClassName("btn-view white fri")

    For Each oHTML_Element In oHTML_Elements
        For Each oHTML_Element_Li In oHTML_Element.getElementsByTagName("span")
                If oHTML_Element_Li.innerText <> "Xem" Then
                    oHTML_Element_Li.Children(0).Click

在第 1 部分中，SIM 有一个很棒的解决方案，我尝试查看源代码，尤其是ExelLichsugia"的 Script 区域.功能，看看我是否可以模仿它.未能完成.

In Part 1, SIM has a wonderful solution, and I tried to look into the source code especially the Script area of the "ExelLichsugia" function to see if I can mimic it. Failed to accomplish.

请你帮帮我好吗?非常感谢您花时间阅读和回复这篇文章

Will you please help me out? Thank you very much for your time reading and responding to this post

推荐答案

改编自 SIM 的答案，您只需要更改 URL 和 params 变量如下:

Adapting from SIM's answer, you only need to change the URL and params variable as below:

Sub DownloadExcel()
    Const URL$ = "http://finance.tvsi.com.vn/Data/LichsugiaExel?"
    Dim aBody As Variant, sPath$, params$

    params = "symbol=AAA&duration=d&startDate=25/07/2020&endDate=25/07/2021"
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL & params, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Safari/537.36"
        .send
        aBody = .responseBody
        sPath = ThisWorkbook.Path & "\" & "output.xls"
    End With
    
    With CreateObject("ADODB.Stream")
        .Type = 1
        .Open
        .write aBody
        .SaveToFile sPath, 2
        .Close
    End With
End Sub

在 Devtools 中，查找 Network Tab 下的网络流量，并在 Devtools 打开时单击 Excel 图标，您应该看到一个返回文档的请求，请求 URL 就是您所需要的.

In the Devtools, look for the network traffic under Network Tab and click the Excel icon while Devtools is open, you should see a request that returns a document, the Request URL is what you need.

这篇关于从网页单击 Excel 图标的 VBA 代码 - 第 2 部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！