mercredi 22 juin 2016

Fill in website search bar using Excel VBA

I'm trying to make an Excel macro that inserts data into a web search form and then copies the results into a table. The web search form is not actually a "form", but a blank table so I can't just change the input value of the form because there is none:

<td valign="top">
    <table border="0" cellspacing="0" cellpadding="2">
        <tr>
            <th class="navLabelText" align="left">Order:</th>
            <td>
                <input class="navEditField" id="opt_ordernumber_int" name="ordernumber" type="text" size="6" maxlength="6" />
            </td>
        </tr>
    </table>
</td>
<td width="10">&nbsp;</td>

The HTML just continues with more of the same types of forms (I'm guessing coded in Java since the site is a .jsp). Is there any way that I can pass values into the blank table?

Here's what I have so far:

Sub featurecode()

Dim ie As Object
Dim doc As HTMLDocument
Dim links As IHTMLElementCollection
Dim link As HTMLAnchorElement
Dim i As Integer
Dim found As Boolean
Dim todaysURL As String

Dim objElement As Object
Dim objCollection As Object

Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = True 'false
ie.navigate "https://website.com"
Application.StatusBar = "Loading Feature Codes"

Do Until ie.readyState = IE_READYSTATE.complete: DoEvents: Loop

Set doc = ie.document


' Find the input tag of the order form and submit button:
Set objCollection = ie.document.getElementsByTagName("input")

i = 0
While i < objCollection.Length
    If objCollection(i).Name = "ordernumber" Then

        ' Set text for search
        objCollection(i).Value = "655032"

    Else
        If objCollection(i).Type = "submit" And objCollection(i).Name = "Submit" Then

            ' "Search" button is found
            Set objElement = objCollection(i)
            objElement.Click

        End If
    End If
    i = i + 1
Wend

End Sub

The part that I'm having trouble with is this:

If objCollection(i).Name = "ordernumber" Then

    ' Set text for search
    objCollection(i).Value = "655032"

Usually you can change the HTML value of the form, but in this case there is no HTML value in the input tag, so I'm at a loss. My goal here is to simply insert an order number into the form and hit the submit button. Unfortunately I can't show you the website as it's an internal corporate site, but here's a screenshot of the relevant info: screenshot

Thanks!

Aucun commentaire:

Enregistrer un commentaire