VBA Extract ID from Anchor Text in Internet Explorer

[jeffreyrgomez]

Hello everyone,

I am trying to grab an ID number from my Excel sheet, navigate to a webpage, find a link using the ID number as anchor text, and extract the 'po_header_id' number from the link.

This is what I have so far:

Dim IE As Object
    Dim PoNum As String

    ' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.Navigate "https://app.buildtopia.com/"
    Application.StatusBar = "Buildtopia is loading. Please wait..."
 
    ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
       
    ' Nav instructions
    If MsgBox("Log in and navigate to the list of POs matching your Excel file.", vbOKCancel) = vbCancel Then Exit Sub
    
    'Gets anchor text
    PoNum = ActiveCell.Value
    MsgBox PoNum

The link html I would like to extract from is as follows:

HR-1745-1

I am not sure the correct command to do this. I have done some VBA macros before, but not many using Internet Explorer.

Thanks!

 

[John_w]

Welcome to MrExcel.

Your HTML hasn't posted correctly - you have to add a space after the < in every opening tag and after the < in every closing tag (including HTML in code comments), otherwise the forum tries to render the HTML - so the following code is a slight guess, but see if it does what you want.

    Dim HTMLdoc As Object 'HTMLDocument
    Dim PoLink As Object 'HTMLAnchorElement
    Dim i As Long
    Dim id As Variant
    
    Set HTMLdoc = IE.document
    Set PoLink = Nothing
    i = 0
    While i < HTMLdoc.links.Length And PoLink Is Nothing
        If HTMLdoc.links(i).innerText = PoNum Then Set PoLink = HTMLdoc.links(i)
        i = i + 1
    Wend
    
    If Not PoLink Is Nothing Then
        id = PoLink.getAttribute("po_header_id")
        If id <> vbNull Then
            MsgBox "Found po_header_id = " & id
        Else
            MsgBox "Attribute po_header_id not found" & vbNewLine & PoLink.outerHTML
        End If
    Else
        MsgBox "Link with display text " & PoNum & " not found"
    End If

 

[jeffreyrgomez]

Thanks John, I see what you mean. Here is the HTML code I intended to post:

< a href="/english_exec/sp-po?op=detail_show&project_id=48720&po_header_id=8748465&clear_session=true">HR-1745-1< /a>

 

[John_w]

I have updated the code to extract the po_header_id from the href URL. It should extract "8748465" for the example given.

    Dim HTMLdoc As Object 'HTMLDocument
    Dim PoLink As Object 'HTMLAnchorElement
    Dim i As Long
    Dim id As Variant
    
    Set HTMLdoc = IE.document
    Set PoLink = Nothing
    i = 0
    While i < HTMLdoc.links.Length And PoLink Is Nothing
        If HTMLdoc.links(i).innerText = PoNum Then Set PoLink = HTMLdoc.links(i)
        i = i + 1
    Wend
    
    If Not PoLink Is Nothing Then
        id = Split(Split(PoLink.href, "&po_header_id=")(1), "&")(0)
        MsgBox "Found po_header_id = " & id
    Else
        MsgBox "Link with display text " & PoNum & " not found"
    End If

Both codes loop through all the links in the webpage. However, the anchor tag can be referenced more directly if there are any nearby elements which have an id= or name= attribute.

 

[jeffreyrgomez]

Thanks a bunch. I was able to get this to work with slight modifications.