Excel VBA : How to copy href from HTML by referenc

Question

<a href="?contractDate=&amp;6578706f7274=1&amp;currency=MYR&amp;currPage=1&amp;method.searchM2MTotal=Retrieve&amp;d-448075-e=2&amp;netType=M&amp;contractFromDate=13%2F05%2F2016&amp;contractToDate=13%2F05%2F2016"><span class="export excel">Excel </span></a>| 

How can I copy the href in this HTML via Excel VBA?

This is my code, but it doesn't work.

Set Export = ie.Document.all("export excel")
    URL = Export.href
    ie.Navigate URL

Answer 1

This code you should walk through in debug mode ... it's more for instructional use than for production, but it will do what you want from it. I used Google as a test site.

Sub Test()
Dim Browser As SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument
Dim Link As String, Target As Object

    Link = "http://www.google.com"

    ' start browser
    Set Browser = New SHDocVw.InternetExplorer
    Browser.Visible = True
    ' wait a bit

    Browser.Navigate Link
    ' wait a bit

    Set HTMLDoc = Browser.Document
    ' wait a bit

    Set Target = GetElementByTagAndClassName(HTMLDoc, "SPAN", "gbtb2")

    If Not (Target Is Nothing) Then
        ' test here if parent really is a <a>
        Debug.Print Target.parentElement.href
        ' ta-taaaa!!!
    End If

End Sub

' get element by tag and attribute value
Function GetElementByTagAndClassName(Doc As MSHTML.HTMLDocument, ByVal Tag As String, ByVal Match As String) As MSHTML.IHTMLElement
Dim ECol As MSHTML.IHTMLElementCollection
Dim IFld As MSHTML.IHTMLElement


    Set GetElementByTagAndClassName = Nothing

    Set ECol = Doc.getElementsByTagName(Tag)
    For Each IFld In ECol
        ' Debug.Print IFld.className
        If IFld.className = Match Then
            Set GetElementByTagAndClassName = IFld
            Exit Function
        End If
    Next
End Function