How to navigate to several URLs, drill down to certain links, and extract data from each

Question

I setup a simple script to loop through cells in an array, copy/paste each to the end of a URL, and import data from each URL into each sheet. It's creating new sheets as it goes.

It's basically like this...

Set rng = Sheets("IDs").Range("A2:A4")
For Each cell In rng

' DO STUFF HERE

Next cell

My question is this. How can I get the script to look drill into each href and extract the data from there? Each href has the string 'Upload*' in the name. So, I don't even know if this is possible, but I would like to get my script to drill into each link with 'Upload*' (and wildcard), copy/paste data into Worksheet, go back to the original URL, look for another link with 'Upload*' and so on and so forth. It would definitely be a recursive script, for sure. Is this possible, or is this just a waste of time?

I'm guessing it would look something like this, but the code below doesn't work for me.

strSQL = "https://la_de_da_de_da_CampaignID=" & cell.Value

Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .Navigate strSQL
    Do Until .ReadyState = 4: DoEvents: Loop
        Set doc = IE.Document

        For Each l In doc.getElementsByTagName("a")
            If l.ClassName Like "Upload*" Then
            l.Click
            End If
        Next l

End With

Next cell

I'm getting an error message that reads: 'Run-time Error 13 Type Mismatch'

This line throws the error:

For Each l In doc.getElementsByTagName("a")

http://stackoverflow.com/questions/27066963/scraping-data-from-website-using-vba — pokemon_Man, Feb 16 '17 at 19:41

score 0 · Accepted Answer · answered Feb 16 '17 at 20:20

I got it working with this.

strSQL = "https://la_de_da_de_da_CampaignID=" & cell.Value

Set IE = CreateObject("InternetExplorer.Application")
With IE
    .Visible = True
    .Navigate strSQL
    Do Until .ReadyState = 4: DoEvents: Loop

        Set ElementCol = IE.Document.getElementsByTagName("a")

        For Each link In ElementCol
        Debug.Print link.innerHTML
            If link.innerHTML Like "Upload*" Then
                link.Click
                Exit For
            End If
        Next link

End With

How to navigate to several URLs, drill down to certain links, and extract data from each

1 Answers1