Styjun

I have code which picks up data from multiple columns from ThisWorkbook and puts in various field in website in internet explorer. The website loads after clicking on line1 (Search button). Then the code throws an error at line2 where it clicks on checkbox, as there is no checkbox yet if the website is still loading. (I think the website is built on Sharepoint and is poorly coded.)

Is there any code which repeats line 2 after 2-3 seconds and continues further whenever the error appears? I tried error handler to repeat the code but didn't work.

 Sub CSA_Upload()

 Dim test1 As Long, test2 As Long
 test1 = Timer

 Dim n As Long
 Range("A1").Select
 n = Selection.End(xlDown).Row
 ThisWorkbook.Sheets("Data").Range("A2:A" & n).Interior.ColorIndex = 0

 Dim IE As Object
 Dim doc As Object
 Dim htmlTable As htmlTable
 Set IE = New InternetExplorerMedium
 'Set IE = CreateObject("InternetExplorer.Application")
 IE.Visible = True

 'Navigate to CSA tool Home Page
 IE.navigate "https://csa.abcdefg.com/Collector_view.aspx/"

 'Wait till it loads
 Do While IE.Busy
 Application.Wait DateAdd("s", 1, Now)
 Loop

 Set doc = CreateObject("htmlfile")
 Set doc = IE.document

 'Enter Invoice Number in SearchBy box
 doc.getElementById("ContentPlaceHolder1_ddlSearch").Value = "[Inv Number]"

 Range("A1").Select

 'Count the number of rows in the data list
 Dim X As Long
 Range("A1").Select
 X = Selection.End(xlDown).Row

 'For each invoice number the loop starts here
 For rowNo = 2 To X
 ActiveCell.Offset(1).Select
 'Fill Blue colour in active processing invoice number cell
 ThisWorkbook.Sheets("Data").Range("A" & rowNo).Interior.ColorIndex = 37

 'Input the invoice number
 doc.getElementById("ContentPlaceHolder1_txtSearch").Value = ThisWorkbook.Sheets("Data").Range("A" & rowNo).Value

 'Click the Search button
 'This is the Line1
 doc.getElementById("ContentPlaceHolder1_search").Click

 'Wait till it loads
 Do While IE.Busy
 Application.Wait DateAdd("s", 5, Now)
 Loop

 'Checkbox select all
 'This is the Line2
 doc.getElementById("ContentPlaceHolder1_GridView1_chkboxSelectAll").Click
 'Wait 3 seconds till it selects all the checkboxes
 Application.Wait DateAdd("s", 3, Now)

 'Enter rest of the data
 doc.getElementById("ContentPlaceHolder1_ddlaction").Value = ThisWorkbook.Sheets("Data").Range("B" & rowNo).Value 'Input Action
 doc.getElementById("ContentPlaceHolder1_txtToDoDate").Value = ThisWorkbook.Sheets("Data").Range("C" & rowNo).Value 'Input Action Date
 doc.getElementById("ContentPlaceHolder1_ddlstatus").Value = ThisWorkbook.Sheets("Data").Range("D" & rowNo).Value 'Input Root Cause
 doc.getElementById("ContentPlaceHolder1_txtcomments").Value = ThisWorkbook.Sheets("Data").Range("E" & rowNo).Value 'Input Comments
 doc.getElementById("ContentPlaceHolder1_btn_Comments").Click 'Click Submit button

 Application.Wait DateAdd("s", 3, Now)

 'Hit enter on MessegeBox
 Application.SendKeys "ENTER"

 'Fill Green colour in the active cell when all entries are passed
 ThisWorkbook.Sheets("Data").Range("A" & rowNo).Interior.ColorIndex = 35

 Next 'Proceed to next invoice number

 IE.Quit 'Quit Internet explorer
 test2 = Timer
 MsgBox (X - 1) & " Invoices have been updated and it took " & Format((test2 - test1) / 86400, "hh:mm:ss") & " Seconds."

 End Sub

revised on 2019-03-25

I think the error is thrown because the doc is changed.

Rewrite

' This is the Line2
 doc.getElementById("ContentPlaceHolder1_GridView1_chkboxSelectAll").Click
 'Wait 3 seconds till it selects all the checkboxes
 Application.Wait DateAdd("s", 3, Now)

as

' This is the Line2 
 application.wait Application.Wait DateAdd("s", 1, Now)
 set doc = IE.document
 doc.getElementById("ContentPlaceHolder1_GridView1_chkboxSelectAll").Click
 'Wait 3 seconds till it selects all the checkboxes
 Application.Wait DateAdd("s", 3, Now)

maybe helpful.

Use proper page load waits after each .Navigate and .Click.

While ie.Busy Or ie.readyState < 4: DoEvents: Wend

Also, you can wrap elements that are throwing errors, related to timings, in timed loops which attempt to set the object reference

Dim t As Date, ele As Object
Const MAX_WAIT_SEC As Long = 10

t = Timer
Do
 On Error Resume Next
 Set ele = doc.getElementById("ContentPlaceHolder1_GridView1_chkboxSelectAll")
 On Error GoTo 0
 If Timer - t > MAX_WAIT_SEC Then Exit Do
Loop While ele Is Nothing

If Not ele Is Nothing Then
 ele.Click
End If

I have removed below wait loop after line 1.

 'Wait till it loads
 Do While IE.Busy
 Application.Wait DateAdd("s", 5, Now)
 Loop

and added fix 10 seconds wait time Application.Wait DateAdd("s", 10, Now) just before

 doc.getElementById("ContentPlaceHolder1_GridView1_chkboxSelectAll").Click
 'Wait 3 seconds till it selects all the checkboxes
 Application.Wait DateAdd("s", 3, Now)

So the final piece of code is as below and it's working!

'This is the Line1
 doc.getElementById("ContentPlaceHolder1_search").Click

 'Checkbox select all
'This is the Line2
 Application.Wait DateAdd("s", 10, Now)
 doc.getElementById("ContentPlaceHolder1_GridView1_chkboxSelectAll").Click
 'Wait 3 seconds till it selects all the checkboxes
 Application.Wait DateAdd("s", 3, Now)