VBA to extract values from HTML table

  • I have a task at hand where I have over 1000+ blog URLs and I need to fetch metrics for each URL from a site called Ahrefs.com (Paid Service) They do have a trial version that you can sign up to create the codes.


    I Sheet 1 in my excel workbook, I have the URLs listed in Column A and needs the metrics entered in adjacent cells.


    [TABLE="width: 1475"]

    [tr]


    [td]

    URL

    [/td]


    [td]

    URL rank

    [/td]


    [td]

    Domain rating

    [/td]


    [td]

    backlinks

    [/td]


    [td]

    Referring domains

    [/td]


    [td]

    Organic keywords

    [/td]


    [td]

    Est organic traffic

    [/td]


    [td]

    Paid keywords

    [/td]


    [/tr]


    [tr]


    [td]

    https://ahrefs.com/site-explor…esh?target=problogger.net

    [/td]


    [TD="align: right"]15[/TD]
    [TD="align: right"]66[/TD]
    [TD="align: right"]35200[/TD]
    [TD="align: right"]33[/TD]
    [TD="align: right"]21[/TD]
    [TD="align: right"]211[/TD]
    [TD="align: right"]13[/TD]

    [/tr]


    [tr]


    [td]

    https://ahrefs.com/site-explor…?target=dailyblogtips.com

    [/td]


    [TD="align: right"]15[/TD]
    [TD="align: right"]66[/TD]
    [TD="align: right"]35200[/TD]
    [TD="align: right"]33[/TD]
    [TD="align: right"]21[/TD]
    [TD="align: right"]211[/TD]
    [TD="align: right"]13[/TD]

    [/tr]


    [tr]


    [td]

    https://ahrefs.com/site-explor…et=smartpassiveincome.com

    [/td]


    [TD="align: right"]15[/TD]
    [TD="align: right"]66[/TD]
    [TD="align: right"]35200[/TD]
    [TD="align: right"]33[/TD]
    [TD="align: right"]21[/TD]
    [TD="align: right"]211[/TD]
    [TD="align: right"]13[/TD]

    [/tr]


    [/TABLE]



    That is just a few of the items I have on my list.


    Is there any way this can be automated so much so I don't have to go and extract the data manually for all the 1000+ blogs?


    I've tried several codes from the web, but none of them seem to work, or at least to my knowledge of VBA.


    Any help and support regarding this would be awesome.

  • Re: Extract Specific Values from Ahrefs.com


    Ah... that's a bummer. What's the point of having a premium account and I can't automate my work the way I want. Thanks for catching that Pike. I never imagined they would have a restriction like that. After all I'm not stealing information I'm paying to access the info.

  • Re: Extract Specific Values from Ahrefs.com


    Hello,


    Since you are paying a subscription, you should contact them to find out if they have an FTP service, or any other way for you to recup the data you are paying for ... :wink:


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Re: Extract Specific Values from Ahrefs.com


    Hi DancingLady, and welcome to OzGrid :)


    Quote

    After all I'm not stealing information I'm paying to access the info.


    For what it's worth, I think the idea of these policies is to try and prevent too much traffic hitting the server at once as this can cripple a server and make the site inaccessible (which is essentially how DDoS attacks work). When you manually browse a web page you might send a handful of requests per minute depending on how much clicking around you do, whereas a script could potentially be sending hundreds of requests per second if it's scraping the site.


    Scripts like the one you're proposing in reality probably don't have that much bearing on the server, but if they make any kind of exception in the terms of use then they leave themselves wide open to loopholes and gray areas for other people to run much more resource-hungry scripts and potentially cause issues for everyone using the site.


    In some cases (although very, very, very slim chance), you can write directly the company and ask for explicit consent to run your script - they can review it and if needed create an exception for your machine to do what it needs to do. Might be worth a try if it's a legitimate need for business use?


    Hope that helps :)