VBA code to click on Excel icon from a webpage

user_16338135 0
Viewed 190

I am trying to automate the click on Excel icon action to download Excel data using VBA from the following link Financial Statement of a Firm

The below is a html code for that Excel icon

<div class="fri nav-item-right">
           <a onclick="changPeriod(2);" class="red" id="hang_quy" style="cursor:pointer;">Hàng quý</a>"|"
           <a onclick="changPeriod(1);" id="hang_nam" style="cursor:pointer;" class="">Hàng năm</a>
           <span class="curp"><img src="/images/icon-excel.jpg" alt="#" id="divPoints"></span>
 </div>

The below VBA code intends to trigger the Excel icon clicking action.

    URL = "http://finance.tvsi.com.vn/Enterprises/FinancialStatements?symbol=AAA" ' & Range("TickerName")
Set objIE = CreateObject("InternetExplorer.Application")
ThisWorkbook.Activate
objIE.Visible = True
objIE.navigate URL

Do While objIE.Busy
    Application.Wait DateAdd("s", 1, Now)
Loop

Set HTMLDoc = objIE.document
Set oHTML_Elements = HTMLDoc.getElementsByClassName("fri nav-item-right")

    For Each oHTML_Element In oHTML_Elements
        For Each oHTML_Element_Li In oHTML_Element.getElementsByTagName("span")
            oHTML_Element_Li.Children(0).Click
        Next
    Next

    Do While objIE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop

     objIE.Quit

What happened was the Web Element Tag Name "a" got clicked and not the Excel icon which belong to the Tag Name span.

What did I do wrong from the above code? and if possible please let me know your thoughts on getting this Excel icon Clicked.

Thank you for reading this post and I am looking forward to receiving your suggestion.

Best regards

BK

web web-scraping vba excel html
1 Answers

This is one of the efficient ways how you can download the excel file from that site. When you execute the following macro, you should get the file downloaded in the blink of an eye. You can format the cells later like the way you want.

Btw, the downloaded excel file comes up with an warning as soon as you open it, no matter whether you download the file manually or using the script. However, you can ignore that warning and open the file without having any issue.

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

    params = "symbol=AAA&YearView=2021&period=2&donvi=1000000"
    
    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
user_91897990
Related