Get existing IE via VBA

Question

My goal is to scrape data off multiple webpages on the same site into Excel. I have these pages open in tabs within IE8. I have no other IE windows open.

I have tried the following to open IE:

AppActivate "Microsoft Internet Explorer provided by [my company]"
' It loses focus, the titlebar flashes for a fraction of a second 
' .. another code ..
Dim ShellApp
Set ShellApp = CreateObject("Shell.Application")
Dim ShellWindows
Set ShellWindows = ShellApp.Windows()
Dim i
For i = 0 To ShellWindows.Count - 1
    If InStr(ShellWindows.Item(i).FullName, "iexplore.exe") <> 0 Then
        Set IEObject = ShellWindows.Item(i) 
    End If
Next
' Did absolutely nothing, grabbed this code from another solution on stackoverflow

I also tried GetObject (I do not want to use the CreateObject method) as follows

dim ie As InternetExplorer 'also tried As Object and other variation
set ie = GetObject(, "InternetExplorer.Application")
'However this is not workable due to security risks and Microsoft 
' disabled GetObject for IE by design. 

The reason I don't want to use CreateObject or any of the variation is because I already have the tabs open ready to be scraped. AppActivate works with Microsoft Excel, but not IE. I cannot do exact titlebars as follows:

AppActivate "Website name - name page - Microsoft Internet Explorer provided by [my company]"

The reason I can't use this because of the tabs with varying named pages, changing the title constantly. I also tried:

AppActivate InternetExplorer.Application

AppActivate InternetExplorer

AppActivate " - Microsoft Internet Explorer"

AppActivate "Microsoft Internet Explorer"

All of the above either are not recognized or merely flash for a fraction of a second (as mentioned in first code). I have tried other methods but they create a new IE instance when I want to use the existing IE with opened multi-tabs.

In a different code:

AppActivate "[name of page] - Microsoft Internet Explorer provided by [my company]"

worked until I changed it to try and reference IE, no matter what page I'm on. Then the old AppActivate code will no longer work. I broke it. It was dumb of me to not have a back up.

System I am running:

Windows 7, IE8 (I'm waiting on my company to upgrade), Excel 2010

Answer 1

A compressed combination of the above answers, here's what works for me.

---

GetIE Function

(No references required.)

Function GetIE() As Object
'return an object for the open Internet Explorer window, or create new one
  For Each GetIE In CreateObject("Shell.Application").Windows() 'Loop to find
    If (Not GetIE Is Nothing) And GetIE.Name = "Internet Explorer" Then Exit For 'Found!
  Next GetIE
  If GetIE Is Nothing Then Set GetIE=CreateObject("InternetExplorer.Application") 'Create
  GetIE.Visible = True 'Make IE window visible
End Function

Example Usage:

Sub demo()
    Dim ie As Object
    Set ie = GetIE                                        'get new/existing IE object
    ie.Navigate "http://stackoverflow.com", 2             '2=don't keep history
    Do: DoEvents: Loop While ie.Busy or ie.ReadyState <> 4'wait til loaded
    Stop                                                  'do your stuff
    ie.Quit                                               'close IE
    Set ie = Nothing                                      'clean up
End Sub

Answer 2

Made some changes and it works now:

Function GetIE() As Object

    Dim ShellApp As Object, ShellWindows As Object
    Dim IEObject As Object

    Set ShellApp = CreateObject("Shell.Application")

    Set ShellWindows = ShellApp.Windows()

    Dim item As Object
    On Error GoTo 0
    Dim sName As String
       
       For Each ObjWind In ShellWindows
        'On Error Resume Next
         If (Not ObjWind Is Nothing) Then
            sName = ObjWind.Name
             If sName = "Internet Explorer" Then
                Set IEObject = ObjWind
                Exit For  'No need to continue....
             End If
         End If

        Next
        
    If IEObject Is Nothing Then Set IEObject = CreateObject("InternetExplorer.Application")

    Set ShellApp = Nothing

    Set GetIE = IEObject

End Function