Download CSV file

[lawvictor]

Hi everyone,

I am trying to download csv file from the nse equity stock watch ("https://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm"). I download it manually by clicking on "Download in csv" text on to the right of the webpage. Is there a way how I could automate it using excel vba. i have small bit of code but it isn't working. Really appreciate if someone could provide a solution. It is failing at

****** (strFile = ieDoc.getElementById("datacsv") )**** below is the code for the same. the code returns ["Object'] for strfile. and hence it fails to Open,

Dim fileStream As ADODB.Stream
Dim xmlHTTP As MSXML2.XMLHTTP60
Dim strURL As String
Dim strFile
Dim ie As InternetExplorer, ieDoc As HTMLDocument

strURL = "https://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm"

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.Navigate strURL

Do
    DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE

Set ieDoc = ie.document

strFile = ieDoc.getElementById("datacsv")

Set xmlHTTP = New MSXML2.XMLHTTP60
xmlHTTP.Open "GET", strFile
xmlHTTP.send

 

[Marc L]

Hi !

Error occurs with this Id which not exists !
As you can see within your webbrowser inspector tool …

As this website seems difficult to command via a request,
you can pilot IE and launch command you see within its inspector tool
and then use system SendKeys. But not easy with this kind of site …
​

 

[lawvictor]

Hi Marc,

Thank you for your feedback, it made me to try out different things, I somehow was able to figure out element "datacsv" and by replacing by below code am able to get the data in the form of a string. this string is instead of a csv file. I am not sure if this was stable solution and will it give a consistent result all the time. Now am facing another issue with this code. When i hit F8 and execute the code and wait for some time at this line of the code I am able to get the required string. However if i run the overall script I get a Null character. Do you have any idea how to resolve this issue. or please let me know if there is a better way to achieve the result.

Dim strURL As String
Dim strFile As HTMLDivElement
Dim ie As InternetExplorer, ieDoc As HTMLDocument

strURL = "https://www.nseindia.com/live_market/dynaContent/live_watch/equities_stock_watch.htm"

Set ie = New InternetExplorer
ie.Visible = True
ie.navigate strURL

Do
    DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE

ie.document.getElementById("bankNiftySelect").selectedIndex = 29
ie.document.getElementById("bankNiftySelect").FireEvent ("onchange")

Do
    DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE

Dim content As String
content = (ie.document.getElementById("datacsv").Value)

WriteFile (content)

Hi !

Error occurs with this Id which not exists !
As you can see within your webbrowser inspector tool …

As this website seems difficult to command via a request,
you can pilot IE and launch command you see within its inspector tool
and then use system SendKeys. But not easy with this kind of site …
​

[/quote]

Edit: Added Code Tags

 

[Marc L]

So you have to wait until the object exists in the webpage …
​