使用 Excel VBA 控制 Internet Explorer 本地 Intranet

Controlling Internet Explorer local intranet using Excel VBA

提问by Varyl

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.

我正在使用 Excel VBA 代码单击我们网站上的按钮。我知道这不是最好的选择，但对我来说这是最不令人反感的选择。

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.

我可以使用这段代码，成功加载 imdb.com、google 等。但是当我加载我们的本地站点时，我失去了对 ie 对象的控制，我无法检查 readyState，我无法退出。

Here is the error I get.

这是我得到的错误。

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

运行时错误'-2147023179 (800706b5)'：
自动化错误
接口未知

Every so often I instead get this message:

每隔一段时间，我就会收到这条消息：

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

运行时错误“-2147417848 (80010108)”：
自动化错误
调用的对象已与其客户端断开连接。

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

单击 Debug 表示ie.readyState，我将其注释掉，然后它指向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

回答by Vigneshts88

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

无需调整 IE 设置

回答by Jim M

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.

这就是正在发生的事情。当您的浏览器在内部“跳转”到不同的安全区域时 - 从本地地址到内部网地址或互联网地址 - IE 关闭/删除 IE 的当前进程并打开另一个具有更严格安全性的进程。旧的和新的 IE 进程是父 IE 任务的 IE 子进程，因此您不会通过查看浏览器或在任务管理器中查看进程来看到它发生。如果您使用 Process Explorer（Microsoft 免费提供），您可以看到这种情况发生。

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.

在这种类型的环境中，您需要做的是使用上面的 Anup Upadhyay 的解决方案。他的代码片段查看所有 IE 任务（父和子没有区别），并找到指向原始进程提供的网址的新 IE 进程。只要您没有对同一地址打开多个浏览器，它就会可靠地找到新进程，并像您期望的那样继续运行。

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

注意：您可能还需要使用InternetExplorerMedium对象而不是InternetExplorer对象。它也更擅长“坚持”一个会议。

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

这是我的版本，几乎与 Anup 的版本相同。

References for MS Office VBA:

MS Office VBA 参考资料：

• Microsoft Internet Controls

• Microsoft Shell Controls and Automation

• 微软互联网控制

• Microsoft Shell 控件和自动化

n

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

回答by Rick

Try this one:

试试这个：

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

回答by Gove

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.

我们在使用 IE9 时遇到了这个问题。它是由安全设置引起的。在“Internet 选项”对话框（工具... Internet 选项）的“安全”选项卡上有一个名为“启用保护模式”的设置。每个“区域”可以有不同的“启用保护模式”设置我们取消选中“启用保护模式”，这解决了我们的问题。我认为正在发生的事情是，当切换到处于保护模式的区域时，IE 会启动一个新进程来处理该区域中的流量（可能会杀死旧进程）。发生这种情况时，您的 VBA 中的 IE 对象变量会与 Internet Explorer 断开连接。

回答by Varyl

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.

好吧，刚刚找到了解决办法，无奈之下决定尝试加载127.0.0.1而不是localhost，果然没有问题，于是解决了本地内网服务器的ip地址，现在可以了。我真的不明白为什么，但这已经解决了我的问题。

回答by Anup Upadhyay

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

'我们在这里遇到错误，我找到了一个小的棘手的替代解决方案，如下所示'参考 Microsoft Shell 控件和自动化'使用下面的代码为丢失的连接问题重新分配 IE 对象

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

回答by xameeramir

For me, the following worked:

对我来说，以下工作：

1. Open Internet explorer
2. Go to tools
3. Goto Internet options
4. Switch to Security tab
5. Click Trusted sites
6. Click sites
7. You will see the site url, select it
8. Click Remove
9. Click close, apply and Ok

1. 打开 Internet Explorer
2. 转到工具
3. 转到 Internet 选项
4. 切换到安全选项卡
5. 单击受信任的站点
6. 点击网站
7. 您将看到网站网址，选择它
8. 单击删除
9. 单击关闭，应用和确定

Ps. I tested these steps on IE 8 browser.

附言。我在 IE 8 浏览器上测试了这些步骤。

回答by ozmike

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.

请注意，通常您只需要使用 InternetExplorerMedium（参见其他答案），仅供参考，您也可以使用 Url 属性 obj.LocationURL 而不是窗口名称，这样会更准确。查看其他答案。

回答by engineer_dave

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.

我在使用我们公司内部网内的网站时遇到了完全相同的问题，该网站也包含在我信任的网站中。禁用保护模式允许我检查 ReadyState，但前提是我禁用 Internet 区域以及 Intranet 和受信任站点。

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?

在受信任的站点上检查 ReadyState 时，为什么为 Internet 区域启用保护模式会导致自动化错误？有没有办法在不禁用 Internet 区域的保护模式的情况下解决这个问题？

回答by Joseph

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:

试试这个代码（改编自http://www.excely.com/excel-vba/ie-automation.shtml）。希望它有帮助：

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