Controlling IE no longer works in different Excel version

[castertroy]

Hi,

I had a code that runs fine in Excel 2000 but now I am on Excel 2003 when I click on the button to start the macro nothing happens. I have checked the references and the Microsoft Internet Controls and HTML object boxes are ticked. Here is the code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 09/03/2011 by X'

Dim Shell As Object
    Dim IE As Object
   Set IE = CreateObject("internetexplorer.application")
Dim oIE As Object
    Dim oForm As Object
    Dim objShellWins As SHDocVw.ShellWindows
    Dim objIE As SHDocVw.InternetExplorer
    Dim objDoc As Object
    Dim usdURL As String
    Dim myUSD As String
    Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
    
    Set Shell = CreateObject("Shell.Application")
    
    
    For Each IE In Shell.Windows
    If IE.LocationURL = "[URL]http://www.google.co.uk/[/URL]" Then
    
            IE.Visible = True
            
                Do While IE.Busy: DoEvents: Loop
    Do While IE.ReadyState <> 4: DoEvents: Loop
            
            
    
    
     
    End If
    Next
'
End Sub

If I use an older PC here it works fine on Windows/Excel 2000 but for some reason it no longer runs. Does anyone have any ideas how to rectify this?

Thanks

 

[castertroy]

Sorry I've probably not explained this very well. The process is:

1.(IE7 already open at correct page, an internet form)
2. click button in excel
3. Screen switches to IE and form is completed
4. Screen switches back to excel

I have a feeling it might be to do with the references in VBA but that is a real grey area for me.

The code I have put on here is just the first bit, where the macro identifies that the google homepage is already open and switches to it.

 

[RoryA]

I can't see anything in your code that would bring an existing IE instance to the front. It creates a new hidden instance and sets the visibility of any Google pages to true, but in your case that's already visible anyway, and the hidden instance is not on Google.

 

[castertroy]

Interesting, thanks for that. I didn't even know there was such a thing as a 'hidden' instance. My original code was put together from a few online codes so I wouldn't say I entirely grasped how it worked.

Is there a simpler way to just bring the open instance to the front? Which part of my code relates to the 'hidden' instance?

 

[RoryA]

This bit creates the new hidden instance:

Set IE = CreateObject("internetexplorer.application")

Will there always be an existing instance open, or do you need to open a new browser then navigate to the site you want?

 

[castertroy]

Thanks again for your help.

The instance will always be open already. I just need to 'take control' of it.

 

[RoryA]

To bring it to the foreground:

Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 09/03/2011 by X'

Dim Shell As Object
    Dim IE As Object
    Dim oForm As Object
    Dim objShellWins As SHDocVw.ShellWindows
    Dim objIE As SHDocVw.InternetExplorer
    Dim objDoc As Object
    Dim usdURL As String
    Dim myUSD As String
    Dim ws As Worksheet
   Set ws = Worksheets("Sheet1")
    
    Set Shell = CreateObject("Shell.Application")
    
    For Each IE In Shell.Windows
    If IE.LocationURL = "http://www.google.co.uk/" Then
      
            IE.Visible = True
            SetForegroundWindow IE.hwnd
     
    End If
    Next
'
End Sub

 

[castertroy]

Fantastic! Thanks a lot for your help, this code works perfectly.

I was ready to give up but once again this board comes to the rescue.