Get list of stocks from a URL with excel VBA

Question

I want to download a file to my local worksheet from the site: https://www.bseindia.com/corporates/List_Scrips.aspx# below is my code which I tried after doing some research. challenge is, how to avoid the page resubmit (retry, cancel warning window) secondly, I need to download the 5mb file and extract to the current localworksheet.

Sub bsecode()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
ie.Visible = True
'To open the website
.navigate "https://www.bseindia.com/corporates/List_Scrips.aspx#"
 Do While ie.readyState <> 4
       Sleep 1000
   Loop
   ie.document.getElementsByName("ctl00$ContentPlaceHolder1$btnSubmit")(0).Click
   Sleep 1000
    Do While ie.readyState <> 4
    Sleep 1000
   Loop
  'To download the file
ie.navigate "javascript:__doPostBack('ctl00$ContentPlaceHolder1$lnkDownload','')"
'Do While .Busy: DoEvents: Loop
End With
End Sub

Hi QHarr, Just edited the post, please suggest. I am trying to get the file directly to the current worksheet. I apologize I am new to vba... — Kiran, Feb 22 '19 at 17:43
I have been looking at it. There is one problem I still have to resolve with current solution. — QHarr, Feb 24 '19 at 10:04
Thanks so much @QHarr, I am also trying from my end. but as a begineer, I am learning from guys like you. — Kiran, Feb 24 '19 at 16:21
Don't know, why the Question is getting downgraded, but with the above code, I am able to download the file (but its asking for a retry (which I need to do manually). and there after I am having difficulties getting into my current worksheet. (I think because the file itself is 5MB).. But really thanks a lot @QHarr for the help. — Kiran, Feb 24 '19 at 16:25
QHarr, any luck.. :), also I just gone through some of the SO question, @Tim Williams has got similar approach... — Kiran, Feb 25 '19 at 16:27
kind of @QHarr, here the link https://stackoverflow.com/questions/28899726/vba-handle-javascript-pop-up-with-ie-automation. but I am confused how it works in my case. Thanks again for the help — Kiran, Feb 25 '19 at 17:31
@QHarr, yeah I got that pop-up..is it a setting in IE or something that I need to upgrade or downgrade IE version to get rid of it. — Kiran, Feb 26 '19 at 17:36
@QHarr, its not downloading the file from the code, IE is opening and getting closed. we have a download option at top right after we click the submit button. here we need to import it to current worksheet. — Kiran, Feb 26 '19 at 18:21
This time no, I didn't got that, when I run even above code in question — Kiran, Feb 26 '19 at 18:31
ok @QHarr , but getting the option open, save popup, not retry and cancel... thanks a lot, I think we almost got there....... — Kiran, Feb 26 '19 at 18:36
Also, until the code halted at stop command, the file is not getting downloaded... anyway.. I will be waiting for your full code.. — Kiran, Feb 26 '19 at 18:43
I haven't written yet as busy with work. Will try to remember to have a look tonight. — QHarr, Feb 28 '19 at 15:59
sure, just thought, if you got any other hurdle , thanks for the information, @QHarr — Kiran, Feb 28 '19 at 19:17

score 1 · Accepted Answer · answered Feb 28 '19 at 20:47

I'm not a fan of sendkeys but the following works

Option Explicit
'VBE > Tools > References:
' Microsoft Internet Controls
Public Sub GetData()
    Dim ie As New InternetExplorer
    With ie
        .Visible = True
        .Navigate2 "https://www.bseindia.com/corporates/List_Scrips.aspx#"

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document

            'status
            .querySelector("[value='Active']").Selected = True 'Suspended,Delisted,Select

            'group
            .querySelector("[value='Select']").Selected = True ' "B ", "C " etc

            'industry
            .querySelector("[value='Advertising & Media']").Selected = True 'Agrochemicals etc

            'segment

            .querySelector("#ContentPlaceHolder1_ddSegment  [value='Equity']").Selected = True

            'Submit
            .querySelector("#ContentPlaceHolder1_btnSubmit").Click
            Const MAX_WAIT_SEC As Long = 5
            Dim t As Date
            While ie.Busy Or ie.readyState < 4: DoEvents: Wend

            Dim download As Object
            t = Timer
            Do
            On Error Resume Next
            Set download = .querySelector("#ContentPlaceHolder1_lnkDownload")
            On Error GoTo 0
            If Timer - t > MAX_WAIT_SEC Then Exit Do
            Loop While download Is Nothing
            If Not download Is Nothing Then
                download.Click
            End If

            Application.Wait Now + TimeSerial(0, 0, 10)
            Application.SendKeys "%N", True
            Application.SendKeys "%S", True
            Application.Wait Now + TimeSerial(0, 0, 10)
            Application.SendKeys "%O", True
        End With
        Stop
        .Quit
    End With
End Sub

It is a whole lot easier with selenium though you need to change the extension of the downloaded file from .tmp to .csv. After installing selenium basic be sure to go VBE > Tools > References and add a reference to Microsoft Scripting Runtime.

Option Explicit   
Public Sub MakeSelections()
    Dim d As WebDriver
    Set d = New ChromeDriver
    Const URL = "https://www.bseindia.com/corporates/List_Scrips.aspx#"
    With d
        .Start "Chrome"
        .get URL
               'status
            .FindElementByCss("#ContentPlaceHolder1_ddlStatus").AsSelect.SelectByText "Suspended"
            'group
            .FindElementByCss("#ContentPlaceHolder1_ddlGroup").AsSelect.SelectByText "Select"   ' "B ", "C " etc
            'industry
           .FindElementByCss("#ContentPlaceHolder1_ddlIndustry").AsSelect.SelectByText "Agrochemicals" 'Agrochemicals etc
            'segment
            .FindElementByCss("#ContentPlaceHolder1_ddSegment").AsSelect.SelectByText "Equity"

            .FindElementByCss("#ContentPlaceHolder1_btnSubmit").Click
            .FindElementByCss("#ContentPlaceHolder1_lnkDownload").Click
        .Quit
    End With
End Sub

Sorry for the delay. I prefer the selenium method for what should be obvious reasons. — QHarr, Feb 28 '19 at 20:53
np, thanks so much @QHarr, actually I can't use internet and can't browse for couple of days to test the code.Hope the above code also take care of copying the data to current worksheet. — Kiran, Mar 02 '19 at 08:59
yeah both solutions are working and giving pop up to download the file, but can help me how to copy the downloaded file to the existing workbook. btw sorry for the delayed reply. — Kiran, Mar 16 '19 at 20:34
I would ask a follow up question. The things I would be considering would be 1) if I can capture the file name before download use that to target the file in downloads, open and transfer data, or 2) do a count of files in download folder and when that increases by 1 (cos new file downloaded) grab the latest file (modified date) and open and transfer data. It is too much to write into the above. I have written similar answers before. — QHarr, Mar 16 '19 at 21:05
The filename will always be ListOfScrips.csv, @QHarr but if we download multiples files it will be like ListofScrips(1).csv ..etc.. — Kiran, Mar 17 '19 at 04:38
My advice would be to consider accepting this answer, have a go at coding the next part using my advice above. Some resources: https://www.google.com/search?safe=strict&rlz=1C1GCEB_enGB815GB815&ei=2mWNXLCLMPXixgOGkaL4DQ&q=vba+find+latest+file+in+folder&oq=vba+find+latest&gs_l=psy-ab.1.0.0l2j0i22i30l5.32445334.32448496..32451250...0.0..0.71.956.15......0....1..gws-wiz.......0i71j0i131j0i131i67j0i67.3Yeyvpt5B1g , https://stackoverflow.com/a/53912638/6241235 — QHarr, Mar 17 '19 at 06:11

Get list of stocks from a URL with excel VBA

1 Answers1