VBA Into IE Automation problem

[supdawg]

For the past two days, I have been working hard trying to figure out how to automate an internal site here at work so we can process bulk items via excel on an internal site.

I have made some progress today, but now I am totally stuck. I have been reading eveything I can on htmldocument, but my head is spinning.

The website is a very simple website.

You take an input, click a button. This will them create a small HTML table with a text input box and another button.

Sub VisitWebsite()
Dim ie As Object
Dim tcode As String
Dim sURL As String
Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
sURL = [URL="http://sample.jsp/"]http://sample.jsp[/URL]
ie.navigate sURL
ie.Visible = True
tcode = "apples"
 
While ie.busy
DoEvents
Wend
Application.Wait Now() + TimeValue("00:00:03")
 
For Each ieForm In ie.document.forms
    ieForm(1).Value = tcode
    ieForm.submit
    Set ieForm = Nothing
    Exit For
Next

The code ieForm(1).Value = tcode works. The code ieForm.submit also hits submit on the button.

The problem is I cannnot figure out how to access the 2nd button which is generated when you click the 1st button. I have tried every variation of ieForm(1).Value. There appears to be only 3 forms (0, 1, 2). When I try 3 or greater, I get an error.

When I use ieForm(2).Value = "something", the Retrieve button text actually changes name to "something".

I've attached a screen shot of the webpage. I can insert text into the top Track Code box, and click Retrieve.

I can't change the text in the Routing Position field, and I cannot click the "Set" button. Any help would be greatly appreciated. I am still a beginner with VBA and it took me a while just to get it to work with the 1st input box.

 

[somnath_it2006]

Yes... Just post the code...

 

[supdawg]

When I post the code, it actually posts the rendered page on here. Not sure if that will help?

  <TABLE height="100%" width="100%" border=0>

    <TBODY><TR height="5%">
      <TD vAlign=top>
     <TABLE height=50 cellSpacing=0 width="100%" border=0>
  <TBODY><TR>
   <TD style="BACKGROUND-COLOR: rgb(185,185,255)" align=left>
      [FONT=Tahoma][SIZE=6][B] WIPS[/B][/SIZE][/FONT]
   
</TD>
   <TD style="BACKGROUND-COLOR: rgb(185,185,255)" align=right>
      [FONT=Monotype Corsiva][SIZE=6][B]Track Code Route Utility[/B][/SIZE][/FONT]  
    
</TD>
  
</TR>
     

</TABLE>
   
</TD>
 
</TR>
 <TR height="93%">
   <TD vAlign=top>


     <!-- Page body here... -->
  [CENTER]
    <TABLE width="100%" align=center>
       <TBODY><TR>
          <TD align=middle>
          <FORM action=TrackCodeRouteUtil.jsp method=post>
     <INPUT type=hidden value=getRoute name=action>                 
           [FONT=Verdana][SIZE=2][B]Track Code[B][/SIZE][/FONT]
           <INPUT name=trackCode>
           <INPUT type=submit value=Retrieve> 
          </FORM>
       
[/B][/B]</TD>         
    

</TR>
       <TR>
          <TD align=middle>
              


              [CENTER]
 
     
[/CENTER]

 
 
           <FORM action=TrackCodeRouteUtil.jsp method=post>
           <TABLE cellSpacing=0 width="75%" border=1>
            <THEAD style="BACKGROUND-COLOR: rgb(200,200,255)" align=middle>
            <TR>
             <TD>[FONT=Verdana][SIZE=2][B] Track Code[/B] [/SIZE][/FONT]</TD>
             <TD>[FONT=Verdana][SIZE=2][B] Order Number[/B] [/SIZE][/FONT]</TD>
             <TD>[FONT=Verdana][SIZE=2][B] Order Tie Number [/B][/SIZE][/FONT]</TD>
             <TD>[FONT=Verdana][SIZE=2][B] Routing Position [/B][/SIZE][/FONT]</TD>
            
</TR>
            
</THEAD>
            <TBODY align=middle>
            <TR>
             <TD>[FONT=Verdana][SIZE=2]43L1[/SIZE][/FONT]</TD>
             <TD>[FONT=Verdana][SIZE=2]924639[/SIZE][/FONT]</TD>
             <TD>[FONT=Verdana][SIZE=2]1[/SIZE][/FONT]</TD>
             <TD>
        <INPUT type=hidden value=setRoute name=action>                 
        <INPUT type=hidden value=43L1 name=trackCode>                 
              <INPUT value=5700 name=routePosition>
              <INPUT type=submit value= Set >
             
</TD>
            
</TR>
            
</TBODY>
           
</TABLE>
           </FORM>
 
          
</TD>
       <TD></TD>         
    

</TR>
    

</TABLE>
  
[/CENTER]

   
</TD>
 
</TR>
 <TR vAlign=bottom height="2%">
   <TD> <!-- Footer message here --></TD>
 
</TR>
  

</TABLE>

 

[somnath_it2006]

I think I found it...

Try this code for click the Set submit button:

   For x = 0 To 1
    
        For i = 0 To ie.Document.forms(x).Length - 1
            
            'MsgBox ie.Document.forms(x)(i).Value & " " & ie.Document.forms(x)(i).Type
            
            If ie.Document.forms(x)(i).Value = "Set" And ie.Document.forms(x)(i).Type = "submit" Then
                ie.Document.forms(x)(i).Click
                Exit For
            End If
    
        Next i
    
    Next x

 

[supdawg]

I think I found it...

Try this code for click the Set submit button:

   For x = 0 To 1
 
        For i = 0 To ie.Document.forms(x).Length - 1
 
            'MsgBox ie.Document.forms(x)(i).Value & " " & ie.Document.forms(x)(i).Type
 
            If ie.Document.forms(x)(i).Value = "Set" And ie.Document.forms(x)(i).Type = "submit" Then
                ie.Document.forms(x)(i).Click
                Exit For
            End If
 
        Next i
 
    Next x

Thank you somnath_it2006. Your code led me to figure out the syntax to click the last set button.

ie.document.forms(1)(3).Click

was the magic line of code to click the set. (Although when I tried to call it by name, it never worked. May have something to do with the spaces around the Set button )

Now I am having problems with the code not working within the module when ran at full speed.

If I slow it down and step through the code, these two lines of codes work great. However, when ran at full speed, they don't appear to work.

ie.document.getElementById("routePosition").Value = "5700"
ie.document.forms(1)(3).Click

I've tried to use both the AppActivate and Application.Wait to help keep the focus on the website and it does not work.

Either the code is going to fast, or its losing focus is all I can assume. Got any pointers on getting around this issue?

 

[somnath_it2006]

Use below method:

    'Loop unitl ie page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    Do Until ie.Document.ReadyState = "complete"
        DoEvents
    Loop

 

[supdawg]

Where do I insert this code?

 

[somnath_it2006]

Just after Navigation of Link:

     ' Link Navigation
     ie.Navigate "https://www.google.com/accounts/Login?hl=en&continue=http://www.google.co.in/"
    
    ' Visible the Internet Explorer
    ie.Visible = True
    
    'Loop unitl ie page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    Do Until ie.Document.ReadyState = "complete"
        DoEvents
    Loop

    ' Code for extract the data or Operate the controls on the page

If you want I can send you the example file...

 

[supdawg]

I think I got it. I had to put that loop statement after every button click in order for it to be stable.

Thank you for all the help. I REALLY appreciate it.

 

[somnath_it2006]

Yes... Put that code whenever you navigate through the webpage...