VBA 与 Internet Explorer 的交互

VBA interaction with internet explorer

提问by orangehairbandit

The macro I am building takes names from an Excel spreadsheet, opens Internet Explorer, and searches the online directory. After searching the directory, it pulls up a Java form with the manager's name in it. I am able to manually tab to the manager name, right click, copy shortcut, and then post it back on the spread sheet. However, I am having problems with consistent tabbing and copying the shortcut.

我正在构建的宏从 Excel 电子表格中获取名称，打开 Internet Explorer，并搜索在线目录。搜索目录后，它会调出一个 Java 表单，其中包含经理的姓名。我可以手动切换到经理名称，右键单击，复制快捷方式，然后将其发回电子表格。但是，我在使用一致的制表符和复制快捷方式时遇到了问题。

1. Is there a simple way of bringing focus back onto the IE window?
2. How do you copy a shortcut without manually clicking it?

1. 有没有一种简单的方法可以将焦点重新放在 IE 窗口上？
2. 如何在不手动单击的情况下复制快捷方式？

Code:

代码：

Sub Macro1()
'
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")

ie.Visible = True
ie.navigate "****url****"

While ie.busy
    DoEvents
Wend

ie.document.getElementById("SSOID").Value = "Z19516732"
ie.document.getElementById("Advanced").Checked = False
ie.document.all("Search").Click

'this loop is to slow the macro as the java form is filled from the search
For i = 1 To 400000000  
    i = i + 1
Next i

'ie.Object.Activate
ie.document.getElementById("Advanced").Checked = False
ie.document.getElementById("SSOID").Focus
Application.SendKeys "{TAB 6}" ', True

'bring up the control menu/right click
Application.SendKeys "+{F10}"

'copy shortcut is 8 items down on the list
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"

'enter was not working so the shortcut for the menu is 't'
'SendKeys "{ENTER}"
Application.SendKeys "{t}"

Windows("Book21").Activate
Range("A1").Select
ActiveSheet.Paste

End Sub

回答by Gaffi

At the beginning of your module, put this line of code:

在模块的开头，放置以下代码行：

Public Declare Function SetForegroundWindow Lib "user32" (ByVal HWND As Long) As Long

This is called a Declare Statementand will allow you to access the SetForegroundWindowfunction that is built into Windows. This function lives in the user32DLL of the Windows system. There are actually a number of other functions among multiple DLLs that are accessible to VBA in this way (see link for more examples).

这称为 aDeclare Statement并且将允许您访问SetForegroundWindow内置于 Windows 中的功能。这个函数存在于user32Windows 系统的DLL 中。实际上，VBA 可以通过这种方式访问​​多个 DLL 中的许多其他函数（有关更多示例，请参见链接）。

In your code, while interacting with your IE object, record the HWND(handleto that window) like so:

在您的代码中，在与 IE 对象交互时，像这样记录HWND（该窗口的句柄）：

Dim HWNDSrc As Long
HWNDSrc = ie.HWND

Then, after you've interacted with Java, use this to continue:

然后，在与 Java 交互后，使用以下命令继续：

SetForegroundWindow HWNDSrc

This tells the Windows system to set the window identified by the HWNDas the foreground window (as the name implies).

这告诉 Windows 系统将 标识的窗口设置HWND为前台窗口（顾名思义）。

However, this may not be necessary, depending on how you are interacting with IE. In other words, if you don't need to see/touch the window, you can still interact using the object as you have in your code already.

但是，这可能不是必需的，这取决于您与 IE 的交互方式。换句话说，如果您不需要查看/触摸窗口，您仍然可以像在代码中一样使用该对象进行交互。

There are ways to get the shortcut you are looking for using code like GetElementById()and GetElementsByTagName()(see here for more info), but it will depend on how the source was created. e.g. an <a href="...>link should be relatively easy to pull, if you know the HTML source.

有很多方法可以使用GetElementById()和GetElementsByTagName()（有关更多信息，请参见此处）之类的代码获取您正在寻找的快捷方式，但这取决于源的创建方式。例如<a href="...>，如果您知道 HTML 源代码，链接应该相对容易拉取。

---

---

After reviewing your code a second time, I noticed you use a loop to 'slow down' the macro. I have a function I use all the time for similar methods of my own. Hopefully this will help in you getting done what you need. I've modified my code below from my own original, since I had additional specifics that don't apply to your case. If there are any errors with it, I can adjust as needed.

第二次查看您的代码后，我注意到您使用循环来“减慢”宏。我有一个一直用于我自己的类似方法的函数。希望这将有助于您完成所需的工作。我已经从我自己的原始代码修改了下面的代码，因为我有其他细节不适用于您的情况。如果有任何错误，我可以根据需要进行调整。

Public Sub WaitForIE(myIEwindow As InternetExplorer, HWND As Long, WaitTime As Integer)

    ' Add pauses/waits so that window action can actually
    ' begin AND finish before trying to read from myIEWindow.

    ' myIEWindow is the IE object currently in use
    ' HWND is the HWND for myIEWindow
    ' The above two variables are both used for redundancy/failsafe purposes.
    ' WaitTime is the amount of time (in seconds) to wait at each step below. 
    ' This is variablized because some pages are known to take longer than 
    ' others to load, and some pages with frames may be partially loaded,
    ' which can incorrectly return an READYSTATE_COMPLETE status, etc.

    Dim OpenIETitle As SHDocVw.InternetExplorer

    Application.Wait DateAdd("s", WaitTime, Now())

    Do Until myIEwindow.ReadyState = READYSTATE_COMPLETE
        ' Wait until IE is done loading page and/or user actions are done.
    Loop

    Application.Wait DateAdd("s", WaitTime, Now())

    While myIEwindow.Busy
        DoEvents  ' Wait until IE is done loading page and/or user actions are done.
    Wend

    On Error Resume Next
    ' Make sure our window still exists and was not closed for some reason...
    For Each OpenIETitle In objShellWindows
        If OpenIETitle.HWND = HWND Then
            If Err.Number = 0 Then
                Set myIEwindow = OpenIETitle
                Exit For
            Else
                Err.Clear
            End If
        End If
    Next OpenIETitle
    On Error GoTo 0

End Sub