Display data from html/asp in userform

[cellulargnome]

First of all let me apologize for the fact questions almost the same as this have been posted to death, I just haven't found a solution that meets my needs.

I need to pull a short line of text from a web page and display it in a userform. The page I need text from is VERY small.

I'm able to post the given text to a form an a different page with the below.

Private Sub btnSubmit_Click()
 
Dim IE As SHDocVw.InternetExplorer
Set IE = New SHDocVw.InternetExplorer
 
IE.Visible = False
IE.Navigate "URLHERE"
 
Do
DoEvents
Loop Until IE.ReadyState = 4
 
 
Call IE.Document.GetElementByID("<wbr>ELEMENTID").SetAttribute("<wbr>value", TEXTBOX.Value)

Set AllInputs = IE.Document.<wbr>getElementsByTagName("input")
    For Each hyper_link In AllInputs
        If hyper_link.Name = "button" Then
            hyper_link.Click
            Exit For
        End If
    Next
Do
DoEvents
 
Loop Until IE.ReadyState = 3
Do
DoEvents
 
Loop Until IE.ReadyState = 4
 
IE.Quit
 
Unload Me
End Sub

I have the Microsoft Internet Controls ref added to the vba project. and the above works to get the text posted.

What I am now having trouble with is seeing this text without having web page open.
For now having looked around for hours I've settled for using the web browser control in a user form just to display the actual page, it's not pretty and not ideal.

What I want is to post the text from that page on the same userform I have to update it.

I tried to paste the HTML I'm looking at but MrExcel seems to process it in and out of code tags and even commented out it still runs so you see a table.

The only way I see the ID the text I want is with it's class

<td height="31" align="center" bgcolor="#4F6C8A" class="THECLASS">THE TEXT TO DISPLAY</td>

I'll post the HTML in tags anyway (maybe it'll show up in the page source)

<table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr>
     <td align="left" valign="top">
       <table width="100%" border="0" cellspacing="0" cellpadding="0">
         <tr>
           <td height="31" align="center" bgcolor="#4F6C8A" class="THECLASS">THE TEXT TO DISPLAY</td>
         </tr>
       </table>
     </td>
    </tr>
  </table>

Nothing useful above or below this table in the code, I did say it's a small page. A little bit of CSS and bit of JAVA to refresh the page at set intervals. Page contains the table above and nothing else.

It would be a great help if somebody had something that would work for me.

I've tried a few times to utilize the first bit of code to grab the text and add it to a string that I can ref in a label but it keeps failing when I run it. It's fails when I attempt to set a string to the value of IE.Document.<wbr>getElementsByClassName("THECLASS"). I've tried adding .InnerHtml and .InnerText but it still fails.

Many thanks in advance for any solutions.

 

[John_w]

getElementsByClassName returns an array of elements (even if there is only 1 element) and the VBA syntax to refer to an item in an array is arrayName, where n=0 for the first item in this case. Therefore try:

IE.Document.getElementsByClassName("THECLASS")(0).innerText

If you want to retrieve the text from the web page without using IE you could try his:

    Dim httpReq As Object, HTMLdoc As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    With httpReq
        .Open "GET", "http://theURL", False
        Set HTMLdoc = CreateObject("HTMLfile")
        HTMLdoc.body.innerHTML = .responseText
    End With
    MsgBox HTMLdoc.getElementsByClassName("THECLASS")(0).innerText

Using this XMLhttp method will retrieve the text only if the web page doesn't run any scripts (e.g. Ajax) or has a <b_ody o_nload=""> (without the underscores which I've had to add to prevent the forum changing those tags to ******) to populate the HTML table.

To prevent the forum rendering HTML, select the 'HTML Off' option in the advanced editor.</b_ody>

 

[cellulargnome]

John_w Thank you for taking the time to reply to me it's very much appreciated.


Attempted to use your second solution with the following errors.


First I get "Unspecified Error" on this line

HTMLdoc.body.innerHTML = .responseText
[/cpde]


If I change responseText to responseXML it then fails once it gets to the message box giving me error 438 - Object doesn't support this property or method


this line 


[code]
MsgBox HTMLdoc.getElementsByClassName("THECLASS")(0).innerText

Any ideas?
Once again thank you.me("THECLASS")(0).innerText

 

[John_w]

Sorry I missed a crucial line.

    Dim httpReq As Object, HTMLdoc As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    With httpReq
        .Open "GET", "http://theURL", False
        .Send
        Set HTMLdoc = CreateObject("HTMLfile")
        HTMLdoc.body.innerHTML = .responseText
    End With
    MsgBox HTMLdoc.getElementsByClassName("THECLASS")(0).innerText

If I change responseText to responseXML it then fails once it gets to the message box giving me error 438 - Object doesn't support this property or method

getElementsByClassName is available in IE9+, so you would get that error if your computer has IE8 or lower.

 

[cellulargnome]

Many thanks for your efforts with this one.
It's working!

It was still failing here:

MsgBox ie.Document.getElementsByClassName("that****Classname")(0).innerText

I had a good look through the locals window and couldn't find any reference to the class.
I did however find that innerText still had the text string that I need to pull from the page, Quick edit to the message box got me what I wanted.

MsgBox HTMLdoc.body.innerText

Thanks again, You've been an amazing help.

 

[cellulargnome]

Now I have an issue with refreshing data.

At first this seemed to be working but I failed to spot something that I'm now struggling with a little. The text string from the page I'm grabbing it from ends up in a label on the form the first time the form loads but the string can change at any moment, If I run the form again an hour later knowing that several changes have been made to the test string I get the same text I got on the forms first load. First I thought it was holding the text because I wasn't cleaning up after pulling the text the first time. I tried setting httpReq as Nothing after the label caption was set but this didn't help. I then tried doing the same with httpReq and HTMLdoc but again the same problem.

Knowing the data I was seeing would likely be incorrect I opened the page I get the text from so I can double check and the form started refreshing albeit slowly but it was refreshing. If I close the page it's breaks again.

For now I'm working round this by opening the page when the form is loaded in an IE window in the background and everything seems to work. At the moment I'm starting this IE window every time the form is opened but if I try to quit it as I do in my first post I still end up with iexplore.exe instances showing in the task manager. Starting IE every time the form is run end up in many ie instances so this isn't ideal.

If anybody can help with either closing the ie instance or fixing the issue with refreshing data that would be awesome! The asp page I'm grabbing the data from if left open refreshes every 60 seconds.

Many thanks!