?
Solved

Automate a web site from Access

Posted on 2012-09-05
5
Medium Priority
?
410 Views
Last Modified: 2012-09-12
I have the following code. It works just fine. However I need to select a check box NOT the Select All box. I am trying to select a different load offer based on criteria I have. Any thoughts would be greatly appreciated.
Sub SteelScape()
Dim strNavigate As String
Dim objIE As Object
Dim frm As Object
Dim colInp As Object
Dim inp As Object

On Error GoTo LogError
    Set objIE = CreateObject("InternetExplorer.Application")
    strNavigate = "http://lomas.isg2tech.com/Steelscape/cgi-bin/wlogin?"

    objIE.navigate strNavigate
    objIE.Visible = True

    'allow time for the page to finish loading so I can assign userid and password
    Do While objIE.busy Or objIE.readystate <> 4
        DoEvents
    Loop

    With objIE.Document.Forms("wlogin")
        .UserId.Value = "sherman"
        .Passwd.Value = "she7845"
        .cmd.Click
    End With
    'allow time for the page to load after sign in
    Do While objIE.busy Or objIE.readystate <> 4
        DoEvents
    Loop

    'move to the page I need after the log in page
    objIE.navigate "http://lomas.isg2tech.com/Steelscape/cgi-bin/wlmt204?tstamp= 20120501081019"

    'assign the combo box on the page to Tendered
    Do While objIE.busy Or objIE.readystate <> 4
        DoEvents
    Loop
    'select all of the tendered loads and accept them
    Set frm = objIE.Document.Forms("wlmt204")

    With frm
        .pstat.Value = "T"
        .submit
    End With
    
    Do While objIE.busy Or objIE.readystate <> 4
        DoEvents
    Loop
    Set frm = objIE.Document.Forms("wlmt204")

    frm.selall.Click

    Do While objIE.busy Or objIE.readystate <> 4
        DoEvents
    Loop

    Set frm = objIE.Document.Forms("wlmt204")

    Set colInp = frm.getelementsbytagname("INPUT")

    For Each inp In colInp
        'Debug.Print inp.Value
        If inp.Value = "Accept Selected Loads" Then Exit For
    Next inp
    inp.Click

ExitPoint:
    Set objIE = Nothing
    On Error GoTo 0
    Exit Sub
LogError:
    Call LogError(Err.Number, Err.Description, "CSA2010 Error", , True)
    objIE.Quit
    Resume ExitPoint
End Sub

Open in new window

0
Comment
Question by:JArndt42
  • 3
  • 2
5 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38370554
First off, are you sure it's okay to have a login and password in the code you provided?
Secondly, what do you mean by load offer and what criteria are you referring to?
0
 
LVL 1

Author Comment

by:JArndt42
ID: 38372957
Irog, Thank your for your concern. It is OK as it is not sensitive information. The log on is merely so that the trucking companies can select loads and it assigns them to the correct company. Here is some of the source code of the web site when it is has loads to tender. Note that the select all and then two check boxes with loads to be tendered. I want to be able to select only one of the boxes where name="hsload" value="1217351"> is the pick up number I get from a notification email. The process is such, and it works with the select all fyi. I get an email notification about a load offer. I have a procedure that checks the email to make sure the load offer meets the criteria we need. If so then it opens the website, signs on, selects all and accepts them. I do NOT want it to select all. I want ONLY the one where the source code name="hsload" value="1217351"> is the same as the one I pulled out of the email. How do I select just the one load?

 <TD class="header" align="center"><INPUT TYPE="checkbox" NAME="selall" onclick="selectAll();"></TD>
<TD class="header" align="center">Load<BR>Number</TD>
<TD class="header" align="center">Pro<BR>Number</TD>
<TD class="header" align="center">Shipper</TD>
<TD class="header" align="center">Consignee</TD>
<TD class="header" align="center">Equipment<BR>Type</TD>
<TD class="header" align="center">Estimated<BR>Weight(Lbs)</TD>
<TD class="header" align="center">Accepted<BR>Weight<br>(Lbs)</TD>
<TD class="header" align="center">Estimated<BR>Miles</TD>
<TD class="header" align="center">Earliest <BR>Delivery<br>Date</TD>
<TD class="header" align="center">Pickup Date</TD>
<TD class="header" align="center">Delivery Date</TD>
<TD class="header" align="center">Tender Date</TD>
<TD class="header" align="center">Status</TD>
<TD class="header" align="center">Freight<BR>Resp.</TD>
<TD class="header" align="center">Notes</TD>
</TR>
<TR>
<TD class="row1" align="center" ><INPUT TYPE="checkbox" NAME="sel" VALUE="001KAS1 12173510130" style="" ></TD>
<TD class="row1" >1217351
<INPUT type="hidden" name="hskey" value="001KAS1 12173510130">
<INPUT type="hidden" name="hsload" value="1217351">
<INPUT type="hidden" name="h1weig" value="35,530">
</TD>
<TD class="row1" align="center" ><INPUT maxLength="21" size="10" name="wspro" value=""></TD>
<TD class="row1" nowrap valign="top" >STEELSCAPE KALAMA<BR>PO BOX 606<BR>KALAMA, WA 98625</TD>
<TD class="row1" nowrap valign="top" >ASCD - ASC PROFILES<BR>2110 ENTERPRISE BLVD<BR>W. SACRAMENTO, CA 95691</TD>
<TD class="row1" align="center" >Regular Flatbed</TD>
<TD class="row1" align="center" >35,530</TD>
<TD class="row1" align="center" >35530</TD>
<INPUT type="hidden" name="wscnac" value="35530">
<INPUT type="hidden" name="h1err" value="">
<TD class="row1" align="center" >615</TD>
<TD class="row1" align="center" nowrap style="background-color: red;">9/06/12</TD>
<TD class="row1" align="center" nowrap style="background-color: red;">9/06/12</TD>
<TD class="row1" align="center" nowrap ></TD>
<TD class="row1" align="center" nowrap >09/06/12 7:51</TD>
<TD class="row1" align="center" >Tendered</TD>
<TD class="row1" align="center" >S</TD>
<TD class="row1" >&nbsp;</TD>
</TR>
<TR>
<TD class="row2" align="center" ><INPUT TYPE="checkbox" NAME="sel" VALUE="001KAS1 12174340130" style="" ></TD>
<TD class="row2" >1217434
<INPUT type="hidden" name="hskey" value="001KAS1 12174340130">
<INPUT type="hidden" name="hsload" value="1217434">
<INPUT type="hidden" name="h1weig" value="35,600">
</TD>
<TD class="row2" align="center" ><INPUT maxLength="21" size="10" name="wspro" value=""></TD>
<TD class="row2" nowrap valign="top" >STEELSCAPE KALAMA<BR>PO BOX 606<BR>KALAMA, WA 98625</TD>
<TD class="row2" nowrap valign="top" >ASCD - ASC PROFILES<BR>2110 ENTERPRISE BLVD<BR>W. SACRAMENTO, CA 95691</TD>
<TD class="row2" align="center" >Regular Flatbed</TD>
<TD class="row2" align="center" >35,600</TD>
<TD class="row2" align="center" >35600</TD>
<INPUT type="hidden" name="wscnac" value="35600">
<INPUT type="hidden" name="h1err" value="">
<TD class="row2" align="center" >615</TD>
<TD class="row2" align="center" nowrap style="background-color: red;">9/06/12</TD>
<TD class="row2" align="center" nowrap style="background-color: red;">9/06/12</TD>
<TD class="row2" align="center" nowrap ></TD>
<TD class="row2" align="center" nowrap >09/06/12 7:51</TD>
<TD class="row2" align="center" >Tendered</TD>
<TD class="row2" align="center" >S</TD>
<TD class="row2" >&nbsp;</TD> 

Open in new window

0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38378517
Sorry that I didn't get back to you right away.  I'm not sure if you've already found the answer or are still looking, but if the latter, this seems to work.  I stuck it in place of lines 48 to 54 in your original post:

    Dim Doc As HTMLDocument
    Set Doc = objIE.Document
    Set colInp = Doc.getElementsByName("sel")
    For Each inp In colInp
        'Debug.Print inp.Value
        If InStr(inp.Value, "1217350") > 0 Then
            inp.Checked = True
        End If
    Next inp

Open in new window

You just need to add a Reference to Microsoft HTML Object Library
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38379277
Or if you want to do it without adding the reference I mentioned, use this instead (replace lines 48 to 54):
    Set colInp = objIE.Document.getElementsByName("sel")
    For Each inp In colInp
        'Debug.Print inp.Value
        If InStr(inp.Value, "1217520") > 0 Then
            inp.Checked = True
        End If
    Next inp

Open in new window

0
 
LVL 1

Author Closing Comment

by:JArndt42
ID: 38387885
Thank you for that. works like a charm. appreciate your assistance.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question