Click on Href link through vba

Question

I am trying to create IEautomation through vba-excel for the following link.

URL: http://qpldocs.dla.mil/search/default.aspx

The code includes search for the string "QPL-631",and click on the corresponding java script link MIL-I-631D(6).When I inspected "MIL-I-631D(6)" link ,I found following source code of href tag

<a href="javascript:__doPostBack('search_list$DG$ctl03$ctl00','')">MIL-I-631D(6)</a>

So there are no click options for the href link and the address of manual clicking on href link is completely different than href address.So I am stuck here.I would like to add a code that clicks "MIL-I-631D(6)" and outputs the results.

I have tried the below code and so far and unable to proceed further.

Private Sub IE_Autiomation()

Dim i As Long

Dim IE As Object

Dim objElement As Object

Dim objCollection As Object

Dim ae As HTMLLinkElement

Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = True
IE.navigate "http://qpldocs.dla.mil/search/default.aspx"
Application.StatusBar = "Loading. Please wait..."

Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop

Application.StatusBar = "Search form submission. Please wait..." IE.document.getElementById("Search_panel1_tbox").Value = "QPL-631" IE.document.getElementById("Search_panel1_btn").Click Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop

Answer 1

Here is an interim solution to write out to sheet as you are already hard coding the product code "QPL-631" you can just skip straight to using that in the URL string to return your results.

Note: I have pulled the table ID from that page:

html.getElementById("Lu_gov_DG")

You might want to explore if this is a common theme across products (I suspect yes). Will make life a lot easier. You could even do away with IE altogether and go for a faster XHR solution.

Option Explicit

Private Sub IE_Automation()

    'References Internet Controls and HTML Object library

    Dim i As Long
    Dim IE As Object
    Dim html As HTMLDocument

    Dim product As String
    product = "QPL-631"

    Dim url As String

    url = "http://qpldocs.dla.mil/search/parts.aspx?qpl=1528&param=" & product & "&type=256"

    Set IE = CreateObject("InternetExplorer.Application")

    With IE

        .Visible = True

        .navigate url                            '"http://qpldocs.dla.mil/search/default.aspx"

        Application.StatusBar = "Loading. Please wait..."

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

        Set html = .Document

        Dim allRowOfData As Object

        Set allRowOfData = html.getElementById("Lu_gov_DG")

        Dim r As Long, c As Long

        Dim curHTMLRow As Object

        For r = 1 To allRowOfData.Rows.Length - 1

            Set curHTMLRow = allRowOfData.Rows(r)

            For c = 0 To curHTMLRow.Cells.Length - 1
                Cells(r + 1, c + 1) = curHTMLRow.Cells(c).innerText
            Next c

        Next r

        .Quit

    End With

    Application.StatusBar = False 'And tidy up our change to the status bar

End Sub

There is example with postback here, which I will have a look at.

Reference:

1. How to reset the Application.StatusBar

Answer 2

You have several options available to you. See below for a pretty comprehensive list of possibilities.

Try getting the collection of anchor tags, with:

GetElementsByTagName("a")

Then, iterate that collection using as much logic as you can to ensure you're clicking the right button.

For each l in ie.document.getElementsByTagName("a")

If l.ClassName = "hqt_button" Then
    l.Click
    Exit For
Next

If there are multiple anchors with the same classname, you could do:

If l.ClassName = "hqt_button" AND l.Href = ""javascript:void(0): onclick=HeaderBox.trySubmit()" Then
    l.Click
    Exit For
Next

Alternatively

If you are using IE9+ you could use the GetElementsByClassName method.

GetElementsByClassName("hqt_button") 

How do I use excel vba to click a link on a web page