Controlling Internet Explorer local intranet using Excel VBA

Question

I am using Excel VBA code to click a button on our website. I know this isn't the best of things to be doing, but it is the least objectionable option available to me.

I can using this code, successfully load imdb.com, google, etc. But when I load our local site, I lose control of the ie object, I can't check readyState, I can't Quit.

Here is the error I get.

Run-time error '-2147023179 (800706b5)':
Automation error
The interface is unknown

Every so often I instead get this message:

Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients.

Clicking Debug indicates the ie.readyState, I commented that out and then it points to ie.Quit

Sub dothestuff()
    Dim ie As InternetExplorer
    Set ie = New InternetExplorer
    ie.Visible = True

    ie.Navigate "http://www.google.com/"
    anerror = webload(ie)

    ie.Quit
    Set ie = Nothing
End Sub

Function webload(ie)
    Do Until ie.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop
End Function

For everyone else struggling with those kind of issues, I have found a solution and compiled a summary with sample instructions [here](https://stackoverflow.com/a/45016781/3410218). It does not rely on using a fixed IP, nor changing the security settings and neither do you need to include references to anything. That being said, thanks a lot for all your contributions and valuable suggestions - this seems to be a bug in how event handling with IE works in relation to security zones. — must_improve, Jul 10 '17 at 16:08

score 33 · Accepted Answer · edited Oct 07 '13 at 09:54

33

Here is a quick and easy solution for this issue:

Instead of:

set IE = createobject("internetexplorer.application")

Use:

Set IE = New InternetExplorerMedium

No need to tweak the IE settings

edited Oct 07 '13 at 09:54

laalto

137,703
64
254
280

answered Oct 07 '13 at 09:33

Vigneshts88

346
3
2

8

`InternetExplorerMedium` requires a reference to `Microsoft Internet Controls` – Mike Nov 03 '14 at 18:58

score 18 · Answer 2 · answered Jan 05 '15 at 17:39

Here's what's happening. When your browser internally "jumps" to a different security zone - from say a local address to an inTRAnet address or to an inTERnet address - IE closes/drops the current process of IE and opens another one with tighter security. The old and new IE processes are IE child processes to the parent IE task so you don't see it happen either by watching the browser or watching the process in Task Manager. If you use Process Explorer (free from Microsoft), you can see this happen.

What you need to do in this type of environment is use Anup Upadhyay's solution above. His code snippet looks at all IE tasks (parent and child doesn't make a difference) and finds the new IE process that is pointing to the web address that the original process was given. As long as you don't have multiple browsers open to the same address, it will find the new process reliably and carry on like you would expect it to.

Note: You might also need to use the InternetExplorerMedium object as opposed to the InternetExplorer object. It is also better at "holding on" to a session.

Here's my version which is almost the same thing as Anup's.

References for MS Office VBA:

Microsoft Internet Controls
Microsoft Shell Controls and Automation

n

Dim IE As InternetExplorerMedium ' This object (the "medium" variety as opposed to "InternetExplorer") is necessary in our security climate
Dim targetURL As String
Dim webContent As String
Dim sh
Dim eachIE

targetURL = "[your URL here]"
Set IE = New InternetExplorerMedium
IE.Visible = False ' Set to true to watch what's happening
IE.Navigate targetURL

While IE.Busy
  DoEvents
  Wend

Do
  Set sh = New Shell32.Shell
  For Each eachIE In sh.Windows
    If InStr(1, eachIE.LocationURL, targetURL) Then
      Set IE = eachIE
      'IE.Visible = False  'This is here because in some environments, the new process defaults to Visible.  
      Exit Do
      End If
    Next eachIE
  Loop
Set eachIE = Nothing
Set sh = Nothing

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

score 9 · Answer 3 · edited Sep 06 '13 at 12:10

9

Try this one:

Set IE = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")

edited Sep 06 '13 at 12:10

Tim Cooper

144,163
35
302
261

answered Jul 15 '13 at 19:12

Rick

91
1
1

3

Clever, this is simply the class id of InternetExplorerMedium, which has no progid unless you create one, for example InternetExplorer.ApplicationMedium:[HKEY_CLASSES_ROOT\InternetExplorer.ApplicationMedium] [HKEY_CLASSES_ROOT\InternetExplorer.ApplicationMedium\CLSID] @="{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}"See https://blogs.msdn.microsoft.com/ieinternals/2011/08/03/default-integrity-level-and-automation/ – WizzleWuzzle Aug 16 '17 at 16:57

score 7 · Answer 4 · answered Mar 28 '13 at 16:42

7

We had this problem using IE9. It arises because of security settings. There is a setting called "Enable Protected Mode" on the "Security" tab of the "Internet Options" dialog box (tools...internet options). Each "zone" can have a different setting for "enable protected mode" We unchecked "enable protected mode" and this solved our problem. I think what is happening is that when switch to a zone that is in protected mode, IE starts a new process to handle traffic in that zone (maybe killing the old process). When this happens your IE object variable in VBA gets disconnected from Internet Explorer.

answered Mar 28 '13 at 16:42

Gove

93
1
4

1

Thank you! I've been working on this, trying everything under the sun in VBA! Many googles later, I came to this, and it solved the problem. I was getting various runtime errors all at the same line. It was driving me nuts! – PermaNoob Dec 31 '14 at 17:39
So many issues... so many resolutions... this one worked for me. – interesting-name-here Jun 13 '17 at 16:26

score 4 · Answer 5 · answered Oct 19 '12 at 16:35

4

Alright, just found a solution, decided out of desperation to try loading 127.0.0.1 instead of localhost, sure enough, no problems, so resolved the ip address of the local intranet server, and now I am good to go. I don't really understand why, but this has solved my issue.

answered Oct 19 '12 at 16:35

Varyl

276
1
2
11

just curious, did you use "http : //localhost/" (without the spaces) or just localhost in the URL? – Joseph Oct 19 '12 at 16:59
I used http : //localhost/ (without spaces) The way I got it working was using http : //127.0.0.1/ (without spaces) – Varyl Oct 19 '12 at 17:59
thanks for the info. not sure why it's working like that, but not like the other way...but it's good to know for any future coding I may do :) – Joseph Oct 19 '12 at 18:08
Yeah, I truly don't understand why either, just happy to have _a_ working solution. Would love if anyone else could explain the reason for this behavior. – Varyl Oct 19 '12 at 18:11

score 4 · Answer 6 · answered Nov 05 '13 at 05:44

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
IE.Navigate "C:\abc.html"

'We are getting error here , I have found a small tricky alternate solution for this as below 'Take reference for Microsoft Shell Controls And Automation 'Use below code to reassign IE object for the lost connection issue

Dim sh
Dim eachIE
Do

    Set sh = New Shell32.Shell
    For Each eachIE In sh.Windows
        ' Check if this is the desired URL

' Here you can use your condition except .html
' If you want to use your URL , then put the URL below in the code for condition check.
    ' This code will reassign your IE object with the same reference for navigation and your issue will resolve.
' 
        If InStr(1, eachIE.locationurl, ".html") Then 
        Set IE = eachIE
        Exit Do
        End If
    Next eachIE
Loop

score 2 · Answer 7 · answered Mar 30 '15 at 15:14

2

For me, the following worked:

Open Internet explorer
Go to tools
Goto Internet options
Switch to Security tab
Click Trusted sites
Click sites
You will see the site url, select it
Click Remove
Click close, apply and Ok

Ps. I tested these steps on IE 8 browser.

answered Mar 30 '15 at 15:14

Zameer Ansari

23,672
19
120
191

@kjack Glad to know this! – Zameer Ansari Jan 25 '18 at 11:35

score 1 · Answer 8 · answered Oct 19 '12 at 13:55

1

Maybe you're having a problem with binding? That's a strange error to get for this.

Try this code (adapted from http://www.excely.com/excel-vba/ie-automation.shtml). Hopefully it helps:

Option Explicit

' lasts for the life of Excel being open
Dim ie As Object

Sub dothestuff()
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.Navigate "http://www.google.com/"

    Do While ie.Busy
        DoEvents
    Loop

    ie.Quit
    Set ie = Nothing
End Sub

answered Oct 19 '12 at 13:55

Joseph

4,795
1
22
24

Unfortunately no, still getting the same message with that change, still works with public domains like google.com, but localhost doesn't work. – Varyl Oct 19 '12 at 15:09
is the intranet site on your machine or a server on your intranet? – Joseph Oct 19 '12 at 15:34
server on the intranet, I just tested the same with localhost server hosted on this machine, same exact errors. – Varyl Oct 19 '12 at 15:44

score 1 · Answer 9 · answered Jul 17 '13 at 13:28

I am having the exact same issue working with a website that is inside our company intranet and is also included in my trusted sites. Disabling protected mode allows me to check ReadyState, but only if I disable for the internet zone in addition to intranet and trusted sites.

Why would enabling protected mode for the internet zone cause the automation error when checking ReadyState on a trusted site? Is there a way around this without disabling protected mode for the internet zone?

ozmike · Answer 10 · 2016-05-06T02:17:23.660

Another solution..

Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate ("http://URLOnMyIntranet/site.html")
IE.Visible = True

Set IE = nothing
Set objShellApp = CreateObject("Shell.Application")
For Each objWindow In objShellApp.Windows
    Debug.Print objWindow.LocationName
     If LCase(objWindow.LocationName) = LCase("Your windows name, use debug to find out") Then
            Set IE = objWindow
     End If
Next

Note, using InternetExplorerMedium (see other answers) usually all you need, FYI also you could use Url property obj.LocationURL instead of window name which would be more accurate. See other answers.

score 0 · Answer 11 · answered Dec 21 '19 at 02:53

Dim IE As InternetExplorer
Set IE = New InternetExplorerMedium
IE.Navigate "yousite.org"
IE.Visible = True

Do While IE.Busy
    DoEvents
Loop    

Set HTML = IE.document
Do
var8 = HTML.DocumentElement.innerHTML
v3 = var8
v4 = InStrRev(v3, "the class or ID you are looking for when the _    
page final loads", -1)
Loop While v4 = 0

Do While IE.Busy
    DoEvents
Loop 

'Your in the new page look for elements and click rinse and repeat
'the Do loop. This method works with my intranet corporate site

Controlling Internet Explorer local intranet using Excel VBA

11 Answers11

Linked