+ Reply to Thread
Results 1 to 7 of 7

VBA Script to retrieve text from label on webpage

  1. #1
    Registered User
    Join Date
    09-22-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    4

    VBA Script to retrieve text from label on webpage

    Hi all, first time poster here.

    I'll preface this by saying I know nothing about VB, though I've done a few years of programming in various languages.

    My problem is relatively simple - I have a column of URLs in excel, and I wish to go through each one and retrieve the email address located on the page and spit that back into my excel sheet. I tried using a web query but I couldn't import the table containing the email - it would only grab the whole page 'skeleton', MINUS the actual information.

    I figured the next alternative would be to use a VB script. I have spent maybe 3-4 hours trying to hack examples from the web into shape, but none of my attempts have worked.

    A sample URI I need to visit is: http://www.minedu.govt.nz/Parents/Al...ngton%20School

    The label id I need is: span id="ctl21_ctl13_ctl00_lblEmail"

    Basically, I've tried things like
    Please Login or Register  to view this content.
    or .text etc, but none of them worked. If someone could help me out I would really appreciate it =)
    Last edited by Manifest0; 09-23-2011 at 10:45 PM.

  2. #2
    Registered User
    Join Date
    09-22-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VBA Script to retrieve text from label on webpage

    Alternatively, if anyone knows how to force excel's built-in web query function to pull from a specific value or span id in a webpage rather than a large table, that would also work. Are there any add-ons that would do that?

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Script to retrieve text from label on webpage

    Hello Manifest0,

    Welcome to the Forum!

    I must be missing something in your post. It doesn't seems reasonable that would want the same email address from the same web page more than once. Can you provide a few more URLs and what you extracted?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    09-22-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VBA Script to retrieve text from label on webpage

    Thanks for responding =)

    So I have a column of around 1,000 URLS which was given to me by the ministry of education. Each site is in a standard format with the email stored in a label. I need to iterate through the list of URLS and pull the text from the email label, and spit that back into excel, giving me a list of 1,000 emails.

    For example:
    http://www.minedu.govt.nz/Parents/Al...ngton%20School
    You can see that the email is office@addington.school.nz

    The next URL on the list, http://www.minedu.govt.nz/Parents/Al...stian%20School
    has an email of office@aidanfield.school.nz

    I hope that explains what I want a little more clearly - and that someone is able to help! =D

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA Script to retrieve text from label on webpage

    hi, Manifest0, one of the options to make it. Prior to running the code you'll need to add the following library: Microsoft WinHTTP Services (Tools - References) and then, run the code "test". It will take some time to complete depending on Internet connection.
    Attached Files Attached Files
    Last edited by watersev; 09-23-2011 at 07:20 AM.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: VBA Script to retrieve text from label on webpage

    Hello Manifest0,

    Here is a workbook that extracts the emails from the sites you provided. Expand the list as needed. This method is the fastest available. Here are the macros used in the attached workbook.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-22-2011
    Location
    New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VBA Script to retrieve text from label on webpage

    Oh wonderful people, you have saved me a massive amount of work. Thank you so much - I really wish I could express my gratitude further than this. Know that you have made my day and relieved me from a ton of stress. Cheers guys.

    Marking as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1