CODEWITHSUNDEEP
excelvba
David
David

Reputation: 149

How to restart for loop when error occurs vba

I am doing IE automation using VBA (Basically I open IE and goto the specific URL from the sheet and then login using credentials from the sheet and then extract data from the webpage to excel) This has to happen for 20 websites so I added for loop and it works fine.

What I want is, in case of any error occurs with in the loop then loop has to restart. I also tried "on error got 0, on error got -1" but it did not work. Below is my Code - Kindly pardon me for poor coding I am new to VBA.

    Sub Get_Data()
    Sheets("Sheet2").Select
    Range("E2").Select
    Range("H6:H120").ClearContents
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.application")
    IE.Visible = True
    Dim E As Long
    Dim S As Long
    E = Range("A" & Rows.Count).End(xlUp).Row

JumpToHere:
    For j = S To E
        S = Range("H" & Rows.Count).End(xlUp).Row
        Sheets("Sheet2").Select
        Range("E" & S).Select
        ActiveCell.Offset(1, -2).Select
        Dim X As Variant
        X = ActiveCell.Value
        IE.navigate X
        Do
            If IE.ReadyState = 4 Then
                IE.Visible = True
                Exit Do
            Else
                DoEvents
            End If
        Loop

        ActiveCell.Offset(0, 1).Select
        Dim Y As Variant
        Y = ActiveCell.Value
        IE.document.all("username").Value = Y
        ActiveCell.Offset(0, 1).Select
        Dim Z As Variant
        Z = ActiveCell.Value
        IE.document.all("password").Value = Z
        IE.document.all("merchant_login_submit_button").Click
        Application.Wait (Now + TimeValue("0:00:8"))

        Set ElementCol = IE.document.getElementsByTagName("span")
        For Each link In ElementCol
            If link.innerHTML = "Authentication Failed" Then
                ActiveCell.Offset(0, 3).Value = "Authentication Failed"
                GoTo JumpToHere
            End If
        Next

        Set tags = IE.document.getElementsByTagName("input")
        For Each tagx In tags
            If tagx.Value = "Continue to Control Panel" Then
                tagx.Click
                Application.Wait (Now + TimeValue("0:00:3"))
                Exit For
            End If
        Next


        Set ElementCol = IE.document.getElementsByTagName("a")
        For Each link In ElementCol
            If link.innerHTML = "Reports" Then
                link.Click
            End If
        Next
        Application.Wait (Now + TimeValue("0:00:06"))
        Dim checkdate As Integer
        checkdate = Format(Date, "dd") - 1

        IE.document.getElementById("snapshot_group_by").Value = "payment_processor"
        IE.document.getElementById("snapshot_end_date_day").Value = checkdate
        IE.document.all("reports_submit_button").Click
        Application.Wait (Now + TimeValue("0:00:3"))

        Dim ws As Worksheet
        Dim rng As Range
        Dim tbl As Object
        Dim rw As Object
        Dim cl As Object
        Dim tabno As Long
        Dim nextrow As Long
        Dim I As Long

        Set ws = Worksheets.Add

        For Each tbl In IE.document.getElementsByTagName("TABLE")
            tabno = tabno + 1
            nextrow = nextrow + 1
            Set rng = ws.Range("B" & nextrow)
            rng.Offset(, -1) = "Table " & tabno
            For Each rw In tbl.Rows
                For Each cl In rw.Cells
                    rng.Value = cl.outerText
                    Set rng = rng.Offset(, 1)
                    I = I + 1
                Next cl
                nextrow = 0
                Set rng = rng.Offset(1, -I)
                I = 0
            Next rw
        Next tbl

        ws.Cells.ClearFormats
        Sheets("Sheet2").Select
        ActiveCell.Offset(0, 3).Value = ActiveSheet.Previous.Range("F4")
        Application.DisplayAlerts = False
        ActiveSheet.Previous.Delete
        Application.DisplayAlerts = True

        Set ElementCol = IE.document.getElementsByTagName("a")
        For Each link In ElementCol
            If link.innerHTML = "Logout" Then
                link.Click
            End If
        Next
    Next j

End Sub

Upvotes: 0

Views: 2910

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Sounds like your real problem is that your code isn't properly waiting. Instead of Application.Wait, use a proper waiting loop any time you invoke the IE.Navigate or any element .Click or form .Submit event.

VBA HTML not running on all computers

Otherwise, you don't have any active error-trapping in your code. Wrap your loop with On Error statements, as below.

The first one, On Error GoTo MyErrorHandler instructs the program of what to do if an error is encountered within the loop. If there's an error, the code underneath the MyErrorHandler label will execute, and resume at the NextJ label. Once the loop finishes, On Error GoTo 0 returns normal (i.e., none) error-handling. Any errors occurring outside the loop still raise an exception during runtime.

Option Explicit
Sub Get_Data()
    '// Dim your variables

    '// Executable code starts here

JumpToHere:    
    For j = S To E
        On Error GoTo MyErrorHandler
        ' Now ANY ERROR, ANYWHERE in the loop will go to the error handler

NextJ:
    Next j
    '// Code below this line won't be subject to the error handler
    On Error GoTo 0

    '// more code if you have it

    ' Exit gracefully if there was no error:
    Exit Sub

    '// Here is the error handler:
    MyErrorHandler:
        Err.Clear()
        Resume NextJ
End Sub

If you truly want to re-start the loop, then instead of NextJ, do Resume JumpToHere.

Upvotes: 1

Related Questions