Call JavaScript from VBA

Question

Fisrt of all I'm not a programmer, I'm a finance student so I make an Excel file with macro where all the fields of https://www.nseindia.com/products/content/equities/equities/eq_security.htm will filled automatically. However I'm unable to click How to click "Download file in csv format". What will be the VBA code to click on the link?

Private Sub CommandButton1_Click()
Dim IE As New InternetExplorer
Dim oW As Worksheet: Set oW = ThisWorkbook.Worksheets("Sheet1")
Dim oEleCol As MSHTML.IHTMLElementCollection
Dim oEle As MSHTML.IHTMLElement


With IE
'Set IE = CreateObject("InternetExplorer.Application")

    ' Set IE
    .Visible = True
    'ShowWindow .hwnd, SW_SHOWMAXIMIZED

    ' Navigate to URL
    .Navigate "https://www.nseindia.com/products/content/equities/equities/eq_security.htm"

    ' Wait for page to be ready
    While .Busy
      DoEvents  'wait until IE is done loading page.
    Wend

    ' Set criteria
    .document.all("symbol").Value = oW.Range("B1")
    .document.all("series").Value = oW.Range("B2")
    .document.getElementById("rdDateToDate").Click

    ' Wait for page to be ready
    While .Busy
      DoEvents  'wait until IE is done loading page.
    Wend

    ' Set remaining criteria
    .document.all("fromDate").Value = oW.Range("B3")
    .document.all("toDate").Value = oW.Range("D3")

    ' Submit criteria
    .document.getElementById("submitMe").Click

    ' Wait for page to be ready
    While .Busy
      DoEvents  'wait until IE is done loading page.
    Wend

    ' Find the link to download file
    Set oEleCol = .document.getElementsByTagName("A")
    For Each oEle In oEleCol
        If oEle.innerHTML = "Download file in csv format" Then
            oEleCol.Click
            Exit For
        End If
    Next

    ' Wait for page to be ready
    While .Busy
      DoEvents  'wait until IE is done loading page.
    Wend

End With

End Sub

Note - Parameter of input will be Symbol - SBIN Series - EQ Time period from - 1/1/2016 Time period to - 1/12/2016

The HTML code for "download file in csv file" looks like this <span class="download-data-link"><a download="" target"_blank"="" style="cursor:pointer">Download file in csv format</a></span> — sks15
– sks15, Commented Sep 4, 2017 at 14:24

Moacir · Accepted Answer · 2017-09-04 14:35:09Z

1

I changed the IE to object and fixed oEleCol.Click to be oEle.Click and it worked. However it asks for confirmation to save, so you would have to either enable auto-confirming or look for how to implement Send Keys

Private Sub CommandButton1_Click()
Dim IE As Object
Dim oW As Worksheet: Set oW = ThisWorkbook.Worksheets("Plan1")
'Dim oEleCol As MSHTML.IHTMLElementCollection
'Dim oEle As MSHTML.IHTMLElement
Set IE = CreateObject("InternetExplorer.Application")

With IE
'Set IE = CreateObject("InternetExplorer.Application")

    ' Set IE
    .Visible = True
    'ShowWindow .hwnd, SW_SHOWMAXIMIZED

    ' Navigate to URL
    .Navigate "https://www.nseindia.com/products/content/equities/equities/eq_security.htm"

    ' Wait for page to be ready
    While .Busy
      DoEvents  'wait until IE is done loading page.
    Wend

    ' Set criteria
    .document.all("symbol").Value = oW.Range("B1")
    .document.all("series").Value = oW.Range("B2")
    .document.getElementById("rdDateToDate").Click

    ' Wait for page to be ready
    While .Busy
      DoEvents  'wait until IE is done loading page.
    Wend

    ' Set remaining criteria
    .document.all("fromDate").Value = oW.Range("B3")
    .document.all("toDate").Value = oW.Range("D3")

    ' Submit criteria
    .document.getElementById("submitMe").Click

    ' Wait for page to be ready
    While .Busy
      DoEvents  'wait until IE is done loading page.
    Wend

    ' Find the link to download file
    Set oEleCol = .document.getElementsByTagName("A")
    For Each oEle In oEleCol
        If oEle.innerhtml = "Download file in csv format" Then

            oEle.Click
            Exit For
        End If
    Next

    ' Wait for page to be ready
    While .Busy
      DoEvents  'wait until IE is done loading page.
    Wend

End With

End Sub

This way it works for me.

answered Sep 4, 2017 at 14:35

Moacir

6274 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Moacir Over a year ago

It worked fine for me. Try adding Application.Wait(Now + TimeValue("00:00:01")) before the 'Find the link to download file'

Moacir Over a year ago

Add ` Application.Wait Now + TimeValue("00:00:01") Application.SendKeys "%+s", True` at the end of the code, before the End Sub. See if that work

Moacir Over a year ago

The problem is that the button is, as far as I know, impossible to be identified as a variable. Is it possible to do all your macros and then just go manually and click yes? There are other tools, like Mouse recorder that could aid on this task

Moacir Over a year ago

%+s would be Alt+S, which is the hotkey to Save button on the download window. That is as close as it can get via VBA. Maybe you can try to configure Internet Explorer to automatically accept any downloads?

sks15 Over a year ago

Actually, I have to download thousands of data for my research that is why I was working for some automation to reduce time, anyway though than you so much for your support.

|

Collectives™ on Stack Overflow

Call JavaScript from VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related