Search code examples
excelvbado-loops

What is causing my timing loop to break when extending timer

The macro I'm developing is designed to launch from a button. The button triggers a looping sub that simply fires the sub's child process which does most of the work.

The child process loads a URL in IE and then navigates to a report screen. Following that it waits a specified pored of time (in the flow the value is X. When X is exceeded IE is closed and control is surrendered back to the parent which waits 10 seconds before relaunching the sub.

enter image description here

The problem comes into play when X is changed to one hour from my 10 second test timer. I'm suspecting that there is something in VBA Excel that decays so after about five minutes I get the error (which doesn't make a lot of sense to me).

Interested in finding out what the time change does to break this and see if anyone knows of a way around the problem.

Code below... Thanks in advance!

Private Sub OpenReport()
Dim text As String
Dim x As Integer
Dim IE
Set IE = New InternetExplorerMedium
text = "PCode"

'Load link
IE.Visible = True

IE.Navigate "http://~~~~~~~~~~~~.net/reports/views/result/reportResult.faces"

'Wait for page to load
Do While IE.Busy Or IE.ReadyState <> 4
    DoEvents
Loop

'fill in the pcode
IE.Document.getelementbyid("Pcode").Value = text

'Click Submit
IE.Document.getElementsByClassName("btn btn-lg btn-primary btn-block")(0).Click

'Wait for page to load
Do While IE.Busy Or IE.ReadyState <> 4
    DoEvents
Loop

'Click on Favorites
IE.Document.getElementsByClassName("myMRSFavoritesLink")(0).Click

'Wait for page to load
Do While IE.Busy Or IE.ReadyState <> 4
    DoEvents
Loop

'Click on report
IE.Document.getElementsByName("myFavoritesForm:treeTable_tableId:6:infoButton98772406j_id__v_55")(0).Click

'Wait for page to load
Do While IE.Busy Or IE.ReadyState <> 4
    DoEvents
Loop

IE.Document.getElementsByName("setParametersForm:startReportButton")(0).Click

'Wait for a period of time contained in TimeValue
    Application.Wait (Now + TimeValue("00:01:00")) ' This is X
'Close IE
    IE.Quit
End Sub

Private Sub CommandButton1_Click()
Dim Looper As Long
Looper = 0

Do While Looper < 1
    Application.Wait Now + TimeValue("00:00:10")
    Call OpenReport
Loop
End Sub
Solution

  • The final solution to this project has been running 24/7 for three days now, so I think it is safe to provide you with where all the extremely helpful discussion and suggestions has taken me.

    The key changes that made this a success:

    1. Focusing in on the elements of the IE to direct the login to the report.
    2. Adding a 'On Error Resume Next' statement.

    The network is wireless where this is implemented and periodically there will be momentary signal interruptions. Once all the timing loops and element controls were made effective for getting the session to the report and back out again, we were seeing an intermittent issue with the code bombing. The 'Resume Next' assured that when we had these interruptions, which the macro would step down to the point of closing the IE session, which resulted in the restart of a fresh session.

    Doing this provided stability.

    The entire solution is below. Note that the first sub is the one that is initiated in the work flow above. It waits briefly to let the system settle, then fires off the sub that calls and navigates the IE session.

    The second sub includes the timing loop (59 minutes) and the key 'On Error' statement that overcame our network issues.

    Private Sub CommandButton1_Click()
Dim Looper As Long
Looper = 0

Do While Looper < 1
    Application.Wait Now + TimeValue("00:00:10")
    Call OpenReport
Loop
End Sub

Private Sub OpenReport()
Dim text As String
Dim Looper As Long
Dim x As Integer
Dim IE
Set IE = New InternetExplorerMedium
text = "PCode"
'Load link
IE.Visible = True

    On Error Resume Next '<<<--- Final resolution'

    IE.Navigate "http://~~~~~~~~.net/reports/views/result/reportResult.faces"

'Wait for page to load
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop

'fill in the pcode
    IE.Document.getelementbyid("PCode").Value = text

'Click Submit
    IE.Document.getElementsByClassName("btn btn-lg btn-primary btn-block")(0).Click

'Wait for page to load
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop

'Click on Favorites
    IE.Document.getElementsByClassName("myMRSFavoritesLink")(0).Click

'Wait for page to load
    Do While IE.Busy Or IE.ReadyState <> 4
    DoEvents
    Loop

'Click on report
    IE.Document.getElementsByName("myFavoritesForm:treeTable_tableId:6:infoButton98772406j_id__v_55")(0).Click

'Wait for page to load
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop

'Click on execute report
    IE.Document.getElementsByName("setParametersForm:startReportButton")(0).Click

'Wait for a period of time contained in TimeValue
    Application.Wait (Now + TimeValue("00:59:00"))

'Close IE
CloseIE:
    IE.Quit

End Sub