VBA to click a particular tab on webpage and then copy all of the data.

[AshG1990]

Hi,

I am having real issues trying to get the desired result from a macro I'm writing. I'm perfectly fine in launching internet browser and navigating to a webpage. However, once on this webpage I want to click a certain tab and then copy all of that data. The issue is, that when you click the tab manually it doesn't have a different URL, it remains the same. I've looked over various forums for guidance but still no luck.

The url is https://stockbrokers.webfg.com/inde...ection=stockFactsheet&tidm=AZN&index_tidm=ASX

As you can see from the source code below the li classes have the same names for all the tabs and so do the a classes. It is tab 7 that I want to click. Also then I want to copy all of the information from this tab, it doesn't matter if it copies the whole page as long as I get this data.

Any help would be much appreciated, I've spend 4 days trying to figure this out.

Thanks. Source code link below - had to use link as it keeps applying weird formatting to the source code if I copy and paste.

 

[John_w]

Try the code at https://www.mrexcel.com/forum/excel...tion-ie-dropdown-post4896667.html#post4896667, as that also deals with UL and LI elements.

Or try looping through all the links in the HTMLdocument looking for the required link display text, like this:

    Dim HTMLdoc As HTMLDocument, link As HTMLAnchorElement, i As Long
    Set HTMLdoc = IE.document   'InternetExplorer object with web page loaded
    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        If HTMLdoc.Links(i).innerText = "Required Link Display Text" Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
        link.Click
    End If

 

[AshG1990]

Hi John,

Really appreciate your response, it has got me further than I did. However, it still isn't clicking the tab, I know it has found the tab with the "Technical analysis" as its inner text as I put a msgbox line in the code you provided. It's not giving me any errors or anything but it's just not clicking it.

Any ideas?

Also what's the difference between using HTML class, e.g. HTMLDocument and using internetexplorer.document?

Hope you can help me further and thanks in advance.

Regards,

Ash

 

[AshG1990]

Please, can you help? Or anyone else.

Thanks

 

[John_w]

Also what's the difference between using HTML class, e.g. HTMLDocument and using internetexplorer.document?

Ash

They are the same type of object, but if you set references to MS Internet Controls (ShDocVw) and MS HTML Object Library (MSHTML) and use the Object Browser in VBA (press F2) you will see that IE.document is a generic Object type whereas HTMLDocument is a class with its named members shown. If you declare a variable as HTMLDocument the intellisense in the VBA editor shows you all its methods and properties so it is easier to write code.

This works for me (IE11, Win10) - it clicks the 'Technical analysis' link and copies the whole web page to the first worksheet:

Public Sub IE_Copy_Page()

    Dim IE As InternetExplorer
    Dim URL As String
    Dim HTMLdoc As HTMLDocument
    Dim link As HTMLAnchorElement
    Dim tabDiv As HTMLDivElement
    Dim i As Long
    Dim destSheet As Worksheet, shp As Shape
    
    Set destSheet = Worksheets(1)
    With destSheet
        .Cells.Clear
        For Each shp In .Shapes
            shp.Delete
        Next
    End With
    
    URL = "https://stockbrokers.webfg.com/index.php?key=fb2682696113530491da9975a1292c21&section=stockFactsheet&tidm=AZN&index_tidm=ASX"
    
    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .navigate URL
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        Set HTMLdoc = .document
    End With
    
    Set link = Nothing
    i = 0
    While i < HTMLdoc.Links.Length And link Is Nothing
        If InStr(HTMLdoc.Links(i).innerText, "Technical analysis") > 0 Then Set link = HTMLdoc.Links(i)
        i = i + 1
    Wend
    If Not link Is Nothing Then
        link.Click
    Else
        MsgBox "'Technical analysis' link not found"
        Exit Sub
    End If
       
    Set tabDiv = HTMLdoc.getElementById("tabs7")
    While InStr(tabDiv.innerText, "Trend") = 0
        DoEvents
    Wend
    
    With IE
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        .ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
        .ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER
        .ExecWB OLECMDID_CLEARSELECTION, OLECMDEXECOPT_DODEFAULT
    End With
    
    With destSheet
        .Activate
        .Range("A1").Select
        .Paste
        .Range("A1").Select
    End With
       
End Sub

 

[AshG1990]

Hi John,

It was the "InStr" part I was missing, thanks so much for your help, really appreciate it.

Regards,

Ash