IE automation by excel vba

[maxbre]

I want to find all the forms and input control names within a given IE webpage in order to fill them all automatically with some required values (in fact I hope to automate this part I’m now simply doing by ‘keyboard entering’)

To do that I need to be able to refer to the proper forms and input control names but because it’s a complex web page written with asp (I’m a bit lost with that!) I’m wondering if there is a vba code to retrieve such information (all form and control names form a given web page)?

I have worked out this code which is failing in the referencing of the proper form or control names, I presume…. (error 440)


Set IE = CreateObject("InternetExplorer.Application")

With IE
.navigate http://....... ‘ it's a password protected webpage
.Visible = True

With .document.forms("invia") ' it’s failing here
.c76.Value = "09"
.submit
End With
End With

Set IE = Nothing

 

[John_w]

Maybe this will help. Change the URL as necessary.

Public Sub List_Forms()

    'Navigate to a URL and output details of forms and form elements to Immediate Window
     
    Dim URL As String
    Dim IE As Object
    Dim f As Integer, e As Integer
    
    URL = "http://www.mrexcel.com/forum/forumdisplay.php?f=10"
    
    Set IE = CreateObject("InternetExplorer.Application")
    
    With IE
        .Visible = True
        .navigate URL
        Do While .Busy Or .readyState <> 4: DoEvents: Loop
    End With
    
    Debug.Print "IE.document.forms.Length=" & IE.document.forms.Length
 
    For f = 0 To IE.document.forms.Length - 1
    
        With IE.document.forms(f)
            Debug.Print vbNewLine & "IE.document.forms(" & f & ")"
            Debug.Print "  .Name=" & .Name & " .ID=" & .ID & " .action=" & .action & " .method=" & .method
            Debug.Print "  .elements.Length=" & .elements.Length
        End With
        
        For e = 0 To IE.document.forms(f).elements.Length - 1
            With IE.document.forms(f).elements(e)
                Debug.Print "  .elements(" & e & ")"
                Debug.Print "    .tagName=" & .tagName & " .Name=" & .Name & " .ID=" & .ID & " .Value=" & .Value
            End With
        Next

    Next
    
    Set IE = Nothing
    
End Sub

 

[maxbre]

yes, thank you it's very useful

apparently the link to the web page I'm following it's not the name of page from which I need to retrieve the information because it's generated dinamically....

I'll ask more details to the webmasters

thank you very much for your code, it's really smart!

can you suggest me some web addresses where to find some information, resources and examples on IE object automation?

 

[John_w]

can you suggest me some web addresses where to find some information, resources and examples on IE object automation?

These are useful references:

Internet Explorer Development - Hosting and Reuse
http://msdn.microsoft.com/en-us/library/aa752038(VS.85).aspx

WebBrowser Control
http://msdn.microsoft.com/en-us/library/aa752040(VS.85).aspx

InternetExplorer Object
http://msdn.microsoft.com/en-us/library/aa752084(VS.85).aspx

HTML Object Library and HTML Elements (tags)
http://msdn.microsoft.com/en-us/library/ms533029(VS.85).aspx


You'll find coding easier if you use early binding and named objects from the HTML Object Library to access elements in IE.Document, rather than using late binding and Dim'ing variables as Object. To do this, set a reference to Microsoft HTML Object Library in Tools/References in the VB editor. Similarly, you could set a reference to Microsoft Internet Controls and then you can declare IE with:

Dim IE As InternetExplorer
Set IE = New InternetExplorer

instead of:

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")

and then the VBA intellisense will show the members of the IE object.

 

[maxbre]

hi john_w and all others willing to help me (thanks a lot for that, indeed!)

I'm still in the search of a solution to the problem I posted
by using the above Public Sub List_Forms()

I've got, among many other things, the following element listing

....
.tagName=INPUT .Name=c69 .ID=c3 .Value=
.elements(61)
....

now I'm in the need to fill the input name c69 with a specific value and to do that I worked out this macro which is failing there.... (error 438)

any help for that? any idea on how to overcome this?


Public Sub Insert_Forms()

Dim IE As InternetExplorer
Set IE = New InternetExplorer


Dim URL As String
Dim f As Integer, e As Integer

URL = "http://www.arpa.veneto.it/script/air_quality2/admin/inserisci.asp"


With IE

.navigate URL
.Visible = True
Do While .Busy Or .readyState <> 4: DoEvents: Loop

End With


For f = 0 To IE.document.forms.Length - 1

For e = 0 To IE.document.forms(f).elements.Length - 1

With IE.document.forms(f).elements(e)
.c69.Value = "999" 'failing here!
End With

Next
Next


Set IE = Nothing

End Sub

 

[John_w]

Why are you looping? To set your c69 field, all you need is (untested):

IE.document.forms(f).elements(60).Value = "999"

where f is the index number of the form - look at the output of my program to determine this number. Your ".elements(61)" refers to the next form element, so your c69 field is elements(60).

IE.document.forms(f).c69.Value = "999"

might also work.

 

[maxbre]

the problem is I can't get the number of the form and that's why I've been forced to loop

 

[John_w]

The quick and dirty solution then is:

With IE.document.forms(f).elements(e)
  If .Name = "c69" Then
    .Value = "999"
    Exit For
  End If
End With

However, if you need to loop and exit prematurely from the loops I would use While Wends or Do Loops with a flag to control the exit, rather than For Next and Exit For, which is like a GoTo IMO and therefore bad practice.