What is causing my timing loop to break when extending timer

Question

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.

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

Did you read the error message? Somewhere you are scheduling a system shutdown TWICE. — , Dec 30 '19 at 18:31
Yes i read it and it makes no sense because I am not scheduling a shutdown. All my code is there. No shutdown instruction. — Ken Carter, Dec 30 '19 at 18:37
Well you have some code somewhere. And it appears to be VB6 code. When you pressed debug in the dialog what line did it take you too? What does your web page do - reboot systems? — , Dec 30 '19 at 18:40
https://stackoverflow.com/questions/23994477/createobject-randomly-throws-a-system-shutdown-has-already-been-scheduled-erro — Siddharth Rout, Dec 30 '19 at 18:42
https://winsourcecode.blogspot.com/2019/12/decoding-errors.html So it is a Win32 error in a hResult. `C:\WINDOWS\system32>net helpmsg 1190` `A system shutdown has already been scheduled.` — , Dec 30 '19 at 18:42
The web page itself I have nothing to do with other than filling in and clicking on options to bring up the report. This is a clean load of Excel with nothing but the button on the single worksheet that is up. That button imitates the first sub which triggers the repeated execution of the second one. It works perfectly if the wait (X) is set to 10 seconds in stead of 1 hour. I ran the loop for about 50 reiterations before attempting the 1 hour wait. It would seem to me that if it was a shutdown directed that it would have shown up in both scenarios. — Ken Carter, Dec 30 '19 at 18:56

score 0 · Accepted Answer · answered Jan 04 '20 at 15:29

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:

Focusing in on the elements of the IE to direct the login to the report.
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

What is causing my timing loop to break when extending timer

1 Answers1