asked on

Excel Visual Basic error "Object has disconnected from its clients

My macro uses Internet Explorer to open PeopleSoft Financials and then run a public query. It has been working for a long time until recently. I'm aware of an update to Internet Explorer on our machines in early July and am wondering if that has caused this issue.

The macro is throwing an "Object has disconnected from its clients" error message.

Here is how the variable for Internet Explorer is defined: Public ExpApp As InternetExplorer

Here is the code where the error is thown:
Set ExpApp = CreateObject("InternetExplorer.Application")
ExpApp.Visible = True

ExpApp.navigate vLogin

Do Until ExpApp.readyState = READYSTATE_COMPLETE ' ERROR IS THROWN HERE
MyTimer
Loop

Do Until ExpApp.document.Title <> "PeopleSoft 8 Sign-in"
MyTimer
Loop

Do Until ExpApp.readyState = READYSTATE_COMPLETE
MyTimer
Loop

vLogin is a string with the URL to the PeopleSoft login screen MyTimer is a little function that waits 1 second each time it is kicked off

This macro has been around a while so I imagine that this is pretty old code. I'm hoping there are a few minor changes I could make instead of re-writing this macro. I've attached the code for the macro.

Any suggestions would be greatly appreciated. Thanks for the help......
Code.docx

Neil Fleming

I believe this error is often caused by the fact that IE "secretly" destroys itself and creates a new instance when switching from one security zone on the internet to another.

I would suggest the following:

1. Add "Microsoft Internet Controls" to your "Tools/References" list in VBA
2. Create your ExpApp variable as follows, rather than using "CreateObject"

Dim ExpApp As SHDocVw.InternetExplorer
Set ExpApp = New InternetExplorer
 ExpApp.Visible = true

Open in new window

3. If the error persists, try defining ExpApp as

Dim ExpApp As SHDocVw.InternetExplorerMedium

Open in new window

and creating it accordingly.

The "medium" version of the object interacts differently with security settings.

4. If you still have a problem, the following code may help. It requires a further addition to "tools/references" namely the DLL called "Microsoft Shell Controls and Automation"

This routine runs through all open windows (visible and invisble) in search of the one that references the URL you are trying to access, and reconnects to it

Sub CheckIEprocess(hLink As String)
Dim sh
Dim eachIE

Do
  'create Shell
  Set sh = New Shell32.Shell
  'loop through all windows)
  For Each eachIE In sh.Windows
  'if url found then attach IE variable
    If InStr(1, eachIE.LocationURL, hLink) Then
      Set ExpApp = eachIE
      Exit Do
      End If
    Next eachIE
  Loop
Set eachIE = Nothing
Set sh = Nothing

While ExpApp.Busy  ' The new process may still be busy even after you find it
  DoEvents
  Wend
DoEvents


End Sub

Open in new window

I use a version of the routine above with the five short routines below to access urls, and it seems to work reliably (for now.. there may be further issues with Excel2016). You need to define ExpApp at the module level for this to work.

Function goPage(hLink As String) As Boolean
'If no instance of IE running, then create one using the OpenSite routine below
If (ExpApp Is Nothing) Then goPage = OpenSite(hLink, True)
'navigate to page:
ExpApp.navigate hLink
Application.StatusBar = "Connecting to " & hLink & ". Please wait..."
'call PageOpens to wait for page

'open page with a 30-second timeout
goPage = PageOpens(30)

'check IE is attached to correct process, using the routine from earlier in this post..
CheckIEprocess (hLink)
End Function

Function OpenSite(hLink As String, showPage As Boolean) As Boolean

'opens a webpage when no instance of IE is running

 Set ExpApp= New InternetExplorer
 ExpApp.Visible = showPage
 OpenSite = goPage(hLink)
 End Function

Function PageOpens(TimeOut As Long) As Boolean
Dim i As Long
' Wait while IE loading...
 
 'abort if can't open site
 
 If IEHangs(TimeOut) Then
 MsgBox ("Opening web page timed out")
 Application.StatusBar = ""
 PageOpens = False
 Else
 PageOpens = True
 End If
End Function

Function IEHangs(TimeOut As Long) As Boolean

Dim i As Long
i = 0

On Error GoTo errortrap:
DoEvents
'wait 2 seconds as a precaution
wait 2

i = 0
IEHangs = False
 'wait for IE to stop being "busy"
 Do While (ExpApp.Busy)
 Application.wait DateAdd("s", 1, Now)
 i = i + 1
 If i > TimeOut Then
 IEHangs = True
 Exit Do
 End If
 Loop
 i = 0
 
Exit Function

errortrap:
MsgBox ("IE has closed")
IEHangs = True
End Function

Sub wait(iSec)
Dim i As Long
For i = 1 To iSec
Application.wait Now + TimeValue("0:00:01")
DoEvents
Next
End Sub

Open in new window

Hope this helps.

Mike Schaper

ASKER

Thanks for the suggestions. I had to use the functions above to get past the issue I was having. The macro is doing a couple of strange things. (1) It is displaying the messages "IE has closed" Opening Web Page has timed out" but keeps on running. ( I haven't stepped though it yet). It is almost like it is opening the PeopleSoft signon page twice but I only see one. Once it opens the user has to key in their user id and password. the first time I key it, it goes blank and says user id or password is invalid. Then the second time the logon works.

The macro is able to navigate to the query screen and fill in the prompts. However when it hits the command below, nothing happens.

ExpApp.navigate vSubmit

vSubmit is a variable that contains "javascript:submitAction_win0(document.win0,'#ICQryDownloadExcelFrmPrompt');".

So it is kind of good news / bad news but it is progress. Thanks again for your help..........

ASKER CERTIFIED SOLUTION

Neil Fleming

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Schaper

ASKER

You are correct. After it fills in the 4 prompts, the next step is to push the button; which runs the query and displays the results

Excel Visual Basic error &quot;Object has disconnected from its clients

Excel Visual Basic error "Object has disconnected from its clients