Error automating website data entry as the website

Question

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

Answer 1

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)

Answer 2

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.

Answer 3

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