Automating IE - 'Object variable or with block variable not set'

[castertroy]

I have a code that works fine to take control of an IE window and complete a form. For some reason it isn't working for a specific webpage and is giving the dreaded runtime error 91. Here is the code:

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
    Dim UserNameInputBox As HTMLInputElement
Dim IeApp As Object

   Set ws = Worksheets("Sheet1")
    
    Set Shell = CreateObject("Shell.Application")
    
    For Each IE In Shell.Windows
    If IE.LocationURL = "mywebpage" Then
      
            IE.Visible = True
            SetForegroundWindow IE.hwnd
 
Set srch = IE.document.getElementById("strLOGON")
       Set rng = Range("='Sheet1'!A1")
       srch.Value = rng
 
End If
    Next
'
End Sub

I can't figure out why it works for other websites but not this one. Here is the HTML code for the field I am trying to populate.

        <td align="right" class="normal">Please enter UserID &nbsp<input type="text" onkeypress="return entsub(this.form)" maxlength="16" name="strLOGON" value="" /></td>
    </tr>

Can anyone think of a reason why an error is being generated? This code is tried and tested for 4 similar webpages and I am stumped as to why this one should be different.

 

[Ruddles]

Are you actually setting the URL to "mywebpage"?

If not, what are you setting the URL to?

Incidentally, your HTML didn't display - check it out. Try inserting a space after each < next time.

 

[castertroy]

No sorry I replace that bit of the code with the URL of the webpage I am working with, it is a work thing so I am playing safe and not putting the URL on here.

In the HTML code the field is named 'strLOGON' but no matter what I try I can't get the code to stick my ID in there. The IE window appears but in the background the code trips up in VBA and leaves the error code.

 

[Norie]

Is it the first part of the code that's failing?

ie the part that appears to be trying to 'grab' an open instance of IE?

If it is do you need to work with an already open instance?

You could just create an instance in code and then navigate to the URL you are interested in using it.

 

[castertroy]

Hi,

No it trips up at this line:

 srch.Value = rng

If for example I was to change it to

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
    Dim UserNameInputBox As HTMLInputElement
Dim IeApp As Object
 
   Set ws = Worksheets("Sheet1")
 
    Set Shell = CreateObject("Shell.Application")
 
    For Each IE In Shell.Windows
    If IE.LocationURL = [URL="http://www.google.co.uk/"]"http://www.google.co.uk/[/URL]" Then
 
            IE.Visible = True
            SetForegroundWindow IE.hwnd
 
Set srch = IE.document.getElementById("q")
       Set rng = Range("='Sheet1'!A1")
       srch.Value = rng
 
End If
    Next
'
End Sub

It seems to work fine and no error is generated.

 

[Norie]

Well that suggests the problem is here and that the element with the id 'strLOGON' is not being found.

Or an element with that id is being found but it's not the one you want.

(you can have elements with the same id on a page - not a good idea but you can.)

Set srch = IE.document.getElementById("strLOGON")

That could be for a number of reasons.

 

[castertroy]

Thanks for that. I have tried logging in manually then trying to replicate this process on the screen that follows the login stage but I have the same issue. It is like it can't 'find' any of the elements on the website.

Is there any way to run something that compiles a list of all the elements on a page, or identifies them in a different way? Normally I just look for the name="X" part of the HTML code.

 

[Norie]

Why are you looking at the 'name' attribute if you are trying to find something using it's ID attribute?

I know it works sometimes, but that's the problem - sometimes.

If I can't find an ID I will try the name but if that doesn't work I'll try some other method.

If what you are looking for is part of a form you could try to get a reference to the form and then try and get the element.

One other thing is that the page might not be fully loaded.

I kind of dismissed that earlier as I assumed since you were dealing with existing instances of IE the page would be fully loaded.

Do you know it it's a possibility it isn't loaded?

I'm afraid I don't really have much experience of working with existing instances, I always create the instance in the code and work from there.

 

[castertroy]

Interesting stuff for me there, thanks! I hadn't even realised the ID/name contradiction in my code. I did try the ID as well but I got the same error unfortunately.

When you say...

If what you are looking for is part of a form you could try to get a reference to the form and then try and get the element.

How would I do that?

I am pretty sure the page is fully loaded. I did try it your way as well, ie. create a new instance and then navigate to the page, but I ended up with the same error!

 

[Norie]

You could get a reference to a collection of all the forms in the document using GetElementsByTagName("FORM").

Since most pages don't have too many forms, actually it's usually just one, then it shouldn't be too hard to get the one you want.

Getting a reference to the form will help narrow down your search for the particular element you want.

By the way this is just a suggestion, it's pretty hard to help with this sort of thing without knowing the URL involved.

It's also pretty hard to come up with any generic code because of the many ways a page can be designed.