Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA code to interact with specific IE window that is already open

I am having trouble finding out how to get excel VBA to interact with a specific IE Window that is already open.

Here my process:

I open IE, navigate to the website, enter my username and pw, click log in. A new IE window opens that I use to enter member numbers and search for membership info.

I am trying to create a VBA script that will interact with the IE window that opens after I successfully log in (to begin to automate some of the more mundane tasks). I have seen tons of tutorials online about how to have VBA open a new IE window, and then interact with it. However, I am really struggling to find info about how to select a specific already open IE window and interact with it.

I have tried this as a quick test to see if I could select the correct IE window as an object that I can interact with.

Sub getIE()
Dim sh As Object, oWin As Object, IE As Object

Set sh = CreateObject("Shell.Application")

For Each oWin In sh.Windows
    If TypeName(oWin.document) = "HTMLDocument" Then
        Set IE = oWin
        Exit For
    End If
Next

MsgBox IE.document.URL

End Sub

The message box appears and shows the URL for initial IE window, but I don't know how to get it to select the IE window that I typically use to actually get member info.

I took several technical classes years ago when I was in school, but has been a while since I have flexed any tech muscles. Any help would be greatly appreciated!

like image 999
user1238528 Avatar asked Feb 17 '14 19:02

user1238528


People also ask

How do I run a certain part of a macro?

Click anywhere in Macro1. Press F8 to start single-step mode. There will be a yellow arrow to the left of the line Sub Macro1(). Drag this arrow down to the line where you want to start.

How do you use vbNewLine?

After the ampersand (&) symbol hit the space bar and get the VBA constant “vbNewLine.” After the constant “vbNewLine,” hit one more time space bar and add the ampersand (&) symbol. After the second ampersand (&) symbol type one more space character, add the next line sentence in double-quotes. Ok, we have done.

How do I run a macro in Internet Explorer?

To do so, open Internet Explorer and in tools->Internet options , click on Content tab and then under AutoComplete, click on Settings and check box for forms. If you fill up a form once, it will be recorded and next time you could just click on textbox to fill it up.


2 Answers

Here's what I use. It counts the number of open instances of IE and then steps through each instance and determines the url and title of the web page. You can then compare the title or url against the title or url you are looking for. The following example compares titles using the "Like" function, but like I said you could compare urls if you like. Also, if you are looking for a title or url that doesn't change you can use "=" instead of "Like".

marker = 0
Set objShell = CreateObject("Shell.Application")
IE_count = objShell.Windows.Count
For x = 0 To (IE_count - 1)
    On Error Resume Next    ' sometimes more web pages are counted than are open
    my_url = objShell.Windows(x).Document.Location
    my_title = objShell.Windows(x).Document.Title

    If my_title Like "XYZ" & "*" Then 'compare to find if the desired web page is already open
        Set ie = objShell.Windows(x)
        marker = 1
        Exit For
    Else
    End If
Next

If marker = 0 then
    msgbox("A matching webpage was NOT found")
Else
    msgbox("A matching webpage was found")
End If
like image 141
ron Avatar answered Oct 15 '22 21:10

ron


The above code worked well for me. I turned it into a function call as follows

Set ieFindUser = FindIEObject("Find and List Users")            'Find handle for popup window.

The Function

Public Function FindIEObject(target As String) As InternetExplorerMedium
    Set objShell = CreateObject("Shell.Application")
    IE_count = objShell.Windows.Count
    For x = 0 To (IE_count - 1)
        On Error Resume Next    ' sometimes more web pages are counted than are open
        my_url = objShell.Windows(x).Document.Location
        my_title = objShell.Windows(x).Document.Title
        If my_title = target Then 'compare to find if the desired web page is already open
            Set FindIEObject = objShell.Windows(x)
            Exit For
        End If
    Next
End Function
like image 26
Steven Alpha Avatar answered Oct 15 '22 22:10

Steven Alpha