Retrieve specific text from web page

[hstaubyn]

Hi,

I am trying to extract some text that is displayed on a web page and store it in a string variable. The code I have to open the web page looks like this:

Dim oIE1 As Object
 
Set oIE1 = CreateObject("InternetExplorer.Application")
 
oIE1.navigate ("myURL")
oIE1.Visible = True
Wait1Ready

(Wait1Ready loops until the browser is open)

What I need to do is then be able to reference the piece of text which has the source code shown below (I have removed the actual name and id because I'm a little uncertain about my company's policy on this kind of thing):

<spanid="NameID" style="font-size:XX-Large;">Text Displayed on Webpage</</< font color="#0000ff" <>span>

The end result being that I have a line of code as follows:

MyString = "Text From Web Page"<TEXT page Web from>

I really don't know where to start. I have tried a web query, but am limitied to extracting the whole page rather than just the text as highlighted above.

Any help would be hugely appreciated.

H

 

[baitmaster]

have a look at a previous post I made, which requires something similar http://www.mrexcel.com/forum/showthread.php?t=482073

The subject is different, but the theories in it may be useful to you

 

[Sektor]

Pass HTML to this function and it will return array with all text between these tags:

Function GetText(Str As String) As Variant()

    Dim i As Integer
    Dim m As Object
    Dim mc As Object
    Dim arr() As Variant
    
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "<span id="".+"" style="".+"">(.+)</span>"
        Set mc = .Execute(Str)
        ReDim arr(1 To mc.Count)
    End With
    
    For i = 0 To mc.Count - 1
        arr(i + 1) = mc(i).Submatches(0)
    Next
    
    GetText = arr

End Function

 

[hstaubyn]

Thanks for the help.

Sektor, I am having a little trouble with your code. Should I be adding "NameID" and "font-size:XX-Large" anywhere?

At the moment, with those additions and without, I get a runtime error with the

ReDim arr(1 To mc.Count)

bit highlighted in the debugger.

I'm afraid my expertise doesn't extend to understanding what this all means...

 

[John_w]

I have tried a web query, but am limitied to extracting the whole page rather than just the text as highlighted above.

A web query is the easiest method. Use the Macro Recorder to create a web query to import the entire page, to a separate worksheet if necessary, and then edit the code to assign the cell containing the required text to your string variable.

 

[Sektor]

No, there can be anything...

 

[hstaubyn]

I could use a web query and that would certainly work, however it would take too long for what I need this code for.

Sorry Sektor - when I run your code I get a runtime error '9', subscript out of range whether the code is exactly as you provided or with some modifications.

Do you know why?

 

[Sektor]

What do you pass to function? It requires HTML source code. It will search for pattern which you wrote in your first post.
Can you e-mail me what namely you pass?

 

[hstaubyn]

This what I am passing: "ctl00_MainContent_ProjectNameLabel"

I'm not sure if it's correct?

 

[hstaubyn]

Hi,

no responses for a while... I still haven't figured this out. To recap:

I need to read a string value from a website. The line in the website code is as follows:

<span id="ctl00_MainContent_ProjectNameLabel" style="font-size:XX-Large;">Name to be stored as variable</span>

<o>The bit in black, "Name to be stored as variable" is what I need to store, probably as a string in my code, does anyone know how this can be done?</o>