CODEWITHSUNDEEP
excelvbahtmlhtml-select
Aditya Pednekar
Aditya Pednekar

Reputation: 462

Changing Contents of Select tag on a webpage using excel vba (To download CSV files from the webpage)

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 . :) ;)

Upvotes: 0

Views: 1561

Answers (1)

ron
ron

Reputation: 1378

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

Upvotes: 1

Related Questions