VBA interaction with internet explorer

Question

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.

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?

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

Solution

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 Statement and will allow you to access the SetForegroundWindow function that is built into Windows. This function lives in the user32 DLL 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).

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

Dim HWNDSrc As Long
HWNDSrc = ie.HWND

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

SetForegroundWindow HWNDSrc

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

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.

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.

---

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

Comments

[orangehairbandit]

i tried that setForegroundWindow before, but this time it worked, Thanks! The html source however does not show the link that is needed. Though the link iss not specfically what i need. I need the text (The manager's name), but i can not copy it with a right click. Maybe there's a simple way of highlighting the text? I was planning on just copying and pasting the link then do some quick lines to clean up the name out of hte link form.

[orangehairbandit]

Thanks Gaffi! That first part with the window focusing workes like awesome. Like i said I tried that before, but you must've had the magic touch. As of the second part of my question, the create shortcut, I found my mistake. For whatever reason, the ".Focus" i was using for the search compenent, was just simply focusing but not selecting. So when i switched it to ".select" and specified hte sendkeys with a application.sendkeys "~" (The enter key), it all came together! My first time here on stackflow as a user not just a reader and its pretty sick nasty! Thanks!

[Gaffi]

@orangehairbandit Glad I could help!

[orangehairbandit]

I'm having an error with your edited comment code for the pause. When declaring the sub, you have myIEwindow as InternetExplorer. Is InternetExplorer a recognized datatype or object? I keep getting a complie error: user-defined type not defined

[Gaffi]

No, you'll have to reference the proper library. From the VBA editor, select Tools-->References and check "Microsoft Internet Controls". If it is not listed, for me on Win Server 2k3, this is located in C:\Windows\System32\ieframe.dll, but this may vary for you. Also offering an alternative in my answer...

[Gaffi]

@student Sorry, no, I don't have any experience with that dialog. But I'm sure someone here will be able to help you. Best of luck!