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

 

[RoryA]

Code looks ok to me - have you tried stepping through it?

 

[castertroy]

Thanks, I have tried stepping through it. It highlights the line...

Set IE = CreateObject("internetexplorer.application")

... but doesn't actually give an error message.

 

[RoryA]

What happens when you press f8 on that line then?

 

[castertroy]

It highlights the line in yellow. If I press F8 again it skips to the line...

Set ws = Worksheets("Sheet1")

... and also highlights in yellow.

I've noticed I am now on IE7 instead of IE6 which I used to use as well. Not sure if that makes any difference.

 

[RoryA]

keep pressing f8 until either an error occurs or the sub ends (that's what I meant by stepping through the code). I am assuming that you have Google set up as your home page?

 

[castertroy]

No error occurs and when I keep pressing F8 it ends up looping between the line ...

If IE.LocationURL = "[URL]http://www.google.co.uk/[/URL]" Then

and the lines....

End If
    Next

Google is not set up as my homepage but I have an open instance of IE which is already navigated to it.

 

[RoryA]

What are you expecting it to do then if you already have an open visible instance with Google loaded?

 

[castertroy]

Sorry, I have a process whereby an online form gets completed based on data in Excel. The old code doesn't work with my new setup on Excel 2003 and IE7.

I am using the google homepage here as an example of how Excel no longer navigates to the open instance of Google.

Once I can get Excel to navigate to the open instance of Google I should be ok. I can go back downstairs to my old PC, open the exact same document and bingo it flicks to google. Up here, it just pauses for a second then... nothing.

 

[RoryA]

Why do you need to navigate to it rather than just manipulating it?