Get data from website screen in Excel with form - VBA

Question

With Stackoverflow, I reached the following code; it basically opens IE, navigates to the URL, fills out the form, and submits.

Sub getdata()
    Application.ScreenUpdating = False

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "http://www.bseindia.com/markets/equity/EQReports/BulknBlockDeals.aspx?expandable=7"

    Application.StatusBar = "Submitting"
    ' Wait while IE loading...
    While IE.Busy
        DoEvents
    Wend
    ' **********************************************************************
    delay 5
    IE.document.getElementbyid("ctl00_ContentPlaceHolder1_chkAllMarket").Click
    delay 5
    IE.document.getElementbyid("ctl00_ContentPlaceHolder1_txtDate").Value = "01/01/2014"
    delay 5
    IE.document.getElementbyid("ctl00_ContentPlaceHolder1_txtToDate").Value = "12/01/2014"
    delay 5
    IE.document.getElementbyid("ctl00_ContentPlaceHolder1_btnSubmit").Click
    delay 5
    '''IE.document.getElementbyid("ctl00_ContentPlaceHolder1_btnDownload").Click
    '''(Commented as the click gives the option asking to save, open the csv file)

    '**********************************************************************
    Application.StatusBar = "Form Submitted"
    'IE.Quit            'will uncomment line once working
    'Set IE = Nothing   'will uncomment line once working

    Application.ScreenUpdating = True
End Sub

Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub

Problem:

After submitting the form, the data is filled on the screen, as well as the Excel icon (Download) with the same data in csv.

How can I get this data (any way is ok) on my active sheet.

Answer 1

try it

Sub getdata()
    Application.ScreenUpdating = False

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "http://www.bseindia.com/markets/equity/EQReports/BulknBlockDeals.aspx?expandable=7"

    Application.StatusBar = "Submitting"
    ' Wait while IE loading...
    While IE.Busy
        DoEvents
    Wend
    ' **********************************************************************
    delay 5
    IE.document.getelementbyid("ctl00_ContentPlaceHolder1_chkAllMarket").Click
    delay 5
    IE.document.getelementbyid("ctl00_ContentPlaceHolder1_txtDate").Value = "01/01/2014"
    delay 5
    IE.document.getelementbyid("ctl00_ContentPlaceHolder1_txtToDate").Value = "12/01/2014"
    delay 5
    IE.document.getelementbyid("ctl00_ContentPlaceHolder1_btnSubmit").Click
    delay 5

    '**********************************************************************
    Application.StatusBar = "Form Submitted"

    Dim tbl As Object, tr As Object, trCol As Object, td As Object, tdCol As Object
    Dim row As Long
    Dim col As Long

    row = 1
    col = 1

    Set tbl = IE.document.getelementbyid("ctl00_ContentPlaceHolder1_divData1").getElementsbytagname("Table")(0)
    Set trCol = tbl.getElementsbytagname("TR")

    For Each tr In trCol
        Set tdCol = tr.getElementsbytagname("TD")
        For Each td In tdCol
            Cells(row, col) = td.innertext
            col = col + 1
        Next
        col = 1
        row = row + 1
    Next


    IE.Quit            'will uncomment line once working
    Set IE = Nothing   'will uncomment line once working

    Application.ScreenUpdating = True
End Sub

Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub

Answer 2

Sorry to misunderstand the question.

Now I got what the OP wants.

Here I am not going to tell how to click the "Open" button in the download window.

excel (, -, OP)

.

Sub getdata()
    Application.ScreenUpdating = False

    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.Navigate "http://www.bseindia.com/markets/equity/EQReports/BulknBlockDeals.aspx?expandable=7"

    Application.StatusBar = "Submitting"
    ' Wait while IE loading...
    While ie.Busy
        DoEvents
    Wend
    ' **********************************************************************
    delay 5
    ie.document.getelementbyid("ctl00_ContentPlaceHolder1_chkAllMarket").Click
    delay 5
    ie.document.getelementbyid("ctl00_ContentPlaceHolder1_txtDate").Value = "01/01/2014"
    delay 5
    ie.document.getelementbyid("ctl00_ContentPlaceHolder1_txtToDate").Value = "12/01/2014"
    delay 5
    ie.document.getelementbyid("ctl00_ContentPlaceHolder1_btnSubmit").Click
    delay 5
   ' ie.document.getelementbyid("ctl00_ContentPlaceHolder1_btnDownload").Click

Set doc = ie.document

For Each d In doc.all.tags("table")

If InStr(d.innertext, "Client Name") > 0 Then

With d
        For x = 0 To .Rows.Length - 1
            For y = 0 To .Rows(x).Cells.Length - 1
                Sheets(1).Cells(x + 1, y + 1).Value = .Rows(x).Cells(y).innertext
            Next y
        Next x
    End With

End If

Next d
    Application.ScreenUpdating = True
End Sub

Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub