VBA Script to retrieve text from label on webpage

[Manifest0]

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

document.getElementbyId("ctl21_ctl13_ctl00_lblEmail").innerHTML

or .text etc, but none of them worked. If someone could help me out I would really appreciate it =)

[Manifest0]

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?

[Leith Ross]

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?

[Manifest0]

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

[Leith Ross]

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.

' Thread:  http://www.excelforum.com/excel-programming/793412-vba-script-to-retrieve-text-from-label-on-webpage.html#post2605487
' Poster:  Manifest0
' Written: Septemeber 21, 2011
' Author:  Leith Ross

Sub ExtractEmailAddresses()

  Dim Cell As Range
  Dim RegExp As Object
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Text As String
  Dim Wks As Worksheet
  
      Set Wks = ActiveSheet
      
      Set Rng = Wks.Range("A2")
      Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
      
      If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Wks.Range(Rng, RngEnd)
                
              For Each Cell In Rng
                  Text = GetPageSource(Cell)
                  X = InStr(1, Text, "Email:")
                  If X Then
                     Y = InStr(X + 6, Text, "href=""")
                     X = InStr(Y + 6, Text, ">")
                     Y = InStr(X + 1, Text, "<")
                     Cell.Offset(0, 1) = Mid(Text, X + 1, Y - X - 1)
                  End If
              Next Cell
          
End Sub

Function GetPageSource(ByVal URL As String) As String

  Dim Request As Object

      On Error Resume Next
    
          Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
          If Request Is Nothing Then Set Request = CreateObject("WinHttp.WinHttpRequest.5")
        
      Err.Clear
      On Error GoTo 0
    
      Request.Open "GET", URL, False
      Request.Send
    
      GetPageSource = Request.responsetext

End Function

[Manifest0]

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.

[watersev]

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.