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!!
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments