0

I am trying to download all the csv files from a specific website by using excel vba

Following is the code i have prepared :

Sub Gettable()

Dim URL As String

Dim ie As Object
Dim ieDoc As Object
Dim sel_day As Variant

URL = "http://www.bseindia.com/markets/equity/EQReports/BhavCopyDebt.aspx?expandable=3"


Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.navigate URL


Do Until (ie.readyState = 4 And Not ie.Busy)
    DoEvents
Loop

Set ieDoc = ie.document

 '============================================================================   
    ieDoc.getElementsByTagName("Select")("fdate1").Options("02").SelectIndex
 '============================================================================

'ie.Quit
'Set ie = Nothing
'Set ieDoc = Nothing


End Sub

Now the problem i am facing here is i am not able to change the contents dropdown box(to form a date). I have already tried lots of solutions from stackoverflow as well as other websites but havent got any success. i have good programming knowledge but am stuck at this point the whole day. Any help would be appreciated. thanks in advance :)

All i wanted in the end was to download all csv files. I figured out an alternative solution in the mean time to download the csv file but would still appreciate if someone gives a solution to this issue i had posted above... :)

My alternative Solution as follows :

Sub try10() 'Took me 10 tries by the way :)

Dim NoOfDays As Long, i As Long
Dim MyDate As Variant


'Since the minimum date can't be less #1/1/2007# so lets loop until Mydate reaches #1/1/2007#.

NoOfDays = Date - #1/1/2007#

For i = 0 To NoOfDays
MyDate = Format((Date - i), "ddmmyy")


Dim myURL As String
myURL = "http://www.bseindia.com/download/BhavCopy/Equity/eq" & MyDate & "_csv.zip"
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody
    oStream.SaveToFile ("C:\Users\X\Desktop\BhavCopies\eq" & MyDate & "_csv.zip")
    oStream.Close
End If

Next

End Sub

This solution however produces a 211kb fake file for csv's that don't exist which can be dealt with manually . :) ;)

Aditya Pednekar
  • 442
  • 2
  • 6
  • 19
  • Does this question help? http://stackoverflow.com/q/15003015/2258 – Richard Morgan Jan 17 '14 at 13:29
  • yes i already tried solving the query with this example before posting the question but none of the solutions is working for me. I dont understand what am i doing wrong because whenever i used solutions from that posted query i get "Error 424 - Object required" – Aditya Pednekar Jan 17 '14 at 13:43
  • Protected mode? http://stackoverflow.com/a/15278562/2258 – Richard Morgan Jan 17 '14 at 14:26
  • nopes .. anyways i figured out an alternative solution to my problem as i am a "die hard trier" :P :D i'll paste out the solution in the qtn itself. However i'd still appreciate if someone finds a solution to this problem.. :) Thanks anyways richards for taking time to peek in into my question :) – Aditya Pednekar Jan 17 '14 at 14:55

1 Answers1

1

The items you are trying to control are in an iframe. To get to the HTML inside the iframe, extract the src attribute from the tag and navigate to the URL formed by that src and the base URL. In this case the src is "Equitydebcopy.aspx", so if you navigate to "http://www.bseindia.com/markets/equity/EQReports/Equitydebcopy.aspx" you'll find that the following lines will allow you to get the information you want.

ie.document.getElementByID("fdate1").Value = "15"
ie.document.getElementByID("fmonth1").Value = "1"
ie.document.getElementByID("fyear1").Value = "2014"
ie.document.getElementByID("btnSubmit").Click
ron
  • 1,456
  • 3
  • 18
  • 27