Interaction with button on webpage using excel vba working only in step debugging mode

Question

I am trying to fetch the data from website & put it in the excel worksheet

Scenario: Webpage contains button with id=btnAllRun.

When i click on the button,table is generated dynamically containing information in tr tags inside it. Using macro i need to count no. of the such tr tags & put the count in worksheet.

Code:

Dim IE as Object
Sub Button_Click()

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate URL
Do
    DoEvents
Loop Until IE.ReadyState = 4 //waiting for the webpage to load

Set Elmt = IE.Document.getElementById("btnAllRun") //get button elmt for All Running
Elmt.Click //Clicking button
Do
    DoEvents
Loop Until IE.ReadyState = 4

Set Tbl = IE.Document.getElementById("gvRunning") //gets table elmt containing tr tags
Sheets("XXX").Range("B36") = Tbl.Rows.Length - 1

When i am trying to run the macro i get 'Object Variable or with block not set' but when running the same macro in step debugging mode i get the correct results.

Any help on this would be greatly appreciated!!

Siddharth Rout · Accepted Answer · 2014-09-12 13:46:16Z

If it works in debug mode then it means your DoEvents is not working as expected. In such a case I use a customized routine Wait. So what I am doing is forcing the code to wait for a specific amount of time and then continue. For slower systems you may have to increase the time.

Wait 2 basically pauses the code for 2 seconds.

Try this. This has worked for me in many occasions.

Dim IE As Object

Sub Button_Click()
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate URL

    Do While IE.ReadyState <> 4: DoEvents: Loop

    Wait 2

    Set Elmt = IE.Document.getElementById("btnAllRun")

    Wait 2

    Elmt.Click

    Do While IE.ReadyState <> 4: DoEvents: Loop

    Wait 2

    Set Tbl = IE.Document.getElementById("gvRunning")

    Sheets("XXX").Range("B36") = Tbl.Rows.Length - 1

    '
    '~~> Rest of the code
    '
End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub

Collectives™ on Stack Overflow

Interaction with button on webpage using excel vba working only in step debugging mode

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related