CODEWITHSUNDEEP
htmlexcelvbaweb-scraping
Bom Kem
Bom Kem

Reputation: 23

VBA code to click on Excel icon from a webpage - part 2

Following on the post Click on Excel Icon to download data Part1

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>

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

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

Upvotes: 0

Views: 256

Answers (1)

Raymond Wu
Raymond Wu

Reputation: 3387

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

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.

Upvotes: 1

Related Questions